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/

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.

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)

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.

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

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.

• https://files.engineering.com/getfile.aspx?folder=b43dbef3-49e2-4b0d-96b2-4

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



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?

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.

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


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/

• https://files.engineering.com/getfile.aspx?folder=64ed1520-9717-4722-ab52-c

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/

Any feedback on this?

Problem solved?

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

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!

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

Skip,

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