How do I group rows in a spreadsheet and keep them together after sorting?

Question

I have a spreadsheet with the information set up as such:

ORDER     PLU/Recipe #     Name/Desc                  Components
2       27905      CINNAMON CRUMB CAKE SQUARES
2.01                           263719
2.02                           261453
2.03                           260018
2.04                           261263
2.05                           319384
2.06                           261487
2.07                           WATER
2.08                           260844
3       99804      APPLE DANISH 
3.01                           320267
3.02                           CINNAMON STREUSSEL FORMULA
3.03                           263046
3.04                           261354
3.05                           263186
4       69961      BLUEBERRY    
4.01                           320367
4.02                           CINNAMON STREUSSEL FORMULA
4.03                           261859
4.04                           261354
4.05                           263186
5       89804      CHEESE DANISH    
5.01                           320627
5.02                           CINNAMON STREUSSEL FORMULA
5.03                           263103
5.04                           261354
5.05                           263186

I would like to be able to collapse the ingredient lines (the ones with the decimals) so that it is easier to browse the items in the list and have that be sortable. When I try to do this, the collapsed lines don't stay with the item names and the item names disappear when I collapse the group. How can I make it so the list is sortable but the ingredients stay with the item names when collapsed?

The "Order" column exists so I can resort the list into the rather arbitrary order my employer has the original list in.

For reference, I am using Excel 2003.

Thanks for any help!

Answer 1

I know this works in Excel 2010, hopefully it also works in Excel 2003.

Part of your problem may be that Excel links grouped rows to the row below the group, not the row above the group.

First sort your data in descending order so that the item row is below the ingredients that belong to it. Group your ingredient rows.

Now that the components are grouped to the correct items, you should be able to easily sort on any of the item columns and have the components move with them.

To re-sort the components, you have to select and sort each group individually.

To get the 2nd picture, I hid the components for items 2 & 4 and then sorted by Order.

Answer 2

FYI to anyone who stumbles onto this.

To fix this issue: "Part of your problem may be that Excel links grouped rows to the row below the group, not the row above the group." it looks like there is an option to reverse it: "summary rows below detail"

To get to that option, do this: for excel 2010: go to Data Tab on ribbon > Click the little arrow in the bottom right hand corner of the "Outline" Section > and un-check "Summary Rows Below Detail" - taken from here: https://answers.yahoo.com/question/index?qid=20080923131443AAIFCpn