Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consolidate data in pivot table | Excel Discussion (Misc queries) | |||
Consolidate Pivot table from multiple files | Excel Discussion (Misc queries) | |||
Using Pivot Table to consolidate multiple worksheets | Excel Discussion (Misc queries) | |||
how do I consolidate multiple pivot tables into one pivot table? | Excel Discussion (Misc queries) | |||
Consolidate data from several worksheets via pivot table | Excel Worksheet Functions |