Support Center

Reconciling Data with VLOOKUP

Last Updated: May 14, 2014 03:25PM EDT
How do I reconcile my data to the original data set?
By using Excel’s VLOOKUP function, you can easily match Semantria’s output to the source identifier.
For example, in the original dataset we have the source identifier being the room number and have created a unique ID for each document (comment).

***Note: Ensure all unique IDs do not have empty Source Data fields. If unique IDs do not contain source text Semantria’s output IDs will not match the original source data IDs.

After you have created unique IDs, follow these step-by-step instructions:
  1. Select “Manage Analyses and Reports"
  2. Select "New" located in the left pane.
  3. Select "DETAILED analysis for each document"
  4. Select the unique IDs column that corresponds to the data in the “Optional ID” box
  5. Select the “Select source text” that corresponds to the unique IDs.For our sample dataset, the above steps are referred  to the window below:

By clicking "Analysis" Excel sends the dataset to Semantria's engine. By double-clicking on the project name after analysis completes, Semantria returns the output results in the format as follows:

Next we followed the analysis steps in Matching Data with Unique IDs.
In order to use the VLOOKUP function:
  1. Copy and Paste the unique ID and source identifier columns into the Semantria output page.
  2. Filter the unique IDs alphabetically.
  3. Create a new column. This will be for your VLOOKUP formulas and will show the source identifier for each source text. Enter ‘=vlookup’. You will be prompted to add to the formula.
  4. Click on the first cell of source text in Semantria’s output, which will enter it into the VLOOKUP formula. Add a comma.
  5. Select the all unique IDs and source identifiers (room number), which will insert them into the VLOOKUP formula. Add a comma.
    ***Note: Be sure to lock this part of the formula by pressing your 'F4' key or by adding a “$” before each value.
  6. Type the column number you would like the output data to match with. In our instance this is column 2, the room number. ​
  7. Press enter and the output data set will be matched with the unique ID and source identifier.
  8. Fill the VLOOKUP formula down to the bottom of the output data and all the output data will be matched with the source identifier.
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found