Merging Two Worksheets with AbleBits Excel Add-In

​By Barry Nagle, Managing Partner/Senior Researcher, Evaluation and Action Research Associates.
 
Data management is a critical research function. I frequently search for tools that limit the time it takes to manipulate data so I can focus more on analysis and results interpretation. Several Excel add-ins have proven to be helpful tools that increase work efficiency. In my work, the AbleBits Merge Tables Wizard has been one of the more helpful add-ins. 
 
Let’s say an Excel file has two worksheets named TestOne and TestTwo. Each worksheet lists test scores.
 
The data look like this on each worksheet:  
 
   
We would like to merge the data from two different worksheets onto one by using ID as the matching variable. The AbleBits add-in automates this process and reduces human error. Once installed, the AbleBits Merge Tables add-in creates a tab in Excel’s ribbon.

Click on the Merge Two Tables function icon to open the wizard box.

Check the box to create a backup copy of the worksheet. Click Next.
 
The data on the worksheet you start with is called the Master Table. For our example, the TestOne worksheet is the Master Table. Select the worksheet you would like to merge. This is called the Lookup Table, which can be in the same Excel file or a different one. For this example, both of the worksheets are in the same file. Select TestTwo as the Lookup Table.

Click Next, then select the matching variable. In this case, the data on each worksheet has column headers. If, however, your columns do not have column headers, you would uncheck the headers boxes for the Master and Lookup Tables. In this example, both worksheets have a matching variable with the same column header: ID. Check the box next to ID and ensure that ID is displayed in the Lookup Table column. Click Next. Note: More complex data can be matched with multiple variables.

Select the variables to add to the Master Table from the Lookup Table. Check the box for TestTwo and click Next.

Note: If the Lookup (TestTwo) Table had multiple variables, you would select all of the variables you want to add to the Master Table. If you had two identical variables in the Master (TestOne) and Lookup Tables (TestTwo) and wanted to update the Master Table, you would opt to update values in the Action column and then select the variable you want to update. 
 
There are additional options in the final step. You can decide if you want to add non-matching rows to the bottom of the Master Table. In this example, all of the rows match, so there is no need to select this option. There is also an option to add a status column, which allows you to see if the data are updated, non-updated, or if it is a new row. For this example, check the box to add a status column.

Click on Finish to process the data; a message box appears when complete.  
 
In this example, five matching rows were found and one column was added in the merger:
 
 
If a new row is created, New Row will display in the MTW Status column. If a row in the Master Table does not have a match in the Lookup Table, Non-Updated would display in the MTW Status column.
 
Note of caution: All matching variables must be unique. Let’s say that the Master Table had one ID called A, and the Lookup Table had two IDs called A. When you ran the match, the program would only match the A in the Master Table with the first A in the Lookup Table; the second A in the Lookup Table would be ignored. 
 
This example used a small amount of data. However, I have used this product to merge sheets that have large numbers of rows and columns.
 
 

 Comments

 
To add a comment, Sign In
Total Comments: 17
 
Lu posted on 11/13/2013 1:11 PM
This article contains useful information and functions a bit better than a simple VLOOKUP function. I have found that with Office 10,some add ins don't always cooperate very well. I am going to try this one myself. It will be a good Tech Tip.
Gary posted on 11/13/2013 1:12 PM
This looks like a more sophisticated tool for merging data sets and gives sql-like options that VLookup does not. Also, this eliminates copying/pasting which is a common source of error. Nice tip!
Dale posted on 11/13/2013 1:15 PM
Ablebits has a nice selection of Excel add-ins, this being one of them. It's nice to have another tool to assist us in the quest for efficient data manipulation. Good tip!
Christina posted on 11/13/2013 1:23 PM
Thanks for this tool tip. I am looking forward to trying out this add-in as I am regularly merging files in excel with VLOOKUP.
Lee posted on 11/13/2013 1:30 PM
Thanks for the tip! I too have used VLOOKUP to merge worksheets - it works but is rather clumsy. This add-in looks to be more streamlined and more flexible.
Steve posted on 11/13/2013 2:15 PM
I'm not familiar with AbleBits. Do you have any information about where to download this or other add-ins?
Wendy posted on 11/13/2013 2:57 PM
This is not a free add-in but it can be purchased with several other useful tools for a fairly reasonable price at www.ablebits.com. There is a 20-day trial period available. Although the IDs may have to be unique - they didn't have to be in ascending order to be matched correctly.
Claire posted on 11/13/2013 4:08 PM
Nice tip, this looks useful for merging files without having to import into Access or SPSS. I especially like the status column option.
Terry posted on 11/13/2013 4:17 PM
VLOOKUP is my goto formula, once you learn how to use it, you use it for a myriad of things. This looks like it could be one of those invaluable tools, one you open at the same time you open Excel.
Julia posted on 11/13/2013 5:22 PM
I had never heard of this Excel add-in before. I like the fact that it can be used for multiple variables. Thanks for sharing!
Marlene posted on 11/13/2013 5:40 PM
This is very interesting! I use SPSS for this type of merging all of the time. I was not aware that you could also have the same functionality within Excel.
Nina posted on 11/13/2013 8:10 PM
What a neat tip!! I used to migrate my files to Access merge the files and put them back into Excel. Had never heard of the software Ablebits. Will definitely look into it. Thank you!!
David posted on 11/14/2013 3:59 PM
I too use VLOOKUP (and its close cousin, HLOOKUP) extensively, and generally like them a lot. As long as keys between one data table and the other are exact matches, the merge works quickly and easily.

It limitation though is memory. It's hog! And if one has one huge table to merge elaborately into another huge one, the memory requirements skyrocket, as does the size of the saved file containing the LOOKUP expressions.

It is also quite useful to learn the Excel OFFSET funtion, which is more of a distant cousin, but (1) uses very little memory, and (2) can be used well for table lookup under certain conditions. Worth getting to know!
Oleg posted on 11/14/2013 5:28 PM
I noticed some folks still use VLOOKUP. I have a much better solution! Try the MATCH INDEX function--it's a lot less clumsy and solves many of the issues VLOOKUP has. Here's a quick tutorial: http://www.mrexcel.com/articles/excel-vlookup-index-match.php
Annie posted on 11/18/2013 3:53 PM
I do not have AbleBits downloaded, so I downloaded and tried this and it worked good. But at this time I am not able to get the licensing so I will keep using V/H Lookup and try the match index that Oleg added.
Laura posted on 11/23/2013 5:59 PM
I was just discussing this very function with my IR office! Everyone present was doubtful that it could be done. This article provides precise steps for merging worksheets in Excel. I can't wait to share this information with our IR office. This is incredibly useful for those times when importing into a database is too time consuming for a specific analysis.
JR posted on 2/12/2014 6:43 PM
I will have to really look into this, I rely on vlookups throughout any given week, the aspect of having a status indicator added and working with multiple variables is very interesting!