Update Data in Excel with Merge Tables Wizard

There are some tasks in Excel that take so long to do and which are so tedious that you wonder why there isn't an automated way to do them. One task that falls into this category is that of updating a table with data stored in another table. There is no Excel feature that does this so you either need to spend a lot of time and exercise a lot of patience and care to perform the task or hand it over to the experts in the form of the Merge Tables Wizard from AbleBits.

Why updates can be cumbersome

Consider the situation where you have a database of client details that contains rich data about your clients. And you have a second, more recent, set of data about some of those same clients - and perhaps some new ones too - and you want to compare and merge the second set of data with the first.

To do this manually you need to look at each entry in the second set of data and check to see if it exists in the first set of data. If you find a match, you need to update the data in the first set with the new information in the second set. If there is not a match, you need to add the new data to the first data set.

Depending on the size of the data sets this could take you hours, days or weeks to complete. The updating process isn't hard, it is just cumbersome, time consuming and easy to mess up if you're not completely focused on what you are doing. If what you are working with is customer data or important business information you simply cannot afford to make a botch of it. Luckily the Merge Tables Wizard can perform the task very quickly and accurately.

Get the Wizard

You can download the Merge Tables Wizard from www.ablebits.com/excel-lookup-tables/index.php. Once installed, you can run it from the Excel ribbon by selecting AbleBits.com > Merge Tables Wizard.

Merge the Data

Before running the Wizard, open the two files you want to merge. For safety, always make a backup of the Master table in case you make an incorrect choice when working through the Wizard. In Excel, choose AbleBits.com > Merge Tables Wizard to launch the Wizard dialog which opens showing the first of the six steps involved. Each step is described down the left side of the dialog.

Select the tables

To begin, you'll select the Master Table which is the main table that you want to have updated at the end of this process. Click the Collapse button to the right of the Select the Master Table box and drag over the cells which comprise the table that you want to update.

If the table is a list with no empty cells you can click somewhere in the table and the Wizard will select all the cells in the table.

Click the Ok button to return to the dialog.

On the first step select the data in the main list

Click Next and repeat the process this time selecting the Lookup Table which contains the data that you want to merge into your first table. Click Next.

Select the workbook and sheet with the data to update the Master table with. This is called the Lookup table

Select the Columns to match

The next step is to select the columns to match so you need to select a column that is common to both tables and which can be used to identify a unique record. In some cases, this will be as simple as choosing a field that contains information unique to each item in the list such as the customer code. In other cases, you may need to identify a unique record by a combination of columns such as the columns containing a person's first name and their last name.

Be careful with your choice of columns. You need to select sufficient columns to uniquely identify each record but no so many columns that the data won't match. So, do not include any fields which contain data which is to be updated, for example. The Wizard will automatically select columns that are the same in both tables. In many cases, however, the column names may not match in both tables so you will need to select the matching column using the dropdown list in the Lookup Table columns area.

Set the Ignore extra spaces in matching columns checkbox so that leading or trailing spaces in your data won't mess up the merge. Click Next to continue.

Select the columns in each data file that will allow the Wizard to uniquely identify each record.

Choose the Actions to perform

Now you will determine what exactly you want to happen with your data. There are a number of scenarios and these include updating data that has changed, adding columns from the second table that do not exist in the first table, and adding data from the second table which does not exist in the first table or which is different to the corresponding data in the first table.

For each field in the Lookup table check to make sure that the Master Table column that has the same contents is correctly identified in the Master Table column area of the Wizard. Then set an Action to perform on the data from the Action list. You can either add an extra column for the data by choosing Add to the end from the Action list, or you can choose Update Values In and select the column to update. You can also disable a column in the display by unchecking its checkbox so that nothing is done with that data even if it is different or brand new data.

For each column you will select to update the data, add it as a new row or do nothing to it

Configure Additional Options

Click Next to move forward to select additional options. These include setting the background color of updated cells to a color of your choice - this is useful in that it will visually highlight changed cells.

You can choose to Update only if the cells in the Master table are empty so that data which already exists will not be overwritten.

In most cases you will want to select Update only if cells from Lookup Table contain data as this will protect cells in your master table from being overwritten by empty cells in the Lookup table. With this enabled, only new information in the Lookup table will be added to the Master table.

From the For NON-Matching rows options enable Add non-matching rows to the end of the Master Table to have records that exist in the Lookup table and for which there is not a matching entry in the Master Table to be appended to the end of the Master table.

You may wish to select Add a Status column to have a new column added to the Master table indicating whether the data in that row was: Updated, Non-updated or added as a New row. As it is easy to remove columns, this is a handy option to include for reference.

There are important options to configure in the Additional Options area for controlling how the data merge is handled

When you're done, click Finish and the update process will take place. When complete you will see a dialog showing you a summary of the changes.

When the merge is complete you will see a message confirming this

If you chose to color cells containing changes or add a column detailing the changes, you can now click to view these.

If you selected to color cells containing changes and add a column indicating the changes made you can view these in the Master Table

Using the Merge Tables Wizard, a task which could take a long time and be cumbersome to perform and at risk of being inaccurately done, can be performed quickly and easily and without error.

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.