Match and merge data in Excel sheets

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

Match and merge two Excel worksheets

Merge Tables Wizard is a time-saving 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.

How to:

Free Download
Free 15-day trial version, 4/8/2014

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 the left 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 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. Click the Next button on the Merge Tables Wizard dialog box to continue.

How to select the Lookup table (the second spreadsheet)

  1. 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. Select 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 with the table into this field.
    the address of the Lookup Table in this field

How to join spreadsheets based on a 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.

    Tip

    Columns with the same names are selected automatically.

  2. If you have a lot of common columns in your spreadsheets, take advantage of the Unselect All button The Unselect All button and all the checkboxes in the table with the list of columns will be unchecked. Then you can select those you need to match.

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

  4. The 1st row content column between Master Table columns and Lookup Table columns shows the 1st item of each Master Table column. It can be useful if your table doesn't have headers so you can see what kind of information is in the columns.
  5. On the Select matching columns step you can also choose to ignore extra spaces in matching columns as well as indicate whether Master and/or Lookup tables have headers.
    You should tick the Ignore extra spaces in matching columns checkbox if your Microsoft Excel data may have some extra leading or trailing spaces. This option will prevent the add-in from missing matching values.

    Tip

    We always recommend using our free tool Trim Spaces before running the add-ins; it will remove all leading and trailing spaces in your table instead of just ignoring them. You can download Trim Spaces for Microsoft Excel for free now.

  6. 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 if you don't want header rows to be indicated.

Choose additional options to lookup and merge data

  1. On the Choose action step you can select Lookup Table columns that will be added to or updated in the Master Table.
    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 selected with the option to Update values in, and all new columns that are not present in your main table are selected with the option to Add to the end.

    If you have really big spreadsheets with numerous columns, it is possible to filter them by clicking on the arrow next to the Show columns field. You can see all columns, columns that will be added to the end, or columns with the values that will be updated only.
  2. If the default selection is not what you would like to do, press the Unselect All button The Unselect All button and all the checkboxes will be unselected.

  3. Check the columns with the data you need to combine and you will see a drop-down list with the actions to choose from in the Action column. You can add the column to the end of your master spreadsheet by selecting the Add to the end action. Or you can select the option to update values in and choose the Master table column you need to update from the list of Master table columns.
  4. If you want to change your choice, you can click an arrow in the Action or Master Table columns section and select a different action or column from the list.

    Note

    Those columns that were selected as matching on step 3 are grayed out.

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

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

  1. When merging two worksheets in Excel 2010 on the Choose additional 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 to the end 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. For matching rows you can:
    • Set background color of Updated cells. If you want to set the background 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.

    • Update only empty cells in 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 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.
  3. For NON-Matching rows:
    • Clear background colors of Non-updated cells. If you check this option, the background color of non-updated cells will be reset to the default one.

      Tip

      Non-updated are the rows that remain unchanged in 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.
  4. Other options:
    • Add a Status column. If you check this option, MTW 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".

Click the Finish button to combine data in your Excel worksheets!

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.