Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recap of Data
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. -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recap of Data
I expect a Pivot Table is exactly what you want.
Look here for more information: http://peltiertech.com/Excel/Pivots/pivottables.htm -- Regards, Fred "Tiziano" wrote in message ... 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. -- tb |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recap of Data
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiziano" wrote in message ... 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. -- tb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Information in Mutiple Workbooks Not Displaying in my Recap Sheet | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
how do i lookup and sum from serveral wkshts to a recap sht? | Excel Worksheet Functions | |||
Recap Worksheet | Excel Worksheet Functions | |||
Create a "recap" worksheet that includes all info from all worksh. | Excel Worksheet Functions |