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.
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 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.
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.
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.
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.
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
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.
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.
5. If you don't want to update or add some column, just unselect it in the Update values or Add columns list.
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.
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.
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.
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.
Now just click the Finish button and enjoy the results.
- ABOUT US