I am working at a nonprofit and we are trying to get a project off the ground where we send people postcards celebrating how many years they have been giving to us.
I am working with Excel in Office 365 on my Windows desktop. My knowledge level of Excel is probably somewhere between Intermediate and Advanced. This is my first post here so I hope I do this correctly.
Here's a small sample of the data I am working with:
| ID | Gift 5 | Gift 4 | Gift 3 | Gift 2 | Gift 1 | Desired Value |
|---|---|---|---|---|---|---|
| 0063532 | 2/12/2020 | 2/12/2020 | 2/12/2020 | 12/23/2019 | 12/20/2019 | 1 |
| 0038837 | 2/12/2020 | 10/3/2017 | 12/1/2016 | 11/20/2015 | 9/18/2014 | 1 |
| 0971229 | 2/7/2020 | 6/30/2019 | 5/31/2019 | 3/18/2019 | 3/5/2019 | 2 |
| 0113996 | 2/12/2020 | 2/12/2019 | 11/29/2016 | 7/19/2002 | 2 | |
| 0974727 | 2/20/2020 | 2/20/2020 | 9/23/2019 | 10/2/2018 | 12/13/2017 | 3 |
| 0176357 | 2/12/2020 | 10/16/2018 | 6/13/2018 | 9/14/2015 | 10/29/2014 | 3 |
| 0031280 | 2/12/2020 | 2/4/2019 | 1/23/2018 | 1/25/2017 | 2/29/2016 | 5 |
Their most recent gift must be from within the past 12 months, and the years must be consecutive, so if they gave in FY2020, FY2019, FY2017, FY2016, and FY2015, they have been giving consistently for 2 years, because they skipped FY2018. Like I said, the most recent gift should be from the past 12 months, so if someone gave in FY2016, FY2015, FY2014, FY2013 and FY2012, the solution should return a zero for them because even though they had 5 years of consistent giving, they've since broken their streak.
That's the other caveat to this, we want to base it on fiscal years, not calendar years. Our fiscal year starts July 1 and ends June 30.
Above I've provided some sample data, but of course in actuality we have about 25k constituents, some of whom have been giving for over 35 years, frequently many times a year. The real sheet in question here has many, many columns of gift dates (one for each gift ever recorded). The ID column is a unique Identifier for each donor. The column on the far right is the value I would like the solution to return, its not a value that would actually be present on the sheet to begin with.
So far, I tried to implement a solution where I created new columns, one for each fiscal year, and then searched all the gift dates for a gift that that takes place in that fiscal year. If there was such a gift, it would put the date in the column just so there was some sort of flag there. Then I had one final row that started with the left most fiscal year, and started counting and kept counting as long as it encountered cells that contained a value.
Sample formula that searches for a gift in a given fiscal year (I have another sheet with the start and end dates for every fiscal year, thats what's being refenced):
=IFNA(IFS(AND(B2<'fiscal year dates'!$B$2,B2>'fiscal year dates'!$A$2),B2,AND(C2<'fiscal year dates'!$B$2,C2>'fiscal year dates'!$A$2),C2,AND(D2<'fiscal year dates'!$B$2,D2>'fiscal year dates'!$A$2),D2,AND(E2<'fiscal year dates'!$B$2,E2>'fiscal year dates'!$A$2),E2,AND(F2<'fiscal year dates'!$B$2,F2>'fiscal year dates'!$A$2),F2),"")
Sample formula that counts number of recent consecutive fiscal years:
=IF(IFERROR((MATCH(TRUE,INDEX(G2:K2="",0),0)-1),5)=0,"",IFERROR((MATCH(TRUE,INDEX(G2:K2="",0),0)-1),5))
Here's how this solution looks in Excel:
| ID | Gift 5 | Gift 4 | Gift 3 | Gift 2 | Gift 1 | FY20 | FY19 | FY18 | FY17 | FY16 | Continuous |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0063532 | 2/12/2020 | 2/12/2020 | 2/12/2020 | 12/23/2019 | 12/20/2019 | 2/12/2020 | 1 | ||||
| 0038837 | 2/12/2020 | 10/3/2017 | 12/1/2016 | 11/20/2015 | 9/18/2014 | 2/12/2020 | 10/3/2017 | 12/1/2016 | 11/20/2015 | 1 | |
| 0971229 | 2/7/2020 | 6/30/2019 | 5/31/2019 | 3/18/2019 | 3/5/2019 | 2/7/2020 | 5/31/2019 | 2 | |||
| 0113996 | 2/12/2020 | 2/12/2019 | 11/29/2016 | 7/19/2002 | 2/12/2020 | 2/12/2019 | 11/29/2016 | 2 | |||
| 0974727 | 2/20/2020 | 2/20/2020 | 9/23/2019 | 10/2/2018 | 12/13/2017 | 2/20/2020 | 10/2/2018 | 12/13/2017 | 3 | ||
| 0176357 | 2/12/2020 | 10/16/2018 | 6/13/2018 | 9/14/2015 | 10/29/2014 | 2/12/2020 | 10/16/2018 | 6/13/2018 | 9/14/2015 | 3 | |
| 0031280 | 2/12/2020 | 2/4/2019 | 1/23/2018 | 1/25/2017 | 2/29/2016 | 2/12/2020 | 2/4/2019 | 1/23/2018 | 1/25/2017 | 2/29/2016 | 5 |
This solution works, I just worry about implementing it on my full data set, when I will have 35 fiscal year columns and hundreds of Gift Date columns. I also worry about duplicating the process month after month. Doing it once might be manageable, but applying it to this report each month to check for anniversaries would be pretty unreasonable.
I feel like this should be something excel is capable of, but I'm having trouble wrapping my mind around how to do it efficiently. Please let me know your thoughts! I am open to any type of solution for this. Its been driving me crazy for months now.
