Merge multiple tables with a single lookup in Excel

Updating many worksheets at one go is now possible with the Merge Several Tables with Single Lookup Table tool. It works in much the same way as Merge Two Tables but provides an opportunity to process numerous tables at once:
This is how the tool works.
Besides, you can save your merge scenarios and run them at the click of a mouse.

Before you start

Pay attention to the points below:

  • Unsaved workbooks cannot be processed. Before running the tool, either close or save them.
  • The tool does not process hidden rows.
  • It is not possible to update a part of a table. Selected main tables are updated in whole.
  • Unlike Merge Two Tables, Merge Several Tables with Single Lookup Table does not allow adding columns with the same name in Step 5.
  • If the Back up this worksheet option is checked off in Step 1, the add-in creates a backup copy of each main table. If there is no need for backups, make sure this option is unselected.
    Note. It is not possible to undo the changes the add-in has made.

How to start

You can find the Merge Several Tables with Single Lookup Table tool in the Merge Tables drop-down menu on the Ablebits Data tab:
Run the tool.
Almost all the steps to be followed are described on our help page for Merge Two Tables. Also, consult the hints available in the tool itself. To see a hint, simply hover the cursor over a blue circled question mark in the add-in's window:
Here is the question mark you need.

How to add other main tables

To update all the necessary main tables, check off the worksheets that contain them in Step 6 of the add-in:
Decide what tables should be updated.

Note. All the main tables to be updated must be structured alike. If header rows are missing, the number and sequence of columns containing data (including empty columns that are before the tables) must coincide.

Scenarios

The tool can memorize the tables and options you select while working with it and keep them as a scenario. A saved scenario makes it possible to automatically reproduce all the steps and quickly update all specified tables with the required values from the selected lookup table taking into account the enabled options.

How to save scenarios

To get a scenario created, click the Save scenario button in Step 7 of the add-in:
Here is the Save scenario button.
You will be asked to name your scenario. When you are done, click OK.

How to run scenarios

After saving your scenario, you can access it at any time. Just start the Merge Several Tables with Single Lookup Table tool and the list of available scenarios will show. To run the scenario of interest, select it in the list and click Start:
Pick the necessary scenario.

Note. Saved scenarios will not work if the workbooks containing the worksheets to be processed have been moved to another location. You can find your saved scenarios and update the location of the files mentioned there by following this path: C:\Users\UserName\Documents\Ablebits\Merge Tables. You can also create new scenarios instead.

To delete a scenario that you do not need anymore, select it in the list and click the Delete this scenario icon on the right of the scenario name.

Here are the help pages that may be of interest to you: