Why updates can be cumbersome

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 an Excel 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.

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 Ablebits.com . Once installed, you can run it from the Excel ribbon by selecting AbleBits.com - Merge Tables Wizard.

Merge the Data

In Excel, open the two files you want to merge. Choose AbleBits.com - Merge Tables Wizard to launch the Wizard dialog which opens showing the first of the six steps involved.

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.

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 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.

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.

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.

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.

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.

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

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.