Aggregate Unique part no in cells to sum qty

[Guest]

I have been trying to use :subtotlas, consolidate, IF, & pivottables to get a
spreadsheet of inventory #'s (same part # in various cells) and want to
aggregate/consolidate those to 1 record with a TOTAL QTY value that I can
then use to import to a program.

I have tried the if command and get no values
I have not been able to successfully use the consolidate function
the subtotals function kind of works BUT does not give me the summed total
on 1 line with the part # for export
And pivot table method returns a COUNT on the qty field NOT a sum of all
same part # ???

Suggestions please I am stuck

 

[Guest]

Hi,

Can you give us an example?

Thanks,

 

[Guest]

PARTNO QTY
aa 2
bb 1
cc 3
aa 1
cc 10
aa 6

I have a list like this of 9000 cells and want to aggregate the unique part
#'s into 1 line with part # and TOTAL QTY
So the result from above would be like:

aa = 9
bb = 1
cc = 13

Any detailed explanation on how to do this is appreciated. the closest I
came was using data/subtotals but even that was not right in that the TOTALS
line did not contain the TOTAL and I could not see how to get just the unique
lines with the total qty

 

[Guest]

My preference would be a pivot table. Place your cursor in the middle of the
source data somewhere and select Data|Pivot Table -> a wizard will pop up. In
all likelyhood you can just select finish and it will make all of the correct
choices for you. A new sheet will be created with a pivot table on it. Drag
the Part numbers field to the left had column and the Quantity to the middle.
That shoud do it for you... for more info see

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

 

[Guest]

I cannot get the QTY output to be a sum of the part #, it keeps doing count
and the when i dbl click the QTY field in data and change to SUM it shows 0
in all cells?

I have tried to change this several ways and cannot seem to get it to work??

Help