Sorting and adding

  • Thread starter Thread starter Chevyone
  • Start date Start date
C

Chevyone

I have a spreadsheet that has multiple items that are same i.e.(lists
are presorted)

Item quan type
Red 15 625
Red 14 625
Red 12 625
Red 8 625
Red 10 125
Red 9 125
Red 7 125
Red 6 125

Green 100 625
Green 100 625
Green 100 625
Green 50 625
Green 200 514
Green 75 514

I would like to have a method to sort and add like this:
Red 49 625
Red 10 125
Red 32 125

Green 350 625
Green 275 514

The actual sheet maybe hundreds of rows long with 30+ different items

Thanks bunch and have a fun day!
Marty
 
I think I'd use data|pivottable. (I'd also get rid of any blank rows in the raw
data first.)

Select your range
data|pivottable
advance to the dialog that has a "layout" button on it
click that layout button
drag the "Item" button to the row field
drag the "type" button to the row field
drag the "quan" button to the data field

If you see "count of", double click on it and change it to "sum of"

finish up the dialog.
 
I'd use a pivot table to do this.

Select the cells you want to evaluate. I've chosen from

ITEM QUAN TYPE

....

Green 75 514

Data -> Pivot Table and Pivot Chart Report

Select NEXT on the first page. (I leave the info as the default value)
Select NEXT again.
I prefer to have the pivot table on a new worksheet, but that's your choice.
Select FINISH
Drop ITEM into "Drop Row Fields Here"
Drop TYPE into the area that reads "Green/Red ..."
Drop QUAN into "Drop Data Items Here"

Double click in the field that reads "Count of Quan"
Select Summarize by SUM

Try that and come back if you have more questions.
 
The Subtotal function should be able to do what you ask.

Assumption: all data is sorted by *item* first, *type* second.

Highlight your entire range of data (include the header row) and select
Data>Subtotals from the main menu.

for At each change in: Select "item"

Use Function: Sum

Add subtotals to: "quan"

uncheck "Replace current subtotals"

click OK

Now you have subtotals for each item. With the entire range still
selected, again choose Data>Subtotals.

At each change in: "type"

Use Function: Sum

Add subtotals to: "quan"

uncheck "Replace current subtotals" (should be unchecked already)

click OK

Now within each item, you have subtotals for the type. (see attached
..jpg for example)

You can then use the '-' in the left column to hide the rows of data to
display only the subtotals, if desired. These can then be printed,
copied to another sheet, etc.

Does this work for you?

Bruce


+-------------------------------------------------------------------+
|Filename: subtotal.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=3578 |
+-------------------------------------------------------------------+
 
Back
Top