Consolidate/VLOOKUP/Pivot Table?
 

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.
--
Grateful

Consolidate/VLOOKUP/Pivot Table?
 

Steve wrote:
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.

Consolidate/VLOOKUP/Pivot Table?
 

Brilliant! Thanks.
--
Grateful


"smartin" wrote:

Steve wrote:
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.

Consolidate/VLOOKUP/Pivot Table?
 

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?
--
Grateful


"smartin" wrote:

Steve wrote:
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.

Consolidate/VLOOKUP/Pivot Table?
 

Steve wrote:
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.

I wrote:

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.