Combine Reports
In some circumstances, you might want to combine the data found in two separate reports. You just need to ensure that both reports have at least a unique variable in common: e.g. email address or order number.
For example, you might want to see the list of customers' postcodes for a specific event. In this situation, you need to download the Customers Report to see the list of all customers' email addresses and postcodes and the Orders By Events Report to see the list of all customers' email addresses for a specific event.
In order to combine both reports (e.g. show the customers postcodes for all customers who have attended a specific event), you'll need to download (click on the blue 'Export as CSV' button when viewing a report) and open both reports in your favourite spreadsheet program (e.g Excel) and run the 'Lookup' function. Here's how:
Once you have both .csv files open, go to the file where you'd like the additional data to be listed. In our example, we would go to the Orders By Events Report because we'd like to add the postcodes to that list.
- Go to the Customers Report .csv file and order the report alphabetically by email address;
- Go to the Orders By Event Reports .csv file and select an empty column and type the word 'postcode' in the first cell (e.g. in cell R1);
You'll then need to enter the LOOKUP function in the second cell (e.g. R2). This formula will need to have three parameters entered, each separated by a comma. The first parameter is the unique value, common to both files, you can see on the second row (because you are entering the formula in the second cell); in our example, that would be the email address found in cell F2. The second parameter is the column where the unique value can be found on the Customer Report .csv file; in our example, that's the email column A. The third parameter is the columns where the value we want to add can be found; in our example, that's the postcode column L.
To enter all the Lookup formula parameters correctly for our example, follow the steps detailed below (it might feel a bit disconcerting to have both files open and switch from one to the other as you enter the formula):
- Place your mouse in cell R2 and type: =LOOKUP(
- Click on F2 to select the first email address and enter a comma;
- Go the Customers Reports .csv file and click on the header of column A to select the email address column and enter another comma;
- Stay on the Customer Report and click on the header of column L to select the postcode column and enter a close bracket sign ')' and press on the Enter key on your keyboard. You can now see the postcode for the customer on line 2 of the Orders By Event Report.
- Copy the formula you have created in R2 to all the other cells of column R. You can copy very quickly by dragging down the bottom right corner of the cell R2 and releasing it when you reach the last row with data.
Related Articles
Reconcile your accounts with Stripe
To reconcile your Little Box Office and bank accounts with Stripe, you need access to the reports or statements for all three. The Little Box Office Sales reports can be reconciled with the Stripe's 'All payments' report (found in the 'Payments' ...Reconcile your accounts with Braintree
To reconcile your Little Box Office and bank accounts with Braintree, you need access to the reports or statements for all three. The Little Box Office Sales reports can be reconciled with the Braintree's Transactions reports: Your Bank account ...Split custom fields text in multiple columns
If you use multiple custom fields, the answers will be displayed in a single column in the Roll Call report. In this situation, you might want to present the answers for each custom field into a separate column. To do this, run the Roll Call report, ...Scan tickets with your own scanner
If you already have a scanning solution in place, we can display on your tickets any barcode or QR code type. These codes can be encoded by the variable or combination of variables of your choice, such as order number, ticket number, seat number, ...Understanding vouchers
The voucher system is essentially a way to make one or more tickets visible and available for purchase when using a valid voucher code. The vouchers control access to hidden tickets; the voucher codes unlock the vouchers to provide access to these ...