On Google Sheets, you can use VLOOKUP to combine multiple tables into one, as long as one of the tables has fields in common with all the others.
There are four pieces of information that you will need to build the VLOOKUP syntax:
search_key
The value to search for. For example, the user ID.
range
The range to consider for the search. The first column in the range is searched for the key specified in 'search_key'.
index
The column index of the value to be returned, where the first column in 'range' is numbered 1.
is_sorted - [optional]
Indicates whether the column to be searched (the first column of the specified range) is sorted, in which case the closest match for 'search_key' will be returned.
Use Case
You can use this approach in any situation that requires merging data from two different sources, but let's use a typical example.
You want to export the People report from the Admin Area and combine it with the Granular Analytics reports, which is stored in a different database without user identifying data, only the User ID.
NOTE: The Granular Analytics report is only available on the Uscreen Plus plan, please contact our Sales team for more information.
Pre-requisites
You requested the Granular Analytics report to the Uscreen support team (via support@uscreen.tv)
You downloaded the People report from the Admin Area
Instructions
Follow the instructions to Import CSV data to Google Sheets - and import the two files on different sheets.
People on the first sheet
Granular Analytics on the second sheet
NOTE: I have created a new column named "User Email", next to "User ID". We will fill the column with the VLOOKUP formula.
You can now follow the instructions to Combine data from several tables onto one worksheet by using VLOOKUP - listed above.
Now use the formula below:
=VLOOKUP(search_key, range, index, is_sorted)
Where:
search_key is the adjoint "User ID" cell on the sheet that contains Granular Analytics.
range is the selection of the cell range (between A and G) on the sheet that contains the People report.
The index is set to Three (3). In this example, the third column contains the User Email. If we wanted to get the User Name, we would use Two (2) instead.
is_sorted is optional, we don't need it in this example.
The result looks like this:
