Merge a table in Excel with several lookups
The Merge Single Table with Several Lookup Tables tool updates the main table fetching the necessary records from numerous lookup tables with a few mouse clicks.
It can also keep merge scenarios and process your data automatically when needed.
Before you start
Before running the add-in, here are the key points to take note of:
- Unsaved workbooks cannot be processed. Before running the tool, either close or save them.
- The tool does not process hidden rows.
- The whole main table is updated. It is not possible to update only a part of it.
- Unlike Merge Two Tables, Merge Single Table with Several Lookup Tables does not allow adding columns with the same name in Step 5.
- If there is a check on the left of the Back up this worksheet option in Step 1, the add-in will create a backup copy of the table that is going to be updated. Clear this checkbox if you do not want to keep the original data your main table contains.
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 Single Table with Several Lookup Tables:
Tip. Hover the cursor over a blue circled question mark to let the add-in's hints become visible.
Note. The help page for Merge Two Tables provides a detailed description of the steps the tools have in common.
How to add other lookup tables
You can choose worksheets containing the necessary lookup tables in Step 6 of the add-in:
Note. All the lookup tables you select 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.
If a row with the same key value is present in several lookup tables, the main table will be updated with the data from each of them consecutively in the order you see the lookup tables on the list in Step 6. In the end, the updated columns of the main table will contain the values brought from the table that is the last of those lookup tables.
Scenarios
Selected tables and options can form a scenario of your choice. If you save and run it, the add-in will bring the necessary data from the selected lookup tables to the specified main one considering the options enabled and you need not go through all the steps anew.
How to save scenarios
To make the add-in memorize the tables and options you selected, click the Save scenario button in Step 7:
Then enter the name of your scenario and click OK.
How to run scenarios
Once your scenario is saved, it will be shown each time you run the tool.
To get your main table updated, select a scenario, decide which worksheet to use as the main 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 main 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 lookup worksheets to be processed must be the same as that of the corresponding worksheets from the scenario.
If you do not need any of your saved scenarios, simply remove it from the list by clicking 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.
