How to use Merge Two Tables for Excel

The Merge Tables Wizard add-in can match and merge data from two Excel worksheets in seconds. This smart tool is an easy-to-understand and convenient-to-use alternative to Excel Vlookup/Index+Match functions.

Video: How to merge two tables in Excel

Before you start

Excel instances

Open the Excel workbooks that contain the tables you are going to compare. Both tables should be opened in the same instance of Excel.

Hidden rows

If you have hidden rows in your main and lookup tables, they won't be processed.

Backup copies

Pay attention to the Create a backup copy of the worksheet checkbox. We recommend keeping this option selected as Excel doesn't let you cancel changes made by add-ins.

How to use Merge Tables Wizard

Start Merge Tables

On the Ablebits Data tab in the Merge group, click the Merge Two Tables icon:
Merge Two Tables in Excel.

Step 1: Select your main table

The main table is a table you merge with a lookup table. Your data will be updated only in the main table.
Select the main table.
There are 4 ways to select the main table:

  • Select any cell in your worksheet before running the add-on, and the whole table will be highlighted automatically.
  • To limit the range to certain data, select your records, and the add-in will pick up your selection.
    Note. If your records are formatted as a table, the add-in will always get the entire table.
  • If you'd prefer to have only a certain range highlighted rather than the whole table, use the Select range icon in the add-in window.
    Select the needed range.
  • 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 starting the add-in.

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

Step 2: Pick your lookup table

The lookup table is a worksheet or range where you search for (look up) matching data. The add-in will pull information from this table. The lookup table remains intact after the add-in merges two tables.
Choose the lookup table.
On this step, you see all the open workbooks and worksheets in the Select your lookup table area. Choose the Excel worksheet with your lookup table and the add-in will highlight the used range.

Tip. You can edit the range by clicking the Select range icon or simply using your mouse cursor to select it in your table.

Step 3: Select matching columns

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 First and Last names. 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.

Here you can see a table with a list of all the columns you have in your main sheet. Tick the checkboxes next to the columns you need to compare. Once chosen, the add-in will automatically pick a column with a matching header in a lookup table if there is one. If there is no match, please select one in the drop-down list of Lookup table columns.
Pick matching columns.

  1. If your tables have header rows, select the Main table has headers and/or Lookup table has headers checkboxes. If your tables do not have headers, clear these boxes. In this case, the contents of the 1st row will be displayed to help you match the right records.
  2. If text case in the key columns is important to you, tick off the Case-sensitive matching checkbox at the top. This will instruct the Merge Tables Wizard to distinguish between uppercase and lowercase letters in the values it compares.
  3. Click Auto Select to get all the columns selected as matching.
  4. Pressing Unselect All will remove selection from all columns.
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.

Click Next to continue.

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

On this step, select the columns you want to update in the main table and pick the corresponding columns from the lookup table with the new values:
Select the columns 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.

Press Next to proceed.

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

If there are new records in your lookup table, you can 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:
Choose the columns to add.
Select or deselect all columns at once by using the buttons Select All and Unselect All respectively.

Step 6: Choose additional merging options

The last step offers advanced options that let you fine-tune the merge. All these options are applied to the main table:
Adjust the merging options.

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. The 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 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 main table:
    How to add non-matching rows.

  • Insert additional matching rows
    Select this option to add rows with duplicate key values that may contain unique information in other columns. You can paste additional matching rows at the end of the table or after the row with the same value in the key column.

    • Attach repeated rows after all data in the main table by choosing the At the end of the main table option:
      Additional matching rows at the end of your table.
    • Or insert additional rows from the lookup table right after the same key value in your main table and get all the duplicates grouped together. For this, select After the row with the same key value:
      Collect all dupes together.

      Tip. You can run Merge Duplicates to combine these rows and keep all unique information in place.
  • Set background color for all added rows
    Choose this option to mark the added rows with a background color.
  • Add a status column
    If you tick this option, the add-in will add a new column to your main table and mark rows as Matching, Matching and updated, or New row.

Update cells in the main table

This group lets you specify how to deal with empty cells in the main table.

  • Empty cells only
    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.
  • Only if cells in the lookup table contain data
    Choose 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 main table.

Highlight cells

You can highlight all updated records in your main table with color by ticking off this checkbox 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, click the Finish button and enjoy the results.
Merge two lists in Excel.

Responses

Hi,

My company is interested in buying the product, yet we wanted to solve a doubt first:
Can it combine multiple workbooks with multiple sheets to update a "master" workbook, but giving the option to choose which information we want to preserve? Or do you have another product that could do it?

Thanks.

ACC

Reply

Hi!

Thank you for your interest in our products.

I think Combine Worksheets Toolkit is the add-in you need.

Have a closer look at Ultimate Suite for Excel, it includes more than 60 tools to boost your Excel.
By the way, you still can take advantage of the Special May Offer!

Reply

I want ONLY the Merge Two Worksheets wizard, but you charge $100 for the entire Ultimate Suite, which I do NOT want. You were offering the Merge Tables tool for $60, what happened to it?

Reply

