Summing Duplicates

[dsjrcw]

Following is a sample of data exported from our DOS-based
order entry system in CSV format:

PART# QUANTITY LIST SALE
PRICE PRICE


OP-AHS-100 2 Each $46.75 $37.40
OP-AHS-025 1 Each $13.00 $13.00
OP-PRG-100-H 2 Each $44.50 $44.50
OP-PRG-025-H 1 Each $12.50 $12.50
AP-AHS-025 1 Each $75.75 $75.75
AP-PRG-025 1 Each $12.50 $12.50
AP-SSS-025 1 Each $0.00 $0.00
* 0 $0.00 $0.00
* 0 $0.00 $0.00
OP-AHS-500 1 Each $220.50 $220.50
AP-AHS-500 1 Each $945.50 $945.50
OP-CCG-500-H 1 Each $549.50 $549.50
AP-SSS-500 1 Each $0.00 $0.00
OM-AMS-500 1 Each $143.50 $143.50
OM-AMS-100 1 Each $36.00 $36.00
OM-MAN 1 Each $5.50 $5.50
OM-KEY 1 Each $17.50 $17.50
* 0 $0.00 $0.00
* 0 $0.00 $0.00
P2-AHS-500 1 Each $454.75 $454.75
OP-WCC-ILL 1 Each $51.75 $51.75
OP-AHS-100 1 Each $46.75 $46.75
OP-PRG-100-H 1 Each $44.50 $44.50
OP-AHS-100 1 Each $46.75 $46.75
OP-PRG-100-C 1 Each $44.50 $44.50
AP-AHS-100 1 Each $280.25 $280.25
PE-AHS-100 1 Each $46.75 $46.75
PE-AHS-025 2 Each $13.00 $13.00


I need to create a report that sums all identical
PART#/Sale Price entries into a format and total for each
like this:


PART# LIST SALE #ENTRIES QUANTITY
PRICE PRICE


OP-AHS-100 46.75 46.75 2 4
46.75 37.40 1 2


TOTAL: OP-AHS-100 3 6


With over 100 part#, it would be very time consuming to
use SUMIF for each. Is there a way for Excel to look for
identical entry-pairs and sum them? Would Access be a
better tool for this? We currently use a very old version
of Paradox for this assignment and I would like to use a
current program for this task. Thanks for any advice you
can give!

 

[Bernard Liengme]

A pivot table is the answer.
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

best wishes

 

[Don Guillett]

You could use a macro that makes a separate list of the unique entries from
advanced filter. The same macro could then do the formula for each unique
part number, either leaving the formula or just the data. ONE mouse click
for all 100 part nums.

 

[dsjrcw]

A pivot table is the answer.
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/downloa....com/assistance/2002/articles/xlconPT101.aspx

best wishes
--
Bernard V Liengme
Microsoft Excel MVPwww.stfx.ca/people/bliengme
remove caps from email













- Show quoted text -

Thanks for your help!! I've used the pivot table and formating to get
the data as follows:

PART# SALE PRICE QUANTITY
COUNT OF PART#
Part-025-SP
$51.75 1
1
$51.75 Total 1 1

$55.25 8
5
$55.25 Total 8 5

Is there a way to create a sub-total for each part number, in the
above example $51.75 x 1 + $55.25 x 8 so the sub-total would be
$493.75?

Thanks Again!