Import of accounts, donations, and events can often be made easier and more successful by adding a Constituent ID where none exists. This demonstration will provide you with some tools and methods for adding a Constituent ID and linking that ID to a sheet of donations.
Apply Unique IDs
The first step is to create the Constituent ID. The Constituent ID can be anything, so long as it is unique. You might be tempted to simply number the rows of data consecutively starting at 1, and this will work. However, if a previous import used this same ID scheme, or if you do a second update to this instance and try to use the same scheme, those IDs will conflict. You can’t have two records with "1" as their imported ID.
To avoid this occurrence, however rare it might be, I use the following Constituent ID scheme: use the two-digit year, two-digit month, and two-digit day, followed by consecutive numbers. Here is an example:
*The header "Patron ID" used in screenshots and GIFs can be replaced with "Constituent ID."
Now every record has a unique Constituent ID, and that ID will not conflict with IDs from previous or future imports. When importing this data using the Import Manager, use this column as the imported account ID.
Create a Key
Next, we have a list of donations that need to be linked to their donors by more than their name. Here is a partial list:
We need to match these records to the records in the previous table. Matching by name alone is not sufficient and can create problems. This is why the Import Manager and our system match by name and email or name and address. We are going to do the same in these spreadsheets, starting with a name and email match.
Before we can match the constituents spreadsheet with the donations spreadsheet, we need to create a field that contains a key. The key will be comprised of the first name, last name, and email address. We will use the following Excel functions:
| CONCAT | This combines text items together. |
| TRIM | This removes any leading or trailing spaces from a piece of text. Excel will see “John “ as different from “John” because the first version has a space at the end; the trim command removes that space. |
| UPPER | This changes a piece of text to an all upper-case version of that text. Depending on the version and the settings in your copy of Excel, it may or may not recognize “john” as being the same as “John”. To avoid any problems, we will change both to “JOHN”. |
Here is the formula in action:
Now we do the same in the donation spreadsheet, and we get this:
Match Constituents to Donations
Now that both spreadsheets have the same key, we can use that key to match constituents to donations. For this we will use the XLOOKUP function.
| XLOOKUP | Finds the first term in the second term and returns the third. |
Here is a simple demonstration of how XLOOKUP works. If we have this table below, we could use this formula in a cell, =XLOOKUP(2,A1:A5,B1:B5), and that cell would have the word “Two” in it. The first term is 2, the second term is where to look for the first term (2), and the third term returns the value on the same row as the second term.
This formula also returns the same value: =XLOOKUP(2,A:A,B:B). The first version only looks at A1 thru A5, whereas this version looks at the entire A column.
Here is the XLOOKUP formula in action finding the Constituent ID in the constituent table and bringing the correct value to the donations table:
You probably noticed that not every donation successfully found a Constituent ID. In the case of Celeste Friend, the reason is that she has an email address in the donation table, but not in the constituent table. So the two keys do not match.
There are others in the same situation. Here is the donation table with all the failed lookups sorted to the top:
To find these Constituent IDs, we can change from name and email as the key to name and address. I have changed the key in the constituents tab to name and address; here you will change the key on the donation page. Note that as soon as the key is changed, the Constituent IDs populate:
Now we have two spreadsheets linked together by a common Constituent ID. This will make the import process quick and easy.