Merging rows from Excel worksheets with Vlookup add-in

Lookup and merge Excel 2000 - 2010 worksheets

Merge Tables Wizard for Microsoft® Excel®

For Microsoft Excel 2010 32-bit, 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" and they both have a column with the "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 by clicking on its icon or pressing Ctrl+Shift+M hotkey.

1 - Select your Main table (Master Table)

Select the Main table, the one you want to update.

TIP: Before you run the Merge Tables Wizard, just select a cell in 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 the Second table (Lookup table)

Select the Lookup table in which the 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'll see the list of columns from the Main table.

Once you check a column from the Main table, you will see a drop-down list from which you can select the matching column in the Second table.

TIP: Columns with the same names are selected automatically.

4 - Select the action for the matching data

In the dialog box, you'll 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.
• Update only empty cells in Master Table. You can check this option if you don't want to overwrite the existing values in your Master Table.
• Update only if cells from Lookup table contain data. You can select this option if you have empty values in your Lookup table that you don’t want to pull into your Master table.

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 the 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 you check the Add a Status column option on the last step of the wizard, you will see a new column, MTW Status, in your Master table after the Merge Tables add-in finishes its work. 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 the data 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 on the final step.

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

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

Did you get the message "No matching rows were found" instead of the result table and you are sure your Excel tables have matching rows?

This may happen if your data have 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 a 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 the following information in your message:

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

We will investigate and do our best to solve the issue

Merge Tables Wizard for Excel is compatible with

• Operating System: Microsoft Windows 7 (x86, x64), Windows Vista (x86, x64), Windows 2003, Windows XP.
• Microsoft Office Microsoft Excel 2010 32-bit, 2007, 2003, XP (2002), 2000.

Try before you buy

Download a fully-functional 15-day evaluation version of Merge Tables Wizard for Excel and try for yourself.