Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    Hard to follow.. can you attach a sample workbook?


  3. #3
    Quote Originally Posted by NBVC View Post
    Hard to follow.. can you attach a sample workbook?
    Sure, thanks!Compiled Cost Estimates.xlsx

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    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


  5. #5

    Smile

    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    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.


  7. #7
    Ah, I see. Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •