If you are not able to get the data you need using one of our built-in roster reports, it is possible to link your data back together using Access or Excel. Before you follow the steps in this tutorial, make sure that a Custom Table Roster does not get the data that you need. Two examples of when you may need to link data back together outside of your Databank include Pledges and when you need to Household your information prior to linkage. If you have access to Microsoft Access, you can follow the directions on page 47 of the Databank User Guide found in the Help Menu. If you've exhausted those other options and have Excel: Be brave and continue. In this example we will look at linking pledge data.
Download Your Linked Tables
1. To get the pledge information, I searched for anyone with a pledge using Search->Pledges in the menu.
2. Go to Members ->Download in the menu.
3. Make sure that the table that you want to link to is checked in the Include Linked Tables section.
4. Click Download.
5. This generates your files, on the ensuing page, download the files you selected. In this example I need main.csv and pledge.csv.
Prepare Your Workbook for Linkage
1. Open up the files that you wish to link in Excel.
2. In the workbook that contains your main member data, create a new sheet for each additional table you will be linking.
3. Copy and paste the information from your other downloaded tables into the new sheet(s). In this case I've renamed my new sheet PLEDGES so I don't forget what is there.
Add New Columns to your Sheet
In this case I want the Total Amount of the Pledge and the Paid Amount in my main sheet.
The Magic Formula
What you'll need to do now is enter the right formula to instruct Excel what fields it should be matching and which fields should return as the value for that cell.
This formula to enter in your cell follows this pattern: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Let's pick that apart:
lookup_value: the cell in your current sheet that you want VLOOKUP to match on.
In this case, my lookup value is the Link column, so I want to match cell B2.
table_array: the array of cells that are included in your other sheet.
In this case on the pledge sheet, I have 14 rows of data and P columns.
My range is then pledges!A$1:P15 .
col_index_num: The number of the column that you want to grab the data from.
In this case on the pledge sheet, the data I want is in the 6th column.
range_lookup: set this to FALSE
One you have the formula set in the first cell, double click the bottom right corner of the cell to copy the contents into each cell in that column.
Repeat this process for any other information from the secondary sheet that you would like in your primary sheet.
For more information on VLOOKUP, please consult this Microsoft help page: http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
See that attached file for a working example of linked tables using Excel.
Please sign in to leave a comment.