Recap of Data

[Tiziano]

Column A of my spreadsheet has several thousand part numbers. Column B has
the code of the customer who purchased that product. Column C has the
quantity purchased. There are many records with the same part number and/or
customer code.

What I would like to obtain is a recap of all the data where column E would
have the list of all part numbers (no duplicates!), column F the number of
customers that purchased that particular product, and column G the total
quantity sold.

Do you have any suggestions?

Thanks.

 

[Fred Smith]

I expect a Pivot Table is exactly what you want.

Look here for more information:
http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[Max]

One ideal way to drive it out ... use a pivot table (PT)

Assume the source table is as per below
(with top row col headers)

Part# CustCode Qty
P00001 C0001 90
P00001 C0002 50
P00003 C0003 40
P00002 C0001 60
P00005 C0003 90
P00003 C0001 20
P00003 C0002 100
P00002 C0003 20
P00004 C0002 100
etc

Select any cell within the source table
Click Data > Pivot table
Click Next > Next

In step 3 of the wiz. click Layout
Drag n drop Part# within the ROW area
Drag n drop CustCode within the DATA area
(It'll appear as Count of CustCode)
Drag n drop Qty within the DATA area
(It'll appear as Sum of Qty)
Click OK > Finish.

Hop over to the PT sheet
Just drag Data n drop it over "Total"
This'll transform it to the desired results table, viz:
a) a unique listing of the Part# and
b) the corresponding # of cust (count of custcodes) and the total qty

Data
Part# Count of CustCode Sum of Qty
P00001 2 140
P00002 2 80
P00003 3 160
P00004 1 100
P00005 1 90
Grand Total 9 570