Excel Experts

[JohnH]

Any Excel experts out there who can help me with this one? I have a spreadsheet with only about a half-dozen columns and about 4,000 rows of data. I have it sorted by entries in one of the relevant columns and sub-totaled. I can collapse the view so that I only see the sub-totals. There are about 350 sub-total amounts.

Now for the problem. I need to manipulate the table in ascending or descending order according to the sub-total amounts. A "copy" of the collapsed view followed by a "paste special - values" doesn't work - that operation copies the entire set of data. Can anyone steer me in the direction of what is probably a very simple operation?

Thanks...

[GeneInAlabama]

John, I'm not an expert in Excel, but I took a similar spreadsheet that I had and collapsed it to the subtotal amounts and then I just simply clicked on the cell in the 1st row of the column under amount and then clicked on the sort down button (the button with the arrow pointing down), and it sorted the rows by amount in descending order. My spreadsheet only had about a hundred entries instead of 4,000, but it should work just the same. If my description is not clear, let me know and I'll try again. I did learn something from your post. I didn't know that I could collapse the spread sheet down to just the subtotals.

Gene

[redux]

John, I'm not an expert in Excel, but I took a similar spreadsheet that I had and collapsed it to the subtotal amounts and then I just simply clicked on the cell in the 1st row of the column under amount and then clicked on the sort down button (the button with the arrow pointing down), and it sorted the rows by amount in descending order. My spreadsheet only had about a hundred entries instead of 4,000, but it should work just the same. If my description is not clear, let me know and I'll try again. I did learn something from your post. I didn't know that I could collapse the spread sheet down to just the subtotals.

Gene

John:

Data can be filtered to show only the parameters you want. Private me, and we can work out something.

[OldJack]

If your version does not work the way Gene says (having the A-Z down arrow on the toolbar), you can always select "sort" from the menu under "data" in the upper left corner of your screen.

[JohnH]

(Previous message deleted in light of new info just discovered)

Never mind, I found it on an obscure Excel help site.

Thanks for all the suggestions.

Just in case anyone ever needs to do this, here's how:

To copy the subtotals summary:

1. Add Subtotals to the List.

2. In the Subtotal levels, click level 2.

3. Select a cell in the List, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A).

4. To select visible cells, press Alt+;.

OR

Press F5, and in the Go To dialog box, click Special. In the Go To Special dialog box, select Visible cells only and click OK.

OR

Press Select Visible Cells Icon.

5. Copy and paste the summary of the subtotals into a different sheet.

(I would never have figured this one out - the key is to "Select Visible Cells" while in subtotals Level 2)

For anyone interested, here's where I found it.

There are several other good Excel tips on this site.

http://www.exceltip.com/excel_tips/Excel_Subtotals/29.html

[TAXBILLY]

Thanx for the website, JohnH. Never too old to learn something new!

taxbilly