How do I sort or compare data from 2 different spreadsheet columns

[Guest]

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.

 

[Dav]

If you wanted to avoid macro, I would paste both lots of data onto th
same sheet in the same columns. If U then create a new column calle
product and copy the product description next to the parts it relate
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 total
they will be highlighted and can be deleted

If you select the whole table then copy it as values elsewhere, yo
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

Da

 

[Guest]

Dav, thanks! I'll give it a try