This article will show you how to quickly merge data from two Excel tables when there are no exact matches in your key columns, e.g. your unique ID in the first table may make up the first five characters of the ID column in your second table. I have tested all the solutions I present in this article, they work in Excel 2013, 2010, 2007.
You have two Excel worksheets with the data you want to merge and analyze. Say, one table contains the prices and the descriptions of the products you sell; the other is a report about the stock. If you or your colleagues created both tables by the book, each sheet has one or more key columns with unique product IDs. The description or the price can change, but the unique ID is always the same.
The problems come with the tables you get from the manufacturer or another department. They may also arise when the format of your IDs changes, or the Stock Keeping Units (SKU) become a little different, and you try to merge the new Excel table with the data from an old one. Anyway, you end up with only partial text match in your key columns, e.g. "12345" and "12345-newsuffix". You know this SKU is just the same, but the computers are not as smart :-( This non-exact match makes it impossible to use Excel formulas for merging data from 2 worksheets.
What makes it all the worse is that the match may be indistinct, "Some company" in one table can turn to "Some Company Inc." in the other worksheet, "New Company (former Old Company)" and "Old Company" are the same record as well. You know this, but how do you explain it to Excel?
You can always find a way, please read on to see the solution:
In this case I strongly recommend using our Merge Tables Wizard for Excel to combine data from your worksheets.
This commercial add-in is designed for fast and intuitive data merge in Excel. This is the only thing it does, and it does it very well! Instead of writing complex formulas, you just start the wizard and follow 5 simple steps to get the results you need. You can download a fully-functional version of it and see how it works with your data.
You can work with it for 15 days, and then you can pay for it. The add-in is worth it, we ask this amount for saving your time and nerves: an hour for learning the vlookup/match functions used in Excel for combining data, about 4 hours you'll spend on learning the specificities of vlookup function and 10-20 hours you'll spend to understand the pitfalls, I assure you there are many. We spent several hundred hours to find and avoid them.
Pick your case to go to the right solution:
- The key columns in one of your tables contain extra characters.
- The data from the key column in Table 1 take 2 or more columns in Table 2.
- The data in the key columns don't match at all (123-SDX vs HFGT-23) or the partial match type changes from cell to cell ("Coca Cola" vs "Coca-Cola Inc.")
Say, you have 2 tables. Table one has SKU, the name of the beer, and the price. Table two has SKU and the number of bottles in stock. It can be any product and the number of columns can be much bigger in real life.
We create an additional column in the table where we have extra characters. We can add it to the end of the table, but the best place is right next to our key column, so that we can see it straight away.
Our key column is column A with "SKU", and we need the first 5 characters in it. We add an additional column to the right that we name "SKU helper":
- We point our mouse cursor to Column B name and see it turn into an arrow down:
- Right-click on it and select the "Insert" menu item:
- Name the column "SKU helper".
- As we need only the first 5 characters from the "SKU" column, we enter the formula
=LEFT(A2,5)into B2 cell.
Here A2 is the address of the cell, from which we want to copy the characters, and 5 is the number of symbols we need to copy.
- Copy the formula across all the cells in our new column
That's it! Now we have the key columns with exact matches, "SKU helper" in our main table & "SKU" in the lookup table.
Now we can use Excel Vlookup or Merge Tables Wizard to get the result:
- Take X characters from the right side: e.g. 6 numbers from the right side of DSFH-164900, the formula is going to look like this
- Miss the first X characters, then take Y characters: e.g. you need to get "0123" from PREFIX_0123_SUFF. The formula is
=mid(A2,8,4). We miss the first 8 characters and then take the next 4.
- Take all characters before the delimiter, the length of the resulting value can differ. E.g. we need 123456 and 0123 from 123456-suffix, 0123-suffix respectively. The formula is
Overall, you can use left(), right(), mid(), find() Excel formulas to extract any complex number. If you have any difficulties, please contact us, we'll do our best to assist you.
Say, your Lookup Table has a key column with IDs. The cells contain values like XXXX-YYYY, where XXXX is the product group code (Mobile phones, TV sets, camcorders, cameras), and YYYY is the product code within the group. Your Master table has 2 separate columns: one is for the product group codes (Group), the other is for the product code (ID). We can't just discard the product group code for the merge as the same product code can be present in different groups.
We add an additional column to our Master Table and name it "Full ID" (column C), please see the previous step for detailed instructions.
We write the formula
=CONCATENATE(A2, "-",B2) into C2 cell. Here A2 is the address of the group ID cell, "-" is the delimiter, B2 is the address of the cell with the product ID. Copy the formula to other rows:
Now we can easily merge data from our spreadsheets. We compare the "Full ID" column of the Master table to the "ID" column of the Lookup table. If there is a match, we add records from the "Description" and "Price" columns we had in our Lookup table to our Master spreadsheet:
Here is an example: you have a little shop, and you get the products from one or several suppliers. Each has their own SKU naming system, yours is different. This leads to the situation when your "Case-Ip4S-01" is "SPK-A1403" in your supplier's Excel file. It just happened so and there's no universal rule that would turn "SPK-A1403" into "Case-Ip4S-01" automatically :-(.
The bad news is that you'll need to process the data from these 2 Excel tables manually to be able to merge them.
The good news is that you need to do it only once and you can leave this auxiliary lookup table for further use. Then you'll be able to merge the tables automatically and save a lot of time :-)
1. Create a lookup helper table
Create a new Excel sheet and name it "SKU converter". Copy the entire column "Our.SKU" from our "Store" sheet to the new one, delete duplicates and leave only unique values.
Add column "Supp.SKU" next to it and manually look for the matches of "Our.SKU" <-> "Supp.SKU" (by the description that matches most). This work is boring, let the thought that you have to do it just once comfort you :-).
You'll get a table like this as a result:
2. Update Master worksheet using data from "Lookup helper" table
Insert a new column to our Master table (Store worksheet), "Supp.SKU"
Then use Merge Tables Wizard or the vlookupfunction to compare the "Store" worksheet to "SKU converter" using Our.SKU as a matching column, and update column "Supp.SKU".
You'll get the "Supp.SKU" column filled in with the original supplier's code.
3. Merge data from Lookup table to Master Table
Our Master table has a key column with the exact match for the Lookup table, so it's really simple now :)
Use Merge Tables Wizard again to merge the "Store" worksheet with the data from "Wholesale Supplier 1" worksheet using "Supp.SKU" as a matching column.
For example, update data in the "Wholesale price" column. 5 quick steps and we get the result:
Everything is simple, isn't it? Feel free to ask your questions in the Comments section, I'll do my best to reply as soon as possible.