Match and merge data in Excel sheets

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

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
Free 15-day trial version, 15.32 MB

How to select your main table

  1. Open Excel workbook(s) with the spreadsheets you need to join based on a key column(s).
    Then open Merge Tables Wizard for Microsoft Excel by clicking on its icon Merge Tables Wizard for Excel icon or pressing the Ctrl+Shift+M hotkey.

    When working with the add-in, on top of the add-in window you can see which step you are on.

    See the steps you take on the left
  2. The main table is the one you want to update. You can choose it by selecting, by clicking the select range icon, or by typing its address in the Select your main table field manually.

    Tip. Before you start Merge Tables Wizard, select a cell in your main worksheet and the whole table will be selected automatically.
    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.
    Note. If you have hidden rows in your main and lookup tables, they will be processed.
  3. On this step you can tick the Create a backup copy of the worksheet checkbox to automatically get an extra copy of your main table. Create a backup copy of the worksheet
  4. Click the Next button on the Merge Tables Wizard dialog box to continue.

How to select your lookup table

Select Lookup Table step

The lookup table is the spreadsheet where the matching data will be searched for (looked up). In other words, the add-in will pull information from this table.

On this step, you will see all the open workbooks and worksheets in the Select your lookup table field. Choose the Excel worksheet that contains your lookup table and you will see it highlighted.

Click on the name of the table where the matching data will be searched for

If it is not highlighted, select your lookup table using your mouse pointer, by clicking on the Select range icon next to the Data range field, or by manually typing in the address of the range into this field.

the address of the Lookup Table in this field

How to join spreadsheets based on the common field

Select matching columns
  1. On the Select matching columnsstep you will see a table with a list of columns. Please mind, the columns chosen on this step are not updated, but matched, or compared, only. Tick the checkboxes next to the main table columns that you need to match. Select the columns to be matched
  2. When you select a column in the list, you can see an arrow next to the name of the column in the Lookup table columns section. Click the arrow to select the matching column in the Second table Click the arrow to select the matching column from a drop-down list with all lookup worksheet columns.
  3. On the Select matching columns step it is also possible to indicate if there are header rows in your tables. As a rule, the add-in indicates headers automatically. If it didn't, you can check the Main table has headers or Lookup table has headers options correspondingly. This will make it easier to select columns with common index fields. You can also uncheck these options Check these options to indicate that your tables have header rows
  4. It's possible to configure Merge Tables Wizard to distinguish between uppercase and lowercase letters. To do this, tick off the checkbox Case-sensitive matching. Tick off the checkbox Case-sensitive matching
  5. You can also click the Auto Select button to tick all matching columns at once. Pressing Unselect All will deselect all columns. Click Auto Select or Unselect All to pick or deselect matching columns
Tip. You can expand the wizard window by dragging the bottom-right corner down until you get the suitable size. It's especially useful if you have multiple columns in your tables.

Select columns to update in your main table

On this step, you can select the lookup table columns that will be updated in your main table.

  1. On the add-in window you will see a list of columns in your second table. Just tick off the main table column you want to update and select the corresponding lookup table column from the drop-down list. Tick off the columns you want to update
  2. You can also click the Auto Select button to tick all matching columns at once. Pressing Unselect All will deselect all columns. Click Auto Select or Unselect All to choose or deselect matching columns

Select the columns to add to your main table

On this step, you can select the lookup table columns that will be added to your main table.

  1. On the add-in window you will see a list of columns from your lookup table that can be added to your main table. Just tick off the checkboxes next to the columns you want to insert to your main worksheet. Tick off the columns you want to add
  2. You can also click the Auto Select button to tick all matching columns at once. Pressing Unselect All will deselect all columns. Click Auto Select or Unselect All button

How to choose additional options that will be applied to the main table

Choose additional options that will be applied to the Master table
  1. When merging two worksheets in Excel on the Options step you can find extra options that can be applied to the main table after you match and combine your data in Excel.
    Note. If you have several options grayed out on the last step of the Wizard, most likely you selected the option to Add columns for all the columns on step 4. The option to select the background color for the updated and non-updated cells and some other options on the last step are available only if you are updating some column.
  2. On the last step of Merge Tables Wizard you can:
    • Choose to add certain rows and columns to the end of your main table
    • Specify how to deal with empty cells in your main table
    • Highlight the updated cells, if necessary.

Add rows and columns

Below you'll find how to work with rows and columns you'd like to add to the end of your main table.

Add non-matching rows to the end of the main table. Non-matching are additional rows that are not present in the main table. For example, both tables contain a column with IDs. Table A has the IDs from 1 to 15. Table B contains IDs from 1 to 20. So, the IDs from 16 to 20 in your lookup table are non-matching. When you check the Add non-matching rows to the end of the main table option, 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 Add non-matching rows to the end of the Master Table

Paste additional matches from the Lookup Table. When you update the same tables together with your colleagues, there can occur duplicate values on different rows that contain unique information. Not to lose data and to add such rows to your main table, select the option Paste additional rows that are not present in your main table.

Add rows with duplicate key values from the Lookup Table

Add the rows with duplicate matching values at the end of the main table. If you select this checkbox, the rows that contain duplicated matching values, will be added after all rows in the master table.

Add rows with duplicate key values

Insert rows with duplicate matching values after the row with the same value. If you press this radio button, the add-in will match and merge rows with duplicate key values in pairs. If there are extra rows with dupes in the lookup table, they will be inserted after the row with the same key column value.

Add rows with duplicate key values after the same key value

Add a status column. If you check this option, the Status column will be added to the Master Table. It will show the changes made to the rows and mark rows as: matching, matching and updated, new row.

Add the Status column

Update cells

Choosing certain option(s) from this group, you can 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 some important information in the column you are updating, you can check 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 empty and new cells in the Master Table

Update only if cells from the lookup table contain data. Select this option if you may have empty cells in your lookup table that you don't want to be copied into your master table.

Update only if cells from the Lookup Table contain data

Highlight cells

Tick off the checkbox Set background color of updated cells to change the fill color for the updated cells. Click the arrow next to the appropriate field and pick a color from the drop-down list. After joining, all the updated Excel data in the master table will be highlighted with this color and you will easily see the changes.

Set background color of updated cells
Tip. If the value in the matching column is the same in your main and lookup tables, the value in the corresponding column of your Master spreadsheet is updated, or a new value is added to a new column on the same row. These are the rows that are Updated.

Now 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 this message on the forum
 

Please remember that we work from 8am to 4pm GMT, this is 4:00am to 12:00pm EST,
1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
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