The Analytics Edge add-in lets you keep or remove rows from one table if matching values exist in another. 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 Match Function
As with the other multiple-table functions in Analytics Edge (Append, Combine, Compare and Update), the Match 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.
Match 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 Match wizard and select the Addresses worksheet from the drop-down list.
Select Key Columns
When we open the Match 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 matched, only values in the key columns are compared.
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 – Matching
When performing the comparison of the key columns, 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 – Keep or Remove
You can choose whether to keep or remove rows that have matching values in the key columns, such as keeping the names for people we have addresses for, or keeping the names for people we do not have addresses for.
Keep |
Remove |
Where Would You Use The Match Function
The function uses one list to filter another, which makes it particularly useful for things like filtering a large data set for specific items, like seeing transactions for the top selling product, or detail click data for the most popular campaign. It could also be used to remove the email addresses of people that have clicked on a previous email campaign, to create a list for followup.
Note that the source files may contain different columns, in different orders — Match aligns key columns by name. If the files use different names for similar columns, use the Analytics Edge Arrange wizard to rename them (you could also reorder, but that is not necessary).