Merging rows from Excel worksheets with Vlookup add-in

Merge Tables Wizard for Microsoft^® Excel^®

Lookup and merge Excel 2000 - 2010 worksheets

For Microsoft Excel 2010 32-bit and 64-bit, Excel 2007, Excel 2003.

Merge Tables Wizard is a time-saving add-in for Microsoft Excel specially designed to quickly find and merge matching rows in Excel worksheets. It can become a good alternative to complicated Excel functions, such as Vlookup, Lookup and Match.

• How to select your Master table (Main Table)
• How to select the Lookup table (the second table)
• How to select matching columns
• How to select the action for other columns
• How to choose additional options that will be applied to the Master table
• Back to Merge Tables Wizard Overview

How to select your Master table (Main Table)

1. Open Excel workbook(s) with the tables you need to join. Then open Merge Tables Wizard by clicking on its 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 Table, 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 table 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 table)

1. The Lookup table (or the second table) is the table 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 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 with the table into this field.
   
2. Then click the Next button on the Merge Tables Wizard dialog box to continue.
3. If you need to get back to step 1, press the Back button on the dialog box.

How to select matching columns

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 tables, take advantage of 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 table 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 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 for Microsoft Excel before running the add-ins; it will remove all leading and trailing spaces in your table instead of just ignoring them. You can use this direct link to download and install this free add-in: http://www.ablebits.com/files/get.php?addin=xl-trim-spaces.

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.
   
7. Click on the Next button on the Merge Tables Wizard dialog box to proceed.
8. If you need to get back to step 2, press the Back button on the dialog box.

How to select the action for other columns

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 tables 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 and all the checkboxes will be unselected.
   
   
3. Check the columns with the data you need to add or update 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 table 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.
6. Then click the Next button on the Merge Tables Wizard dialog box to continue.
7. If you need to get back to step 3, press the Back button on the dialog box.

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

1. On the Choose additional options step you can find extra options that can be applied to the Master table.

   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 the merge all the updated values 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 table 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".
     
5. Click the Finish button and enjoy the results!

Download now and see how it can help you