How do I sort multiple group headings alphabetically AND their contents within?

K4R4

New member
Joined
Aug 20, 2013
Messages
4
Reaction score
0
Points
0
Hello, I'm working on a simple table to compile different cost estimates from various engineering firms. I have different group headings such as ASPHALT, PIPING, STONE, etc. and then there are different items underneath those headings, all in column A. I want to sort everything alphabetically, but I don't know how to keep the items in each group with their appropriate categories. I want all the asphalt-related items to remain under the heading ASPHALT, but I want to be able to alphabetize by category and alphabetize within each category. Can anyone help?

Thanks!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Hard to follow.. can you attach a sample workbook?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
What I would suggest is to add a helper column.

First, we can use column B in the helper... but you will need to fill in the blank cells with something... maybe just a dash ( - )

Then, in G4 enter another heading like "Helper"

Then in G5 enter formula:

=IF(B5="",A5,G4)

copied down.

now select from A4 to G151 and sort first by column G, then by column A
 

K4R4

New member
Joined
Aug 20, 2013
Messages
4
Reaction score
0
Points
0
I was very confused at first because I'm kind of a beginner to formulas, but I figured it out. Thanks so much!!:)

For anyone else who might follow this in the future:

-I created a column after the first one, in which I placed a (-) for every row that was a category title, and left if blank for all the items under the categories
-Then I created a helper column at the end and filled all the values in with: =IF(B5="-",A5,G4) That means: If the value in column B has a (-) in it, then this value should be whatever's in column A, and if it doesn't have a (-), then the value should be whatever's in the row ABOVE. So then the helper column was just filled with whatever the category label was
-Then I sorted based on the category first, and the items within the categories second (column A)

Much appreciated.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Although that would work too, my suggestion was just to fill the blanks in column B between groups with a dash, then apply the formula in the helper. It would prevent the addition of another column.
 
Top