×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Merging Unique Cells Into Unified Headings

Merging Unique Cells Into Unified Headings

Merging Unique Cells Into Unified Headings

(OP)
I have a long list of unique data in excel in the following form. As a first step I removed all the duplicates.

The unique headings always start with a "$_". There are many more unique headings in the list other than GRIDS and MATERIALS.

I would like to filter the Original List, such that each data entry under a unique heading is merged under a single heading. ie the Required List.

Original List:

$ GRIDS
GRID A
GRID B
GRID C
$ MATERIALS
MATERIAL A
MATERIAL B
MATERIAL C
$ GRIDS
GRID D
GRID E
GRID F
$ MATERIALS
MATERIAL D
MATERIAL E
MATERIAL F

Required List:

$ GRIDS
GRID A
GRID B
GRID C
GRID D
GRID E
GRID F
$ MATERIALS
MATERIAL A
MATERIAL B
MATERIAL C
MATERIAL D
MATERIAL E
MATERIAL F

RE: Merging Unique Cells Into Unified Headings

It would be fairly easy with a little bit of VBA.

I can't think of a simple VBA-less way to do it.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Merging Unique Cells Into Unified Headings

Your only problem is the $ - get rid of it and add two spaces after each heading and your list should sort pretty well using Advanced Sort/Filter menu, choosing delete duplicates.

RE: Merging Unique Cells Into Unified Headings

Were I to do this I would have to add an auxiliary column that looked to see if the entry to the right started with "$_". If it did it would copy that value, otherwise it would use the value from the row immediately above it. This would populate the new column with a label that matched the most recent heading. Then copy/paste values to lock that in, delete duplicated rows and sort on the new column 1st and the old column 2nd.

The new column formula is something like =IF(left$(neighbor_cell, 2) = "$_", neighbor_cell, previous row above)

RE: Merging Unique Cells Into Unified Headings

Start recording a macro. Using Ctrl-H, change "$ Grids" to "Grids ". edit the macro adding additional rows for every entry. Then advanced sort works and will delete duplicates.

RE: Merging Unique Cells Into Unified Headings

(OP)
Hi Dave, all,

I'm able to extract all the headings. I'm struggling with the final step of organizing the data to reflect the Required List.

I'm trying to splice together two analysis models through their back-end text file import function. There's around 160 000 lines of text.

$ File C:\Users…
$ PROGRAM INFORMATION
$ CONTROLS
$ STORIES - IN SEQUENCE FROM TOP
$ GRIDS
$ DIAPHRAGM NAMES
$ MATERIAL PROPERTIES
$ REBAR DEFINITIONS
$ FRAME SECTIONS
$ AUTO SELECT SECTION LISTS
$ CONCRETE SECTIONS
$ TENDON SECTIONS
$ SECTION DESIGNER SECTIONS
$ SLAB PROPERTIES
$ DECK PROPERTIES
$ WALL PROPERTIES
$ LINK PROPERTIES
$ PANEL ZONE PROPERTIES
$ POINT SPRING PROPERTIES
$ AREA SPRING PROPERTIES
$ PIER/SPANDREL NAMES
$ POINT COORDINATES
$ LINE CONNECTIVITIES
$ AREA CONNECTIVITIES
$ LINK CONNECTIVITIES
$ STRIP GEOMETRY
$ GROUPS
$ POINT ASSIGNS
$ LINE ASSIGNS
$ AREA ASSIGNS
$ LINK ASSIGNS
$ LOAD PATTERNS
$ POINT OBJECT LOADS
$ FRAME OBJECT LOADS
$ SHELL UNIFORM LOAD SETS
$ SHELL OBJECT LOADS
$ ANALYSIS OPTIONS
$ MASS SOURCE
$ FUNCTIONS
$ GENERALIZED DISPLACEMENTS
$ LOAD CASES
$ AUTO CONSTRUCTION SEQUENCE CASE
$ LOAD COMBINATIONS
$ STEEL DESIGN PREFERENCES
$ STEEL DESIGN OVERWRITES
$ CONCRETE DESIGN PREFERENCES
$ COMPOSITE DESIGN PREFERENCES
$ WALL DESIGN PREFERENCES
$ CONCRETE SLAB DESIGN PREFERENCES
$ CONCRETE SLAB DESIGN OVERWRITES - STRIP BASED
$ SECTION CUTS
$ DEVELOPED ELEVATIONS
$ TABLE SETS
$ PROJECT INFORMATION
$ LOG
$ END OF MODEL FILE


RE: Merging Unique Cells Into Unified Headings

Here is a one liner solution that does it based on having a helper cell with the following formula.

=IF(LEFT(data,2)="$ ",SUBSTITUTE(LEFT(data,LEN(data)-1),"$ ",""),data)

this removes the leading "$ " and the trailing character enabling the data to sort using a

=SORT(data_plus_helper,,1)

data named range is your original data range with the titles
data_plus_helper named range is the data range and the helper column on the left of this


It will fail obviously if once removing the "$ " and trailing character if any two of the titles are then the same, but looking at your list it doesn't seem to be the case.



See attached file for the solution as easier to see it in action and understand the named ranges. Note you will need to be on the monthly Excel channel to use the dynamic ranges feature I believe (but I think it will back magic them into array formulas). If you are not on the monthly update channel you can do so quite easily by searching google.

Let me know if it worked.






RE: Merging Unique Cells Into Unified Headings

