How can I add duplicated items in a list? Help!

C

Class316

Ok, here's what I have


type quantity

a 1
a 2
b 3
b 3
b 1
c 2
c 2

I need to have:

a 3
b 7
c 4

What is the best way to do this without tedious summations and
deletions? thanks in advance!
 
P

Pete_UK

Copy the type column (including heading) to another sheet. While the
data is still highlighted, click Data | Filter | Advanced Filter and in
the dialogue box click Unique Records only and Copy to New Location -
specify $C$1 - then click OK. Delete columns A and B. You might like to
sort the items in column A at this stage. Put a heading like "Total" in
B1 and in B2 enter this formula:

=SUMIF(Sheet1!A$2:A$1000,A2,Sheet1!B$2:B$1000)

Adjust the range references to suit your data, then copy down column B
for as many unique values as you have in column A.

Hope this helps.

Pete
 
S

SteveG

Assuming your data is in A1:B7. In say A10:A12 you enter a,b&C
respectively. Use this in B10 and copy down to B12.

=SUMPRODUCT(($A$1:$A$7=A10)*$B$1:$B$7)

HTH

Steve
 
K

Kevin B

You can use sub totals for the purpose and collapse the outline to level 2 to
show just summations at each change.

Sort your data by type and click DATA in the menu and select SUBTOTALS. Set
the value for the AT EACH CHANGE IN to TYped, set USE FUNCTION to Sum and
check QTY in the ADD SUBTOTAL TO list.

You should get 3 outline buttons to the left of column row in the
workhsheet. Click 2 to view only the totals at each change in Type.
 
R

Ron Coderre

Try a Pivot Table this:

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Type field here
DATA: Drag the quantity field here
If it doesn't list as Sum of Dates...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Type and the total quantity for that type.

To refresh the Pivot Table, just right click it and select Refresh Data

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top