General discussion

Locked

sorting in Excel ...or 123

By tunnel ·
I have a sheet with about 9000 records each containing company, class, and dollar cells...among others. The records need to be sorted by company and class(at different times) and then the subtotals need to be ranked by greatest to least dollar total.
What I have been doing is sorting and subtotalling as indicated and then copying each subtotal line to a new sheet and sorting again to get the ranking I need.
This is a graceless and time consuming solution and I would like to find another way. I can't be the only person on the planet that is asked to rank subtotals and yet, when I called the Lotus help desk, they were clueless as has been every Excel "expert" I know. Any ideas?
mama nancy

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

sorting in Excel ...or 123

by Larry H. In reply to sorting in Excel ...or 12 ...

Using Excel, once you have generated the subtotaled list us the outline bar on the left of the screen to collapse the detail (the button with the 2 on it) so that only the subtotals for each group and the Grand Total are visible. Click in the column containing the subtotal and click the appropriate sort button. This should sort the list from highest to lowest (or vice versa) subtotal. Then, if needed click the button with the number 3 on it to display the detail records. This works fine in Excel 2000.

Collapse -

sorting in Excel ...or 123

by tunnel In reply to sorting in Excel ...or 12 ...

This sounded great but didn't work when I tried it. Perhaps I misled you by not mentioning that when I subtotal, I count as well as totalling so I have two subtotals for each company. Maybe I'm doing something wrong, but when I go into 3 to view all subtotals and try to sort on the dollar collum it doesn't work.
I'm also using Excel 2000.

Collapse -

sorting in Excel ...or 123

by guy In reply to sorting in Excel ...or 12 ...

I would create a Pivot table. Try help pivot table in Excel, they are easy to set up and incredibly powerful. Alternatively you could create a Database in Excel, again try help Database to see how to create. Then you can use the + to get your subtotals.

Collapse -

sorting in Excel ...or 123

by tunnel In reply to sorting in Excel ...or 12 ...

I agree. Pivot tables are very powerful and I though about using one in this case but couldn't figure out how to make it work since pivot tables analyze a one to many relationship...if I'm not mistaken.

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums