Hello all,
I have a question about how to create a cross reference table in Excel to use in Power Query. What I need to create (hopefully automatically) is a table which has 3 columns: Part Number, Company Product Name, Vendor Product Name. I work for a glass fabrication company and we typically get price increases from our vendors twice a year. I have created queries in Power Query to extract the Vendor Product Name and Unit Price for all products from 4 different vendors. I then have a file generated by our software which i use with Power Query to extract Company Product Name and Part Number.
What I need to do now is associate the Company Product Name and Part Number with the Vendor Product Name so I can merge the queries and get a table with the following columns: Part Number, Company Product Name, Vendor Product Name, and Unit Price. This table is then used in a multi-sheet workbook to calculate inventory valuation for different glass types (clear, reflective, patterned, etc).
Is there a way to do this automatically, or do I have to do it manually? The glass names the company uses are similar to the vendor name, but don't necessarily follow a consistent pattern. For example Solarban 90VT on Solargray (vendor name) may be SB 90VT on Gray, while Solarban 70VT on Clear may just be Solarban 70VT. These names are consistent in the system we use, but the pattern of creating the company product names from the Vendor product names hasn't been consistent over the years as new products are added.
I was trying to use VLOOKUP with ISNUMBER(SEARCH()) to look up the Company version in a list of Vendor versions and pull the vendor product name so I would have both names connected to the same part number. Any ideas?
Thank you in advance.