I am trying to simplify the process for identifying 'like' items used on
different MODELS of the same Basic PRODUCT. For example say: O-ring #12345 -
is used on SUPER VAC I(1ea) - and used on SUPER VAC II(3ea) - and used on
SUPER VAC III(4ea). I am starting with a different spreadsheet for each
MODEL# that include comumns for PART NUMBER, DESCRIPTION, COST and QUANTITY,
and what I am looking to create is a single spreadsheet for each BASIC
ASSEMBLY (SUPER VACS) with all the part numbers listed. Since the
description and cost of each part would stay the same, how can I consolidate
the data into one spreadsheet per Assembly, sorted by Part Number,
consolidating the duplicate part #'s into one row each that not only includes
'description' and 'cost', but also includes a column for each 'where used'
'and 'quantity'? I am using Microsoft Excell 2000.

If you wanted to avoid macro, I would paste both lots of data onto the
same sheet in the same columns. If U then create a new column called
product and copy the product description next to the parts it relates
to.

you can then run a pivot table, select all the data
Choose PART NUMBER, DESCRIPTION, COST as row headings,
product as column headings
Quantity as data (it should default to sum which is fine)
A new table will be created, if you then click on any of the totals
they will be highlighted and can be deleted

If you select the whole table then copy it as values elsewhere, you
have a table that can be updated an dis not dependent upon data

I hope this is clearish, if not please get back in touch

Have fun

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=507086

Dav, thanks! I'll give it a try

"Dav" wrote:


If you wanted to avoid macro, I would paste both lots of data onto the
same sheet in the same columns. If U then create a new column called
product and copy the product description next to the parts it relates
to.

you can then run a pivot table, select all the data
Choose PART NUMBER, DESCRIPTION, COST as row headings,
product as column headings
Quantity as data (it should default to sum which is fine)
A new table will be created, if you then click on any of the totals
they will be highlighted and can be deleted

If you select the whole table then copy it as values elsewhere, you
have a table that can be updated an dis not dependent upon data

I hope this is clearish, if not please get back in touch

Have fun

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=507086