Sep
20

How to merge two Excel Worksheets by partial cell match

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.

Find partial text match in two tables
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:

Note: The solutions described in this article are universal. You can adapt them for further use in any standard Excel formula like Vlookup, Match, Hlookup etc.
If you are not an Excel guru and you don't really understand how to create and use formulas.

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

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.
Non-exact match in two Excel tables

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:
    Point mouse cursor to the column name
  • Right-click on it and select the "Insert" menu item:
    Select to insert a column in the menu
  • 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.
    Enter the formula
  • 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:
Use partial match to merge data

Other formulas

  • 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 =right(A2,6)
  • 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 =LEFT(A2,FIND("-",A2)-1)

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.

The data from the key column in Table 1 take 2 or more columns in Table 2

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.
Key values split to different columns

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:
Copy the formula to all 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:
Match records and add the missing data

The data in the key columns don't match at all

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 :-(.
No partial matches in your Excel tables

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:
Lookup table with matches

2. Update Master worksheet using data from "Lookup helper" table

Insert a new column to our Master table (Store worksheet), "Supp.SKU"
Add a new column to the Store worksheet

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.
Fill in the supplier's SKU

Note: If the column "Supp.SKU" has empty cells, you need to take all the SKUs from "Our.SKU" that are next to these empty cells, add them to the "SKU converter" table and find the corresponding code from the supplier's table. Then repeat step 2.

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:
Get the merge result in 5 quick steps

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.

4 Responses to "How to merge two Excel Worksheets by partial cell match"

  1. Saikat says:

    Can you please add a video of this excel tip. That will help us a lot.

  2. Tom says:

    I like your raw data examples.

  3. Randy says:

    I am relatively new to Excel. I am in the process of comparing two columns of data to determine whether the data in each column relate. Column A has the key name from one product in one system and Column B has the key name from the same product in another system. There are 55,000 rows of key names to go through and I would really like to find an easier way to compare than just doing it by eye.

    The key names don't have to match exactly they just have to be similar.

    For example,

    COLUMN A:
    Bourbon Seagram BTL
    SS Bailey's
    Vodka Skyy BTL
    SS House Rum

    COLUMN B:
    BTL Seagram
    Bailey
    BTL Skyy
    Vodka (Column C would show that this doesn't match Column A)
    Is there an easier way to go through the two columns and find cells that don't match at all? Please help!!!

    Thanks,
    Randy

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard