Match and merge data in Excel sheets

How to merge two Excel spreadsheets, lookup and update data in Excel 2013-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, 12.95 MB

How to select your Master table (main spreadsheet)

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.

2. The Master Table (Main worksheet, or First Table) is the one you want to update. You can select it by clicking the select range icon. Or type its address in the Select the Master Table (First 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 Master Table only, apply Auto filter to it and then only visible rows of the Master Table will be processed and updated.

3. On this step you can tick the Create a backup copy of the worksheet checkbox to automatically get an extra copy of your Master Table.

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

How to select the Lookup Table (the second spreadsheet)

The Lookup Table (or the second 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 the Lookup Table (Second 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 the Lookup Table 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

1. On the Select matching columns step 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 Master Table columns that you need to match.

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 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 Master 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

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.

Choose additional actions to lookup and merge data

1. On the Choose action step, you can select the Lookup Table columns that will be added to or updated in the Master Table.

2. On this step, you will see a list of columns in the second table (Lookup Table). By default, the columns with the same names are displayed underUpdate values , and all new columns that are not present in your main table are shown under Add columns.

3. If the default selection is not what you would like to do, benefit from the Auto Select and Unselect All buttons.

The Unselect All button

4. When you select a column in the Update values list, you can see an arrow next to the name of the column in the from Lookup Table columns section. Just click on this arrow to pick the needed column name.

Tip. Those columns that were selected as matching on step 3 are not shown on the Choose action step.

5. If you don't want to update or add some column, just unselect it in the Update values or Add columns list.

How to choose additional options that will be applied to the Master Table

1. When merging two worksheets in Excel 2010 on the Options step you can find extra options that can be applied to the Master 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 Master 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 Master Table.

Add non-matching rows to the end of the Master Table. Non-matching are additional rows that are not present in the Master 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 Master Table option, the rows with such values will be inserted after all rows in the appropriate columns 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 matches from the Lookup Table.

Paste at the end of the Master Table. If you select this checkbox, the rows that contain duplicated matching values, will be added after all rows in the Master Table.

Paste after the same key value of the Master Table. 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 the 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: Updated, Non-updated, New row.

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 Master 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 Master 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 that you don't want to be copied into your Master Table.

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.

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.

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
 
 
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
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