Counting Unique Constituent ID's Across 2 Columns

Patrick Hunt

Here's what I'm trying accomplish: within a gift export, count the unique constituent ID's of alumni and spouses who are also alumni. If a constituent ID shows up on 1 gift as hard credit donor and on a different gift as the spouse of the hard credit donor, count that constituent only once.

Here's a simplified example: Sample Spreadsheet.xlsx

Keep in mind that arrays in Crystal Reports have a maximum size of 1,000 items.

Thank you for your help.

Marie Stark

What about a formula, something like this? You would also have to account for null values somewhere in the formula.

If Cons is Alum =yes and spouse is alum = yes then 1
else
If Cons is Alum =yes and spouse is alum = no then 1
else
If Cons is Alum =no and spouse is alum = yes then 1
else
If Cons is Alum no and spouse is alum = no then 0

Patrick Hunt

Thanks for your post, Marie. Your suggestion would yield a count of how many transactions are associated with alumni, but I'm not sure if I could use it to find the unique count of alumni donors (including spouses who are alumni) in a gift report.


Here's the approach I'm currently looking into:

1. Use a formula to create a unique household ID for couples (the approach that I've taken is to concatenate the const ids of spouses, with the smaller const ID being concatenated first so that no matter where the hard credit of the gift is applied the household ID will always be the same). Non-married individuals will have a household ID equal to their const ID.
2. for each household ID, create an "alumni household score" for the number of alumni associated (0 - 2)
3. Sum the values of the "alumni household scores" for each unique household ID

I haven't got step 3 of the above working yet but so far it looks promising.

Marie Stark

If you used the household ID as an alias, and did a distinct count of the houeshold ID maybe that would work. I think you're on the right track.