Match and merge data in Excel sheets

How to merge two Excel spreadsheets, lookup and update data in Excel 2016-2007

Merge Tables Wizard is a timesaving add-in for Microsoft Excel specially designed to quickly match and merge Excel data by a unique identifier. It can become a good alternative to combining data using Vlookup, Lookup and Match.

Free Download
7-day fully functional version

How to work with Merge Tables Wizard: 3 min video

Before you start
Please open both tables in the same instance of Excel before you click the Merge Two Tables icon to start the process. If they are in different files, you can use the File - Open menu item.

Step 1: Select your main table

The main table is the one you want to update. There are 4 ways you can select it.

  • The easiest and recommended way to pick all your data is selecting one cell in your worksheet and clicking the add-in icon. This way it will choose the entire range with your table at start.
  • To limit the range to certain data, select your records, and the add-in will pick up your selection. Please note that if your records are formatted as a table, the add-in will always get the entire range.
  • Another way to specify the range with your main table is using the "Select range" icon in the add-in window.
  • You can also type the range address in the Select your main table field manually.
Note. If you have the standard Excel auto filter option switched on, the add-in will match only the visible filtered rows. If you need to update the entire table, turn the filter off before you run the add-in. If you want to update some part of the main table only, apply Auto filter to it and then only visible rows will be processed and updated.
See the steps you take on the left
Note. If you have hidden rows in your main and lookup tables, they will be processed.

Once you start the add-in, you will see the step you are on at the top of the window.

The first step also lets you get a copy of your original table: tick the "Create a backup copy of the worksheet" checkbox to have it created. We recommend keeping this option selected as Excel doesn’t let you cancel changes made by add-ins.

Click the Next button in the Merge Tables Wizard dialog box to continue.

Step 2: Select your lookup table

The lookup table is the worksheet where you want to search for (look up) matching data. In other words, the add-in will pull information from this table.

You will see all the open workbooks and worksheets in the Select your lookup table area on this step. Choose Excel worksheet with your lookup table and the add-in will highlight its data and enter the range in the field below.

You can edit the range with your lookup table by manually typing it in, by clicking the Select range icon next to the Data range field, or simply using your mouse cursor to highlight it in your table.

Select Lookup Table step

Step 3: Select key columns to find identical records

Your tables may have several columns in common. Key columns are the important ones that let you identify the same records in your sheets, for example IDs or the combination of Last names and addresses. Please note that the values in these columns are only compared; you will be able to select the columns to update on the next step.

This step displays a table with a list of all columns you have in your main sheet. If your tables have header rows, you will see their values in the list. This will make it easier to select columns with common index fields. You can control whether or not the first row is perceived as a label by ticking the Main table has headers and Lookup table has headers options correspondingly. Without the labels, you can look at the 1st row content to make sure you match the right records.

Select matching columns
  • Tick the checkboxes next to the main table columns that you need to compare. When you select a column in the list, the add-in automatically picks a column with a matching header if there is one. If there is no match, please select one in the list of Lookup table columns.
  • If you have several columns with matching headers, take advantage of the Auto Select button that will pick all columns with common labels at once. Pressing Unselect All will remove selection from all columns.
  • If case difference is important for your key values, you can configure Merge Tables Wizard to distinguish between uppercase and lowercase letters in the values it compares. To do this, tick off the checkbox Case-sensitive matching at the top.
Tip. If you have a lot of columns in your tables, you can expand the wizard window by dragging the bottom-right corner down and to the right until you get a suitable size.

Step 4: Select the columns to update in your main table

This step displays a table with a list of main table columns except for those you selected for comparison on step 3.

Tick off the column you want to update and select the lookup table column with the new values from the drop-down list.

Tick off the columns you want to update

You can also click the Auto Select button to select all matching columns at once. Press Unselect All to remove selection from all columns.

Tip. If you have a lot of columns in your table, the counter at the bottom of the add-in window will help you keep track of how many you select.

Step 5: Select the columns to add to your main table

If there are new records in your lookup table, you can choose to add them to your main sheet on this step. You will see a list of Lookup table columns that were not selected as a source for comparison or update on the previous steps.

Tick off the checkboxes next to the columns you want to insert into your main worksheet.

Tick off the columns you want to add

Select or deselect all columns at once by using the buttons Select All and Unselect All respectively.

Step 6: Choose additional options for the merge

The last step offers 3 groups of advanced options that let you fine-tune the merge. All these options are applied to the main table.

  • Choose to add certain rows and columns to the end of your main table
  • Specify how to deal with empty cells
  • Highlight the updated cells
Tip. The "Update" and "Highlight" option groups are available only if you are updating some column. If they are grayed out, please make sure you select at least one column on step 4.
Choose additional options that will be applied to the Master table

Add rows and columns

Add non-matching rows to the end of the main table. Non-matching are the rows with the key values that are not present in your main worksheet. For example, you match tables by the column with IDs. The main table has IDs from 1 to 15. Lookup table contains IDs from 1 to 20. So, the IDs from 16 to 20 in your lookup table are non-matching. When you choose to Add non-matching rows to the end of the main table, the rows with such values will be inserted after all rows in the appropriate columns of the master table.

Add non-matching rows to the end of the Master Table

Paste additional rows that are not present in your main table. You get the possibility to bring over rows with duplicate key values that may contain unique information in adjacent columns. To add such rows to your main table, select the option to Paste additional rows that are not present in your main table and choose between inserting them after the table or after their respective original values.

  • Add the rows with duplicate matching values at the end of the main table will attach repeated rows after all data in the master table. Add rows with duplicate key values
  • Insert rows with duplicate matching values after the row with the same value will adhere extra rows with dupes from the lookup table right after the same key value in your main table, so duplicates end up grouped after the merge. Add rows with duplicate key values after the same key value
Tip. You can run the Merge Duplicates tool to combine these rows and keep all unique information in place.
Add a status column. This option will help you see all changes made to your master table: the add-in will add a new Status column and mark rows as matching, matching and updated, or new row.

Update cells

This group lets you specify how to deal with empty cells in your master and lookup tables.

Update only empty and new cells in the main table. If you have important information in the column you are updating, select this option to fill only the empty cells. The existing values in your main table will not be overwritten with this option selected.

Update only if cells from the lookup table contain data. Select this option if you may have empty cells in your lookup table, and you don’t want them to overwrite the existing values in your master table.

Highlight cells

If the values in the matching columns are the same in both tables, the add-in updates the record in the corresponding column of your Master spreadsheet.

You can highlight all updated records with color by ticking off the checkbox Set background color of updated cells and picking an appropriate hue from the drop-down list. This way you will easily see the changes after joining tables.

Once you select all the options you need, just click the Finish button and enjoy the results.

See a short message with the results

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our support forum
 

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
 
 
60+ 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
 
 
Excel add-ins and Outlook tools - Ablebits.com