Jul
20

What is the best alternative to Excel Lookup, or how to merge Excel tables?

In our today’s review I offer you to continue our journey among Excel reports and tables and to look for the tools that could make it more comfortable. Today I will dwell on the problem that was probably considered too inessential by Microsoft Excel developers or was left unnoticed. The problem is: merging two Excel tables with a common column(s).

Of course, if you have good knowledge of VBA and Excel formulas you can make use of the built-in Excel functions, say, Lookup, Vlookup etc. But what if one doesn’t?

Well, let me bring to your attention some third-party add-ins that can help you cope with this. For this review I selected only those add-ins for merging Excel data that have trial versions:

Name of the add-in Version Vendor Price, USD
Merge Tables Wizard for Microsoft Excel 1.3 Ablebits 29.95
Excel Join (Merge, Match) Two Tables 7.0 Sobolsoft 49.95
Spinnaker Merge 2.1 Spinnaker Software 25
DigDB Excel add-in 7.1 DigDB 39 (half-year license)

Merge Tables Wizard for Microsoft Excel from AbleBits

On the Merge Tables Wizard for Excel homepage you will find a detailed description of the add-in and its functions. The process of merging tables is illustrated step-by-step with lots of screenshots and examples both on the AbleBits web-site and in the Help file.

After the installation was completed the Merge Tables Wizard’s icon took its place on the Excel toolbar.

The add-in works as a step-by-step wizard, straightforward and intuitive:

On the first step, select the First Table which is your main, or master table to which the results will be placed:

On step 2, select the Second Table, which is a lookup table from which the add-in will take the data to merge.

On step 3, you choose matching columns. It should be mentioned that the Merge Tables Wizard is the only add-in in our review that has an option of including /excluding table headers in the merge process.

On step 4, you select the action – either to add merged data to the end of the Master table, or to update the corresponding columns in the Main Table. Also, you can color updated values or to add a new column to the Master table that shows what rows were updated. It’s strange, but the add-in from AbleBits is, in fact, the only tool that offers such an option. In my view, when you are merging two Excel sheets, it’s quite a natural desire to be able to visually see the changes and to control the whole process.

The resulting table:

Excel Join (Merge, Match) Two Tables Software from Sobolsoft

