Merging Unique Cells Into Unified Headings

[Trenno]

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

 

[IDS]

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/

 

[IFRs]

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.

 

[3DDave]

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)

 

[IFRs]

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.

 

[Trenno]

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

 

[Agent666]

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.

 

[GregLocock]

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

$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

http://eng-tips.com/market.cfm?

 

[Agent666]

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.

 

[IDS]

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):

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/

 

[IDS]

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-functions-and-sorting-to-groups/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Any feedback on this?

Problem solved?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Trenno]

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!

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}