I suspect that the neame of material A doens't actually include material a - it is more likely to be 4x6 wood 20ft or whatever. So a simple sort won't work

Consider

CODE -->

$MATERIAL	$MATERIAL
steel	        $MATERIAL
wood	        $MATERIAL
concrete	$MATERIAL
$FUNCTION	$FUNCTION
wedding  	$FUNCTION
funeral 	$FUNCTION
party   	$FUNCTION
$MATERIAL	$MATERIAL
putty   	$MATERIAL
glass   	$MATERIAL
meat	        $MATERIAL
$FUNCTION	$FUNCTION
addition	$FUNCTION
subtraction	$FUNCTION
sin	        $FUNCTION 


the second column is trivial, and you can then use it sort the first column. But you have to cut and paste and it as values before sorting so it is basically a disaster. VBA is the solution





Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

RE: Merging Unique Cells Into Unified Headings

Greg

I think if that was the case, simply introduce another helper column in my solution that renames the actual Material name (steel/wood/concrete, etc) to Material A/B/C etc. Then you can sort original data based on that like I did.

RE: Merging Unique Cells Into Unified Headings

I thought Greg Locock was a bit hard on Greg Locock's solution, but just for fun I had a go at the VBA anyway (sample spreadsheet attached):

CODE --> VBA

Function GroupSort(DataRange As Variant, Optional ReturnArray As Boolean = True)
Dim DataDict As Scripting.Dictionary
Dim NumRows As Long, i As Long, j As Long, NGroups As Long, Col As Long, Row As Long, TotRows As Long
Dim MaxRows As Long
Dim RowVal As String, GroupedA() As Variant, GroupedA2() As Variant, ColA() As Variant

DataRange = DataRange.Value2
NumRows = UBound(DataRange)

Set DataDict = New Scripting.Dictionary
On Error Resume Next
For i = 1 To NumRows
    RowVal = DataRange(i, 1)
    If Left(RowVal, 1) = "$" Then
        If DataDict.Exists(RowVal) = 0 Then
            NGroups = NGroups + 1
            DataDict.Add RowVal, NGroups
        End If
    End If
Next i
ReDim GroupedA(1 To NumRows, 1 To NGroups)

For i = 1 To NGroups
    GroupedA(2, i) = DataDict.Keys(i - 1)
Next i

For i = 1 To NumRows
    RowVal = DataRange(i, 1)
    If Left(RowVal, 1) = "$" Then
        Col = DataDict(RowVal)
        Row = GroupedA(1, Col)
    Else
        GroupedA(Row + 3, Col) = RowVal
        Row = Row + 1
        GroupedA(1, Col) = Row
    End If
Next i
If ReturnArray Then
    For i = 1 To NGroups
        If GroupedA(1, i) > MaxRows Then MaxRows = GroupedA(1, i)
    Next i
    ReDim GroupedA2(1 To MaxRows + 1, 1 To NGroups)
    For j = 1 To NGroups
        For i = 1 To MaxRows + 1
            GroupedA2(i, j) = GroupedA(i + 1, j)
        Next i
    Next j

    GroupSort = GroupedA2
    Exit Function
' Count total rows
For j = 1 To NGroups
    TotRows = TotRows + GroupedA(1, j) + 1
Next j
ReDim ColA(1 To TotRows, 1 To 1)
Row = 0
For j = 1 To NGroups
    For i = 1 To NumRows
        Row = Row + 1
        ColA(Row, 1) = GroupedA(i + 1, j)
        If GroupedA(i + 2, j) = 0 Then Exit For
    Next i
Next j

GroupSort = ColA
End Function

End If 

The function will either return an array (default) or a single column if ReturnArray is set to false.



Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Merging Unique Cells Into Unified Headings

For those with access to Excel 365 with the new Sort functions, the Sortby function combined with a helper column will return a single column of sorted groups, and the Unique function will remove duplicates, giving the same results as the second option of my VBA function:


For more details and link to latest download file see:
https://newtonexcelbach.com/2020/05/16/new-sort-fu...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Merging Unique Cells Into Unified Headings

Any feedback on this?

Problem solved?

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Merging Unique Cells Into Unified Headings

(OP)
Hi Doug, your help is much appreciated. The GroupSort function worked really well, unfortunately it didn't place nicely with the software when importing the merged text file.

Unfortunately I'll need to manually merge these models - if only the software developer provided a product that worked straight out of the box!

RE: Merging Unique Cells Into Unified Headings

If only the users would specify the requirements and conditions clearly, concisely and completely.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Research Report - How Engineers are Using Remote Access
Remote access enables engineers to work from anywhere provided they have an internet connection. We surveyed our audience of engineers, designers and product managers to learn how they use remote access within their organizations. We wanted to know which industries have adopted remote access, which software they are using, and what features matter most. Download Now
eBook - Managing the Context of Product Complexity Using the Digital Twin
Keeping track of changes to complex products is difficult—think Aerospace & Defense equipment, new generations of commercial aircraft, and software-based automobiles. A new way to managing the digital context of the physical product is required and the answer is the Digital Twin. This ebook explores the opportunity available for Operations and Maintenance for the Digital Twin. Download Now
White Paper - Trends in Industrial Filtration
Substantial progress has been made in filtration technologies in recent years. New filter media materials, designs and processes have led to filters that are more efficient, reliable, compact and longer lasting. This white paper will discuss the various trends that are impacting operational responsibilities of MROs today and the resources that are available for staying up-to-date on the latest filtration solutions. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close