Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
Have you grouped them separately with a blank between groups? –  Raystafarian Feb 28 '12 at 19:20
    
I just tried that. It only sorted the lines that weren't grouped. When I expanded the grouped cells after sorting, they were not attached to the main line. –  false0start Feb 28 '12 at 19:39
    
There seems to be a case for a PivotTable here. –  pnuts Mar 19 '13 at 15:50
add comment

1 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. Grouped 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. grouped rows after a sort

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.