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
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
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
RE: Merging Unique Cells Into Unified Headings
The new column formula is something like =IF(left$(neighbor_cell, 2) = "$_", neighbor_cell, previous row above)
RE: Merging Unique Cells Into Unified Headings
RE: Merging Unique Cells Into Unified Headings
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
=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
Consider
CODE -->
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
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
CODE --> VBA
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 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
Problem solved?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Merging Unique Cells Into Unified Headings
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
Skip,
Just traded in my OLD subtlety...
for a NUance!