Part Number/Qty Consolidations

[tdanielson2193]

All,

Column A holds several thousand different part numbers
and many of these part numbers occur multiple times ;
Column B holds the associated quantities required for
each part number.

I am trying to find a way to consolidate like part
numbers (with a formula) without using the Excel filter
or sort tool and then add the associated quantities?

Is there a way to show on 'sheet2' column A a
consolidation of individual part numbers and a summation
of their associated quantities?

e.g. if Part No. 12345 occurs in A40, A105, A205, and
A500 and Column B shows respective quantities of 1, 3, 4,
and 8, what formula could be used to show the part only
once in Column A of 'sheet 2' and the corresponding
summation of the quantities (16) in Column B?

I have pondered several different count, lookup and sum
formulas, but all of these require the advanced knowledge
of the part number that your looking for. I am hoping to
simply consolidate like part numbers on sheet 2 with the
summation of quantities.

Please help

Thank you.

T. Danielson.

 

[JulieD]

Hi

use the SUMIF function

with the first part number in A2 on sheet 2
type in B2
=SUMIF(Sheet1!A1:A1000,A2,Sheet1!B1:B1000)

you can then fill this down the list of part numbers in sheet2.

Cheers
JulieD

 

[Ken Wright]

If you don't do yourself the favour of checking out pivot tables for this
kind of analysis you will kick yourself when you finally come across them.

From your post, i would say with reasonable confidence that i could build
that report for you in literally no more than 60 seconds from scratch just
using the wizard, as well as a lot more besides.

See here for an example

http://peltiertech.com/Excel/Pivots/pivotstart.htm