data consolidation

  • Thread starter Thread starter Allie
  • Start date Start date
A

Allie

Hi

I need some help consolidating data for PivotReport use.

Say I had the following data

PO, Item, Quantity, Price
1801, A, 1, 500
1801, A, 1, 500
1801, A, 1, 500

What would be the easiest way to roll it all up together
to produce one line??
1801, A, 3, 1500

Also, if I had the following data

1802, b, 1, 500
1802, a, 1, 500
1802, c, 1, 500

IS there any way to have the Pivot recognize that as ONE
rather than as 3 seperate items? (I'm trying to get a
count of the number of unique purchase orders as opposed
to a count of the number of lines)

Thanks for any help.

Finally- if you were going to recommend a book for data
analysis in Excel, any suggestions?
 
Hi Allie
for the first question:
try using the subtotals (goto 'Data - Subtotals')

To count the number of unique oder IDs (in column A) use
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
 
Back
Top