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:

  • Close or save all unsaved workbooks. Unsaved workbooks cannot be processed and stop the add-in from running.
  • 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

On the Ablebits Data tab, in the Merge group, click Merge Tables > Merge Several Tables with Single Lookup Table:
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, decide which worksheet to use as the lookup table, 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.
Note. For a saved scenario to work for the current worksheet, the structure of the current worksheet must be the same as that of the lookup table from the scenario.
Note. If a scenario that was created for the main and lookup tables from one and the same workbook is run for the current worksheet, the required tables will be looked for in the current workbook.

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.