The Analytics Edge add-in lets you easily update one table with values from matching rows of another table. 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 Update function
As with the other multiple-table functions in Analytics Edge (Append, Combine, Compare and Match), the Update 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.
Update 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 Update wizard and select the Addresses worksheet from the drop-down list.
Select Key Columns
We are then 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 are updated from rows with matching values in the key columns.
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 lookup to the reference table, you can tell Analytics Edge to ignore upper/lower case differences, or to ignore spaces.
Options – Empty Cells
When performing the update, you can choose to update only empty cells, preventing existing data from being overwritten i.e. fill in only missing information.
You can also choose to ignore update fields if they are empty. This prevents existing information from being wiped out if new values are not provided.
Options – Add Rows or Columns
You also have the options to add new rows or columns from the reference tables or worksheet.
Where Would You Use the Update Function
The function updates one set of data from another, making it useful to maintaining master email or contact lists. Data collected from sales conferences or collateral downloads can be used to update existing records, filling in any gaps and allowing you to collect a little more about each contact with each interaction.
Note that the source tables may contain different columns, in different orders — Update works with columns by name. If the sources use different names for similar columns, use the Arrange wizard to rename them.