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