Hi,

I have just subtotaled an inventory spreadsheet at work and now have 13,000
lines of data. Now that I have the subtotals by inventory item, I want sort
so that I have all the subtotals together as a group, and all the data that
went into the subtotals separated out. But Excel refuses to do this, even
though I have two columns that I would be able to do this by because the
subtotals have nothing in those two columns and the other lines do have data
in those two columns. I am very frustrated, because going through 13,000
lines of data to delete this information by hand will be extremely
time-consuming!!! Please help!!!

Thank you,
Katherine

You can calculate the subtotals using array formulas (look in help or
book on "array formula") assuming that you have some column (e.g., an
accounting category / account number) containing an entry that is
identical for all of the lines in any given subtotal.

If you aren't worried about dynamically updating the data, you can
Copy, Paste Special, values before moving the rows containing
subtotals to a separate area.

You can give a name to each subtotal (use the account number) and link
to the actual subtotals from an array that has only the subtotals one
after the other by themselves.

A pivot table may give you what you are looking for. Look up Pivot
Table in the help.

Note that Excel sorts the data, and does not sort rows. Hence any
named ranges don't move with the data when sorted.

It's not an easy solution. You will need to do a bit of study.

Fred Holmes

On Tue, 25 Jan 2005 11:15:05 -0800, Katherine
wrote:

Hi,

I have just subtotaled an inventory spreadsheet at work and now have 13,000
lines of data. Now that I have the subtotals by inventory item, I want sort
so that I have all the subtotals together as a group, and all the data that
went into the subtotals separated out. But Excel refuses to do this, even
though I have two columns that I would be able to do this by because the
subtotals have nothing in those two columns and the other lines do have data
in those two columns. I am very frustrated, because going through 13,000
lines of data to delete this information by hand will be extremely
time-consuming!!! Please help!!!

Thank you,
Katherine

Did you use the Subtotal feature to create the subtotals, or did you
create them manually?

Instead of subtotals, you may find it easier, and more flexible, to use
a Pivot Table to summarize the data. There are instructions in Excel's
Help, and Jon Peltier has information and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm


Katherine wrote:
Hi,

I have just subtotaled an inventory spreadsheet at work and now have 13,000
lines of data. Now that I have the subtotals by inventory item, I want sort
so that I have all the subtotals together as a group, and all the data that
went into the subtotals separated out. But Excel refuses to do this, even
though I have two columns that I would be able to do this by because the
subtotals have nothing in those two columns and the other lines do have data
in those two columns. I am very frustrated, because going through 13,000
lines of data to delete this information by hand will be extremely
time-consuming!!! Please help!!!

Thank you,
Katherine


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html