Excel Toolsets: Merging Tables
As is the case with many in the world in online marketing (and, indeed, most IT-based jobs) I spend a lot of time with Excel. One of the reasons for that is that it's excellent software - especially when it comes to handling large sets of data.
However, even with the many, many features of Excel, there are some common tasks that can't be completed 'out of the box'. These are really in two categories:
- Small, uncommon functions that are usually specific to something I do, or specific to a project
- Common tasks that are just not part of Excel's feature-set
In the case of the former, I use a set of custom functions. These can be stored in an Excel macro-enabled template (XLAM) and auto loaded when you run Excel. I do this by placing my template at the following location:
This is very handy and there are many resources out there for custom Excel functions you might use.
However, given that these are made of user-code, quality is pretty mixed, and results vary. Enter commercial add-ons.
One tasks I'm always needing to complete with Excel is to 'line up' or merge two sets of data. For example, I have a spreadsheet like the below:
Jim | 12
Sarah | 14
Derek | 11
I then have a separate spreadsheet as below:
Jim | ABC
Sarah | EFG
Bob | HIJ
The output I need is to have a single table with the columns combined, like so:
Jim | 12 | ABC
Sarah | 14 | EFG
Bob | | HIJ
Derek | 11 |
For very small datasets you can sort alphabetically and 'line up' that way, but it's an all-but-impossible task if you have tables with many rows. You can also use VLOOKUP, but this gets complex quickly, and you can see on Microsoft's site how many steps it takes with Power Query.
Most people describe this process as 'merging' two tables, and there are certainly user functions out there to do it. I've found a few issues with them:
- Limitations on number of rows
- Errors if cells contain certain data
For a few years I've been using DigDB for this (and some of its other functions). However, when my license expired recently (and given that it's a yearly license) I thought I'd look at alternatives. Although there are a few out there, I settled on trialling AbleBits Ultimate Suite for Excel which explicitly offered a table merging function (amongst many others) and looked to have a much better interface than DigDB (integrated as new ribbon items - DigDB uses a single menu of its own construction).
Thus far, I'm sticking with AbleBits. It isn't perfect, and just looking at table merging, here's a quick comparison betewen it and DigDB:
- Much better interface
- Lifetime license
- Merges tables that are in Excel table format (i.e. format >> table) - DigDB seemed to struggle with this
- Output is a single table, and you can control which colums appear at the end of the table or are 'merged)
- For some reason, there's a limit of 256 characters in the cells to be matched. I'm often matching based on a URL (which has a limit of 2,083 characters!) which means I have to start doing hashing...
- No character limit on cells to be matched
- Difficulties matching data in explicit table format
- Certain special characters cause matching to fail
- Annual license
So, thus far, I'm very happy to recommend AbleBits over DigDB - if they could fix the character limit, I'd say the table matching would be just about perfect.