Merging rows from Excel worksheets with Vlookup add-in

Lookup and merge Excel 2000 - 2007 worksheets

Merge Tables Wizard for Microsoft® Excel®

For Microsoft Excel 2010 beta, Excel 2007, Excel 2003, Excel XP (2002), Excel 2000.

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.

• 5 steps to find and copy matching data
• Merge Tables Wizard terms
• What to do if you get "No matching rows were found"

Back to Merge Tables Wizard for Excel homepage

5 steps to find and copy matching data

For example, suppose you have 2 Microsoft Excel tables (lists) - "Orders" and "Prices", they both have a column "Product ID". You want to update prices and add product descriptions from the Second table (lookup table) to your Main table (master table).

Open the Merge Tables Wizard wizard by clicking on its icon , or pressing Ctrl+Shift+M hotkey, or go to Excel Menu Tools -> Merge Tables Wizard.

1 - Select your Main table (Master Table)

Select the Main table to which matching data will be copied.

TIP: Before you run the Merge Tables Wizard, just select a cell of your main Excel table, and the whole table will be selected automatically.

TIP: If you want to update some part of the Master Table only, just apply autofilter to it and only visible rows of the Master Table will be processed and updated.

2 - Select a Second table (Lookup table)

Select the Lookup table in which matching data will be searched (looked up).

If the Lookup table is not in same Excel worksheet, first select the spreadsheet you need from the opened workbooks tree.

3- Select matching columns

In the dialog box you see the list of columns from the Main table.

Once you check a column from the Main table, you will see a pop-up menu from which you will select a matching column of the Second table.

TIP: Columns with the same names are selected automatically.

4 - Select the action for matching data

In the dialog box, you see a list of columns from the Second table (Lookup table). Check a column, and select the action for it:

• Add to the end of the Main table.
• Update values in the selected column of the Main table.

TIP: for columns with the same names the "Update values in" action will be selected automatically.

5 - Choose additional options that will be applied to the Master table

On this step you can choose additional options:

For Matching rows:

• Set background color of Updated cells. In this drop-down list you can set the background color for the updated cells.

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.
• Add non-matching rows to the end of the Master Table. Non-matching rows from the Second table will be inserted after all rows in the appropriate columns of the Master Table.

Other options:

• Add a Status column. If you check this option, a new column "MTW Status" will be added to the Master table that will reflect changes and mark rows as: "Updated", "Non-updated", "New row"

Click the "Finish" button and enjoy the results!

What do the Merge Tables Wizard terms mean?

If at the last step of the wizard, you checked the Add a Status column option, after the Merge Tables add-in finishes its work, you will see a new column, MTW Status, in your Master table. The Status column may have 3 definitions: updated, new row and non-updated.

Updated means that a given row appears in both tables. This row in the master table was updated with data sourced from the corresponding columns of the lookup table, or new columns were added to the master table.

New row means the row appears in the lookup table only. It was copied from the look-up table and added to the master table. New rows are added only if you checked the option Add non-matching rows to the end of the Master Table at the final step.

Non-updated means the row appears in the master table only. No changes were made in this row.

What to do if you get "No matching rows were found"

If instead of the result table you get the message "No matching rows were found", but you are sure your Excel tables have matching rows.

This may happen if your data has some extra spaces, so that the entries look the same, but in fact are not the same (this may be the case when you copy data from databases).

For example:
You want to merge 2 tables using the CustomerName column. "CustomerName" in your Main Table contains "John Doe      ". And it is the problem because "John Doe      " is not equal to "John Doe" in your Second Table, though they look the same.

Solution

Use our free Trim Spaces for Excel add-in to remove excess spaces.

Download the Trim Spaces for Excel (1.2 MB)

Install the add-in, then select the whole table, start the add-in (Excel Menu -> Ablebits.com -> Trim Spaces for Excel), and click the "Run" button.

Then try to run the Merge Tables Wizard for Excel again.

If this doesn't help:

If this doesn't help, please send us your sample workbooks and describe step-by-step what settings of the Merge Tables Wizard you select.

Also, please include in your message:

• Your Windows version
• Your full Excel version (see Excel menu -> Help -> About, e.g. 11.8134.8132).
• If you have other Excel add-ins installed.

We will investigate the issue