About this feature: When it is necessary to pull information from two different Raw Data Reports, the information can be combined into to one file by following the steps laid out below.
Any of the Raw Data Reports can be combined, however the example below will use the Personal Profile and Schedule Detail reports.
Step 1: Run the Raw Data Reports
Run the Personal Profile report, ensuring that you include all volunteers, the desired profile information (e.g. the custom field for the volunteer’s T-shirt size) and the “DatabaseUserId” column.
Export the Personal Profile raw data to a file. This will be your “source” worksheet.
Run the Schedule Detail report for your desired activities, ensuring that you include the “DatabaseUserId” column.
Export the Schedule Detail raw data to a file. This will be your “main/destination” worksheet where you will construct your report.
Step 2: Combine Your Raw Data Reports Into One File
Open the Personal Profile raw data report.
Open the Schedule Detail raw data report.
In the Personal Profile report, right click on the tab at the bottom containing the name of the worksheet and select “Move or Copy…” (PC shortcut: press the [ALT] key, then E, then M)
In the “Move or Copy” dialog:
In “To book”, select the name of the Schedule Detail Export file
In “Before sheet”, click on “(move to end)”
Optional: Check the “Create a copy” box
Click the [OK] button
Open the Schedule Detail report. Your profile information should now be the second worksheet in that file.
Step 3: Prepare Your Source Data
IMPORTANT: It is important to sort the source information (i.e. the values you want to look up) by a unique value. In this case, that is the “DatabaseUserId” column.
Go to the profile information worksheet
Highlight the data
Sort the data by the DatabaseUserId column
Step 4: Look up Desired Information
In the schedule detail worksheet, you can now add a column to look up the T-shirt information for your volunteers.
Click in the blank cell in the first row where the information is to be copied
Enter the appropriate formula. The basic syntax for the LOOKUP function is: @LOOKUP(value_to_look_up,range_to_scan,range_to_return)
“Value to look up”: the single cell in your current worksheet containing the ID you want to find in your source worksheet
“Range to scan”: the range of cells in the source worksheet to check for the unique ID (this can only be one row or column)
“Range to return”: the range of cells in the source worksheet containing the data you want to return to your main worksheet (this can only be one row or column)
NOTE: Instead of entering the formula manually, you can follow the steps listed in the tip below
If you click in the cell where you created the formula, it might look something like this (where “Source” is the name of the worksheet that contains the data you are looking up):
=LOOKUP(A2,Source!A2:A101,Source!G2:G101)
Edit the formula to ensure that you are using an “absolute” cell reference instead of a “relative” one by adding a dollar sign (“$”) before each column letter and row number referenced in the source worksheet). This will ensure that the exact range is checked each time, rather than dropping the range down by one row in your source worksheet for each row of data in your master worksheet:
=LOOKUP(A2,Source!$A$2:$A$101,Source!$G$2:$G$101)
Copy the formula down to the last row of data in your main worksheet
Tip: Instead of entering the formula manually, you can follow these steps:
1. Click in the blank cell (in the destination worksheet) in the first row where the information is to be copied and type @LOOKUP(
2. In the destination worksheet, select the cell value (typically in the same row) you want to use for your search. That cell address will be copied into your formula.
3. Add a comma (“,”) to the end of your formula (after the cell address you just copied)
4. Open the source (lookup) worksheet
5. Highlight all the data in the column you want to search (i.e. the data that matches what you highlighted in your destination worksheet), but don’t include the column header
6. Add a comma (“,”) to the end of your formula (after the range you just copied)
7. Highlight all the data in the column you want to return to your destination worksheet (don’t include the column header)
8. Type a right parentheses (“)”) at the end of your formula
9. Press the [Enter] key on your keyboard to copy the formula to the cell in the destination worksheet
10. In the destination worksheet, click in the cell where the formula was created