The Analytics Edge add-in lets you easily compare data from one table to another, calculating growth or change in numbers as well as time differences in dates. 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 Compare Function
As with the other multiple-table functions in Analytics Edge (Append, Combine, Match and Update), the Compare 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.
Compare 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 Compare wizard and select the Addresses worksheet from the drop-down list.
Select Key Columns
When we open the Compare wizard, we are presented with a list of column names to select from. The list only contains the columns that exist in both tables. When the tables are compared, values in non-key columns will be converted into calculated results (such as the Difference between numbers in the two tables).
You select a single column by clicking on it, and can select others by holding the Ctrl-key down while clicking, just like you would in Excel.
Options – Key Column Matching
When performing the key column 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 – Handling Matches
If there are matching rows in both tables, then the other number or date columns will be converted to the percentage, growth or difference calculations you selected. Note: if either table contains empty cells or there are no matching rows or columns, the result of the comparison will be an empty cell. You can use the Convert wizard to fill empty cells before or after the comparison.
Where Would You Use the Compare Function
The function compares two sets of similar data, calculating relative metrics. This makes it particularly useful to calculate changing measures, like product sales, link clicks, or even rank positions.
Note that the source files may contain different columns, in different orders — Compare works with columns by name. If the tables have different names for similar columns, use the Arrange wizard to rename them (you could also reorder, but that is not necessary).