Flatten recurring items in Excel?

  • Thread starter Thread starter davwhsdb
  • Start date Start date
D

davwhsdb

I was wondering if there is a built in Excel function to do the
following:

Say I had 2 columns: item and quantity. The item column may have a
bunch of repeated entries, each with a different quantity. I want to
end up with only 1 row that has the item and a summation of the
quantities.

For example:

Item Qty
A 2
B 1
A 3
C 1

Becomes

Item Qty
A 5
B 1
C 1

Thanks in advance,
Dave
 
You could add headers and then
sort your data by the first column
Then use data|subtotals to get close to what you want.


Alternatively, you could use data|pivottable to create a summary report.

in xl2003 menus:
Add headers in row 1
Select the range
A1:B###
Data|Pivottable
Follow the wizard until you get to a step with a Layout Button on it.
Click that layout button
Drag the header for the Item to the row area
Drag the header for the Qty to the Data area
(rightclick on it and choose Sum--if you don't see Sum)
and finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
I had found the subtotal thing right after I posted this question and,
you're right, it gets it close.

The pivot table works great though, thanks!
 
A more direct approach:
Data, Filter, Advanced
Set List range to the Item column, leave criteria blank, check Unique
records and select an output range (say column D). In the column next to the
output range (say cell E2) put this formula:
=SUMIF(D:D,"="&A2,B:B)
Copy down the E column.
(This assumes that Item and Qty are in cols A&B respectively, with headings
in Row 1)
 
Back
Top