How can I combine information from two reports?

When creating and exporting reports in SilkStart, you may find that you require a combination of custom information spread amongst different reports. To easily combine multiple reports, we've outlined how to use Excel's V-Lookup function. 

The V-Lookup function lets you search for specific information in your spreadsheet(s). It uses a unique identifier (such as email address or member number) to pull data from one report into another using that value as a link between the two reports.  

For example, let's say you need a report that shows all of your member's transactions within the last month, but you also need to know each member's address so you can contact them via mail regarding their transaction(s). You will notice that although you can filter the All Transactions report by purchases made within the last month, it lacks the address information that you need. 

Goal: Create an All Transactions report that shows the Home Mailing Address for the purchaser of each transaction.

As mentioned, the V-Lookup function uses a unique identifier to pull data from one report into another. For this, we will be using the member email address field. To begin with our example, you will need to create and download two separate reports in SilkStart:

1. All Transactions Report - Filtered by your preferred date and type range. Ensure this report includes member Email Address (the unique identifier) and any other information you need. The image below shows the report opened in Excel. 

2. Member Mailing Address Report - Place Email Address (the unique identifier) in the first column. Place Home Mailing Address (the specific information you need) in the second column. The image below shows the report opened in Excel. 

Once you have opened both reports on your computer, you will need to create and insert the V-Lookup formula into the All Transactions report, so it can pull in the Mailing Address information. 

---

 The V-Lookup formula has certain arguments. The arguments will tell V-Lookup what to search for and where to search. Here is the basic formula: 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1. Lookup_value: This represents the member's email address - ie. the item we are looking to match between the two documents. Make this the first cell of the Email Address column in the All Transactions Report (ie. A2 in the image above). 
   
   
2. Table_array:  This represents the cell range that contains the data we need to insert into the All Transactions Report (ie. the Home Mailing Address). This is the step where you will need to navigate to the report with the member addresses. You will then select the entirety of columns A and B, so we are relating the email addresses in both documents to the Home Mailing Address field. 
   
   
3. Col_index_num: The first column (A) you selected for the range (above) is denoted 1, and the second (B) is 2, and so on. Since we selected Email Address and Home Mailing Address for the range, we are ultimately seeking the Home Mailing Address value, which is in column 2 of this range. 
   
   
4. [range_lookup]: This should be FALSE since we are looking for exact matches. 

Example of the completed formula: 

=VLOOKUP(A2,'mailing address report.csv'!$A:$B,2,FALSE)

Below is a picture of the All Transactions report with the newly added Home Mailing Address information. The V-Lookup formula has been inserted into cell F2 and copied down to F11, pulling information from the other report. 

Summary:

1. Download the two reports from SilkStart using the necessary filters and fields. Make sure each report includes the Email Address field to be used as a unique identifier. 
2. Enter the V-Lookup formula into the last column of the Transactions report (both reports need to be open on your computer in order to do this).
3. Finally, sort your completed report as desired.  

---

How can I apply this to other reports/information?

Depending on the information you need to combine, you can follow the steps above using different reports and different values. However, you will always use Email Address as the primary identifier between your two reports. 

To easily apply these instructions to other reports, think of starting with one report ("Report A" - ie. our All Transactions report) containing some desired data (in blue below), and your goal is to complete that report with more desired information from a different report ("Report B" - ie. our Member Mailing Addresses report). 

Screenshot of Report A

The information we need in column F is currently living in Report B. We will use the V-Lookup function to pull this information from Report B into Report A. 

Screenshot of Report B

The information we need in Report A is currently living in column B of this report. 

Creating the V-Lookup formula

As a reminder: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

We will be placing the V-Lookup formula in cell F2 of Report A to complete the blank cells. 

1. Lookup_value: This always represents the member's email address - ie. the item we are looking to match between the two reports. Make this the first cell of the Email Address column in Report A (ie. A2). 
   
   
2. Table_array:  This represents the cell range containing the data we need to insert into Report A. This is the step where you need to navigate to Report B and select the entirety of columns A and B. This tells Excel that we are using the Email Addresses in both reports to match the information.
   
   
3. Col_index_num: The first column (A) you selected for the range (above) is denoted 1, and the second (B) is 2, and so on. Since we selected "Email Address" and "Desired Data to bring into Report A" for the range, we are ultimately seeking the "Desired Data to bring into Report A" value, which is in column 2 of this range. Simply type the number 2. 
   
   
4. [range_lookup]: This should be FALSE since we are looking for exact matches.

Example of the completed formula:

=VLOOKUP(A2,'Report_B'!$A:$B,2,FALSE)

Below is a picture of the completed Report A with the desired data pulled in from Report B using the formula above. The V-Lookup formula has been inserted into cell F2 and copied down to F8, pulling information from the other report.