Hi Jim,
Thank you for your comment. You still can get Merge Tables Wizard without buying the entire Ultimate Suite. Please go here and click the Buy Now button under the Merge Tables Wizard section.

Reply
Mathew D Wolf says:
June 25, 2018 at 4:09 pm

Hello, I am using the Merge Two Tables application and I am a trying to get every instance to merge but only the first instance is merging and leaving he rest of the merge fields blank. Please assist me.

Reply

Hi Mathew,
Thank you for using the Merge Two Tables tool! Please contact our support team at support@ablebits.com and describe your task in detail. It would be perfect if you could also attach your workbook, the result you get with Merge Two Tables on a separate sheet, and a sample of the result you would like to get. This will help us a lot to understand the problem and assist you.

Reply
Abhijeet Tiwari says:
August 13, 2018 at 1:55 pm

Hello,

I am trying to Merge two worksheet using this add-on but it is showing an error message as follows.

'' The add-in can't add new records because it encounter Excel limit for the total number of rows in a sheet''

Kindly help me to solve this.
Thank you

Reply

Hi Abhijeet,
Thank you for using the Merge Two Tables tool! Please note that our products don't impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. Here you can find the limits for rows and columns.
If the number of rows in your sheet does not exceed the limit, please contact our support team at support@ablebits.com. Please attach your book and describe the Merge Two Tables steps you take in detail, mention all the options you tick in the wizard.

Reply
Antonio Casas says:
October 15, 2018 at 8:36 pm

I decided to take the 7 Day Trial offer for the Merge Table Wizard. However, it only worked the first time. Thereafter, it skips steps 4 of 6 and 5 of 6. I wanted to test with it some more before I buy it. Please advise.

Thanks,

A. Casas

Reply
Antonio Casas says:
October 15, 2018 at 9:07 pm

Never Mind.
Please ignore my previous e-mail claiming that Steps 4 and 5 were not being executed. I closed all sessions of XCEL, restarted the process and it worked just fine.

A.Casas

Reply
Antonio Casas says:
October 15, 2018 at 11:41 pm

I thought I had this problem solved but is doing it again. It goes from step 3 directly to step 6. I don't get the option to select the columns I want updated in the main table or pick the columns I want added to the main table. I'm doing all the work within a spreadsheet that has 6 tabs. I'm using Excel 2010.

Please Help,

Thanks

Reply

Hello, Antonio,
Thank you for your interest in Merge Two Tables, I hope we will solve the problem and you will enjoy it!

As the tool skips steps 4 and 5, the reason why it behaves in such a strange way could be that on step 3 you select all the columns of your table as the matching ones. The thing is that the same columns cannot be matching, updated, or added, so if you select all the columns as matching, nothing is left to be updated and added, and the tool brings you right to the final step. If this is true, you probably have seen a message that you have selected all columns as matching and if you want to update some columns, please select fewer matching columns.
Please try out the add-in once again choosing only some matching columns on step 3 (not all the columns of your table) and let me know if this helped. If the problem still exists, please contact me again!

Thank you.

Reply
Derrick Strom says:
October 18, 2018 at 3:11 pm

i don't seem to be able to get insert additional matching rows to work. I've tried both ways and both times they update the main table row data - I was expecting that it would add a row of new data with a status of matching or add a row of new data right after the matching row with a status of matching so I could compare the data in the fields I'm updating. I have 75k records in the main table and 27k in the lookup with 101 matches - I want to compare the names and company values for the 101 while adding the remaining 26,899 records to the main table with the names and company data in the correct fields for importing my records (the adding the new records (as new row) is working correctly and as expected.

Reply

Hi, Derrick,
Thank you for your comment! And thank you for using Merge Tables Wizard, I am sorry you are experiencing problems with the tool. In order we could provide you with the most accurate feedback, please contact our support team at support@ablebits.com. Please attach your Excel workbook and describe all the options you tick in the wizard on each step, plus the result you expect to get and the result you actually get.
Thank you.

Reply

Hi Abhijeet,
I am trying to Merge two worksheet using this add-on but it is showing an error message as follows.

'' The add-in can't add new records because it encounters Excel limit for the total number of rows in a sheet'
Thank you for using the Merge Two Tables tool! Please note that our products don't impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. Here you can find the limits for rows and columns.
If the number of rows in your sheet does not exceed the limit, please contact our support team at support@ablebits.com. Please attach your book and describe the Merge Two Tables steps you take in detail, mention all the options you tick in the wizard.I need some row according to implements column I put row selected numerical.

Reply

Hi, Manish,
Thank you for using Merge Two Tables and for your comment. I am sorry you are experiencing difficulties with the tool.
As mentioned in the fragment you quote, Merge Two Tables does not impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have. The possible solution is to turn off the backup option or not to select to add additional rows or columns. If this won’t help, please send your book to support@ablebits.com. Please describe the problem in detail, list all the steps you take in the wizard, and mention all the options you check. We'll do our best to help you.
Thank you.

Reply

Does your tool perform a vlookup on multiple rows with the same matching value? I tried the Merge Table feature, and it only reported the first instance/row. However, I have other rows that have the same matching value that I'm trying to add all together in the final result. Thank you.

Reply

Ask a question

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.