combining like data

[Mike]

I have a worksheet with 2 columns.
Col1 is "Name"
Col2 is a number

There are many duplicates in column 1. I would llike to combine those
duplicates in column1 into 1 cell, and total the numbers in column 2.

--Mike

 

[mzehr]

Sounds like a pivot table is in order

Check out these sites for a good starting point:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm
http://www.contextures.com/xlPivot02.html

HTH

 

[Debra Dalgleish]

You could use a pivot table to summarize the data. There are
instructions and links on Jon Peltier's web site:

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

 

[AlfD]

Hi!

A simple basic way might be to use the Data>Subtotals... function t
collect the duplicates together and sum them.

Steps are:
Col A contains list with duplicates
Col B contains corresponding amounts.

Sort the datatable on Col A.
Use Subtotals... selecting changes in Col A and SUM of Col B.

Use the middle button of the 3 little buttons which have now appeare
top-left (part of the subtotals outcome).
This will give a table with only the subtotals in it )col B) and thing
like Cherry Total and Peach Total where Cherry and Peach are items i
Col A.

Select the visible cells (Excel 2003 has a button for it: other peopl
tend to use F5 / Special /Visible cells only /OK.

Ctrl-C to copy them.
Ctrl-V to paste them somewhere suitable. I emphasise suitable: it mus
not overlap the rows in the subtotal array or you will lose rows
Another worksheet might be a good thing!

Now you have a table with the data yo want but a few extraneous words.
Use find/replace on the Cherry Total type of text: find Total an
replace with nothing.

Al