The Analytics Edge add-in lets you easily combine data from two tables, aligning the common rows in key columns, and aggregating the cell values in other columns. The simple wizard interface gets the job done in seconds without programming or formulas. Use it with other Analytics Edge functions to simplify and automate your Excel reports.
Using the Combine function
As with the other multiple-table functions in Analytics Edge (Append, Compare, Match and Update), the Combine function uses a reference table or worksheet. That reference can be an interim analysis step given a name using the Table – Table Name wizard, or it can be a worksheet that has data with a header row starting in cell A1.
Combine in Action
The rest of the options are best explained by example. Assume we start with the following two worksheets, one named ‘Addresses’:
…and a second containing names:
We use the ReadWorksheet wizard to load the names worksheet, then open the Combine wizard and select the Addresses worksheet from the drop-down list.
Select Key Columns
When we open the Combine wizard, we are presented with a list of column names to select from. The list only contains the columns that exist in both tables (‘Email’ in this example). When the tables are combined, there will be a row for each unique combination of values in the key columns i.e. a row for each email address.
You select a single column in the list by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.
Options – Comparison
When performing the comparison, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces. This is useful if the data sometimes has leading or trailing spaces.
Options – Extra Rows and Columns
By default, Combine only uses the rows and columns that it starts with, but you can choose to add rows and/or columns that only exist in the referenced table or worksheet. For example, you can report click totals for the names and email addresses in the names table only, or choose to add other email addresses and/or street addresses to the report that only exist in the Addresses worksheet.
No option |
Add rows |
Add columns |
Add both |
Options – Handling Duplicates
If there are columns in both tables that are not selected as key columns, any duplicate rows will be combined together using the options selected. You can choose to combine text, number and date columns differently using the drop-down selections.
In the example, we don’t have any text columns to combine, but if we did and the first table contained ‘A’ and the referenced one contained ‘B’ for the same row, the result for the All (list) option would be “A,B”.
There are other options for combining the columns, and each data type allows you to keep the first non-blank or non-zero values.
Where Would You Use the Combine Function
The function brings together two sets of data, like the Append function, but it also merges duplicate values at the same time. This makes it particularly useful when merging information that overlaps, such as finding a user’s first engagement date from multiple mailing list campaigns, or calculating the total sales by product from multiple purchasing files.
Note that the source files may contain different columns, in different orders — Combine works with columns by name. If the files use different names for similar columns, use the Arrange wizard to rename them (you could also reorder, but that is not necessary).