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:
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:
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:
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:
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:
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:
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.
Note. If a scenario is run for the current worksheet in another workbook, the names and structure of the main worksheets to be processed must be the same as that of the corresponding worksheets from the scenario.
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.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
