Part Number/Qty Consolidations
 

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.

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

" wrote in
message ...
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.

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

" wrote in
message ...
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.