Consolidate/VLOOKUP/Pivot Table?

S

Steve

Hi - I have a sheet of part numbers (tens of thousands of parts). Each part
has a part number, number of transactions, dollar value, etc... five columns
of data for each part/row... six columns total in this sheet.

I've been given a SECOND list of part numbers, with only one additional
column: a Category with values of "Core" or "Non-Core". This list of parts
is not identical to the earlier list... some of the same items are on both
lists, some are only on one list.

I need to create a single list with ALL the part numbers from both lists,
and all data for each part.

I've tried a pivot table, but the "Core" or "Non-Core" column shows up as 0
or blank.

Thanks in advance.
 
S

smartin

Steve said:
Hi - I have a sheet of part numbers (tens of thousands of parts). Each part
has a part number, number of transactions, dollar value, etc... five columns
of data for each part/row... six columns total in this sheet.

I've been given a SECOND list of part numbers, with only one additional
column: a Category with values of "Core" or "Non-Core". This list of parts
is not identical to the earlier list... some of the same items are on both
lists, some are only on one list.

I need to create a single list with ALL the part numbers from both lists,
and all data for each part.

I've tried a pivot table, but the "Core" or "Non-Core" column shows up as 0
or blank.

Hi Steve,

First use VLOOKUP from list 1 to list 2 to add the "core/non-core" field
to parts in list 1.

Then use VLOOKUP from list 2 to list 1 to identify which parts do not
exist in list 1. Sort list 2 on the lookup result, then copy the
unmatched parts (#N/A results) in list 2 and paste append in list 1.
 
S

Steve

Brilliant! Thanks.
--
Grateful


smartin said:
Hi Steve,

First use VLOOKUP from list 1 to list 2 to add the "core/non-core" field
to parts in list 1.

Then use VLOOKUP from list 2 to list 1 to identify which parts do not
exist in list 1. Sort list 2 on the lookup result, then copy the
unmatched parts (#N/A results) in list 2 and paste append in list 1.
 
S

Steve

smartin- I've run into a snag. When I append the #N/As to the first list,
the VLOOKUP from second list kicks in and recognizes those part numbers...
and I lose the accurate count. any ideas?
 
S

smartin

Steve said:
smartin- I've run into a snag. When I append the #N/As to the first list,
the VLOOKUP from second list kicks in and recognizes those part numbers...
and I lose the accurate count. any ideas?

Hi Steve,

Not sure why, but your post did not appear until today on my news server.

I rather thought you could do away with list 2 after all that, but if
you want to preserve the results, try this.
Then use VLOOKUP from list 2 to list 1 to identify which parts do not
exist in list 1.

Before continuing, select the VLOOKUP column, copy, and Edit | Paste
Special | Values.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top