On the author’s web-site (http://www.sobolsoft.com/exceljoin/) you can find the description of the add-in, a screenshot, a link to download the trial and the e-mail to contact the author. In spite of the fact that all add-ins reviewed in this post are intended for merging Excel tables, from the information on their web-site it appears impossible to understand how you can do this using the Excel Join add-in. The screenshot shows only the add-in’s menu and one window of the Join Tables wizard.

Quite unexpectedly I found out that the version I have installed (it was the latest version at the moment of writing) looks quite differently from what I see on their web-site.

The user interface is also clear and intuitive, though, and there may hardly be any confusion about that.

So then, select the first table (workbook, sheet, range):

Then select the second table in the same way:

Check if you selected the tables to merge correctly:

Choose one common column with identical data from each table that you want to join:

On the next step, you set the selected output table settings:

Click “Finish”:

And where is the result? We were promised a free trial version. Or, did the author just decide to remind us of the necessity to buy the license?

In fact, the resulting table is enough to evaluate the work of the add-in, but it seems impossible to use the result anyhow. In my view, it is fairer to call things with their own names, and inform the user about the limitations of the trial version.

Finally in the output table we get: all columns from both tables, so common column appear twice (columns A and F on the screenshot above) and no options for customizing the output table, e.g. the order of columns, color updated values etc. The results will be placed into a new sheet in any case.

Spinnaker Merge from Spinnaker Software

On the home page of this add-in: http://www.spinnakeradd-ins.com/spinnaker_merges.htm you can find a lot of information about the Spinnaker Merge and what you can use it for. They give detailed descriptions of its functions, buttons and icons and a screenshot of its main window.

All buttons in the screenshot are clickable and will take you to the description of the corresponding function. After starting Excel the add-in’s icon was placed into the Excel toolbar:

In my opinion it is not the best way for the add-in to announce its presence. Although the colors of the icon are quite bright, I didn’t notice it straight away. Having some experience with Excel add-ins I didn’t notice either a new toolbar, or a new menu item.

Remembering that my installation went smoothly I decided to look at my All Programs and found the add-in there. Upon running the Spinnaker Merge I immediately saw its toolbar in my Excel opened beforehand.

Now, what you see in the toolbar (from left to right): the Start button, Help and the Exit button (which was rather unexpected I must admit).

It was not the last strange thing, though: when you click on the Exit button, no matter whether you click Yes or No, the add-in’s toolbar disappears but the small icon in the Excel toolbar remains.

I was starting to guess what they need the Exit button for when I tried to put the add-in’s toolbar to where I wanted to see it. It remained in its new place right until I closed Excel. After that, when I run the add-in by clicking on its icon, the toolbar appeared in its old place again (as shown in the screenshot above)

At that, if you click on “Yes” in the Exit dialog box, on the next run on the add-in your screen flinches just before the add-in toolbar appears.

If you click on “No”, then in the top-left corner of the Excel toolbar area, the Spinnaker Merge toolbar will appear, and all other toolbars will go a little down.

If you try to run the Spinnaker Merge with Excel closed, it will have the following result:

Excel will open later, though, but you will find only a small icon with a sail and when you click on it you will be able to access the Spinnaker Merge toolbar.

The award for your persistence will be the possibility to estimate the functionality of the Spinnaker Merge:

However, it turned out not so easy as it seemed. The “Match Field” box proved to be inactive. May be Help will assist us?

Unfortunately I don’t have a lot of free time, so I had to reject this proposal. If this add-in was sold at a more reasonable price and with the source code, I would recommend it as not the worst version of “make-it-yourself” product.

DigDB Excel add-in

Here once again we meet the veteran of our reviews the DigDB Excel add-in. What can the DigDB add-in offer for merging Excel tables?

The principle of joining tables is excellently described on the corresponding page of its web-site and can serve as a pattern to follow:
http://www.digdb.com/excel_add_ins/join_merge_tables_lists/

The merging process is performed via the wizard. The needed function Join (Merge) is contained in the Table menu item.

On the first step of the wizard, you select the First table (your current table is selected automatically):

On the second step, you select the Second table to match against:

Then, you choose columns to match from the First and Second tables:

At the next step you select the rows to be included in the Merged table:

And here is the result:

All is clear and straightforward, as it should be. And as it was in our previous review when we used this add-in to consolidate Excel data.

Summing up, I can say that our leaders are again DigDB with their DigDB Excel add-in, and Ablebits with the Merge Tables Wizard for Microsoft Excel. On the Ablebits add-in side there is their customary simplicity, good usability, user-friendly intuitive interface, an excellent help system and the possibility to choose the way of presenting the results.

The DigDB Excel add-in can respond to this with a wide-range functionality and a very affordable price. But their add-in loses in usability, it is the case when you will have to read the manual. In general, both add-ins can become a good alternative to Excel VLookup and Lookup.

Also, we should consider the frequency of updates and new versions, the quality of technical support and available discounts when buying other add-ins of the same vendor. For example, AbleBits offer all their Excel add-ins in a collection with a considerable discount.

DigDB has a lower total price if to take into account the whole wealth of the functionality they managed to stuff into one add-in and free upgrades. But, $39 is the price for a half-year license, while AbleBits offer their Merge Tables Wizard for $29.95 lifetime.

Excel Join (Merge, Match) Two Tables Software has left behind exceptionally because of a poor quality of their help system. If you need a comprehensible help and you want to start using the add-in right away, you’d better choose some other add-in.

As to the Spinnaker Merge, well, I don’t know what positive can be said about this add-in, its only achievement is that it finally managed to install.

Merge Tables Wizard for Excel Excel Join (Merge, Match) Two Tables Software Spinnaker Merge DigDB Excel add-in
Installation 4 of 5 4 of 5 4 of 5 4 of 5
Usability 5 of 5 4 of 5 2 of 5 4 of 5
Functionality 5 of 5 4 of 5 2 of 5 5 of 5
Help 5 of 5 3 of 5 2 of 5 5 of 5
Total 4.75 3.75 2.5 4.5

Related reviews:

How to consolidate data in Excel spreadsheets
How to compare and find differences in Excel worksheets?

Comments are closed.