In Excel how would I go about matching data in two different spreadsheets?

Question

I have two different spreadsheets that I need to somewhat merge data from one into the other. Both spreadsheets should have identical 'product description' columns that should be able to match the two spreadsheets together. How would I go about telling Excel to match the data in one column on spreadsheet A to the data in one column on spreadsheet B?

I have 2 different spreadsheets. One with my company's sku# for each product. Another with our supplier's sku# for each product. I'm wanting to match the descriptions (which will be the same in both spreadsheets) and import the supplier's sku# into my company's spreadsheet.

Answer 1

Personally neither one of those methods really works well. About the easiest that Excel offers based on Microsofts features is doing a sort along with possibly an advanced filter. For a long time, I think this is something that Microsoft should have added to Excel. I think they should add a column comparison tool. Excel didn't have it in their program, so I added that capability to my Excel program by writing an Excel macro to do just that. It basically compares the values in one column with the values in another column. Then it takes the matches, finds the value on whatever column of that row the match is on and copies it over to the column where you want the matching data. That little tool I created has saved me months and months of work.

About the only other descent way is to use VLOOKUP like the other person said, but I think that takes a lot of system resources especially for long lists.

Answer 2

If the descriptions are exact, vlookup by description and bring back the SKU. You may have to copy the column over to the right, if it's located to the left, so that it will work.

Answer 3

Are the spreadsheets in the same file - or different files? either way (if I understand your question) you need to specify worksheet location (in single quote) - as part of the cell name such as 'My Worksheet'B1 + 'My Other Worksheet' B1


OK, then I think it is still the same:

if('c:/mydirectory/companyA_SKU'!A1 = 'c:/mydirectory/companyB_sku'!B1) 'c:/mydirectory/companyB_sku'!B1 = 'c:/mydirectory/companyA_SKU'!A1

you must specify directory and file name, them do the 'if'comparison. If they are the same, then do the replacement.



Oops ... I forgot that after you specify directory and filename, you must also spcify worksheet name.

Also, a nifty way the make sure the format is correct is to let Excel do it for you. Open up both spread sheets. Click on the cell in the worksheet you want to copy into. Type "=", then click on the other spreadsheet and cell and hit the enter key. It should show you the fomat for importing that info. Then you enter your comparison statement