Help with to find donations in one year but not the next.

[NeedInformation]

I'm having a brain cramp with trying to determine the total number (both in dollars and number of unique donors but reported separately) of all donors who gave in the first year but not in the second that live in a certain city. I don't need the donors themselves, just the total of the donations and number of unique donors.

I can sumproduct to find who donated in both. I'm having difficulty in finding who donated in the first year but did NOT donate in the second.

For example if my first year is 2018 and 2017 is the second and looking the City of Dallas I should only find those that are starred which should return a sum of $70 and a count of unique donors as 2

The logic is to find all donations in dallas, then find all who donated in 2018 but not in 2017.

I have a feeling this is a complicated, nested sumproduct but I'm stumped. Any pointers are much appreciated.

All values in any column are not necessarily unique.

DonorID	City	Year	Donation
10	San Antonio	2018	$10
10	San Antonio	2018	$20
20	Dallas	2017	$30
20	Dallas	2018	$40
20	Dallas	2019	$40
40	Dallas	2018	$20*
50	San Antonio	2018	$30
50	Dallas	2017	$30
60	Dallas	2018	$30*
60	Dallas	2018	$20*
60	San Antonio	2018	$50

 

[CA_Punit]

Which version of Excel are you using??

 

[NeedInformation]

365 but would hope to be somewhat backward compatible as others may end up taking over the spreadsheet.

Thanks!

 

[CA_Punit]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
4	DonorID	City	Year	Donation
5	10	San Antonio	2018	10			Year	City	Unique Donor in 2018 Not in 2017	Sum
6	10	San Antonio	2018	20			2018	Dallas	2	70
7	20	Dallas	2017	30
8	20	Dallas	2018	40
9	20	Dallas	2019	40
10	40	Dallas	2018	20
11	50	San Antonio	2018	30
12	50	Dallas	2017	30
13	60	Dallas	2018	30
14	60	Dallas	2018	20
15	60	San Antonio	2018	50
Sheet5

Cell Formulas
Range	Formula
I5	I5	="Unique Donor in "&G6&" Not in "&G6-1
I6	I6	=SUMPRODUCT(IF(UNIQUE(IFNA(IF(MATCH(IF(B5:B15=H6,IF(C5:C15=G6,A5:A15)),IF(B5:B15=H6,IF(C5:C15=G6-1,A5:A15)),0)>=1,0,1),1)*IF(B5:B15=H6,IF(C5:C15=G6,A5:A15)))>0,1))
J6	J6	=SUM(INDEX(D5:D15,FILTER(IF(IFNA(IF(MATCH(IF(B5:B15=H6,IF(C5:C15=G6,A5:A15)),IF(B5:B15=H6,IF(C5:C15=G6-1,A5:A15)),0)>=1,0,1),1)*IF(B5:B15=H6,IF(C5:C15=G6,A5:A15))>0,1,0)*(ROW(A5:A15)-ROW(A5)+1),IF(IFNA(IF(MATCH(IF(B5:B15=H6,IF(C5:C15=G6,A5:A15)),IF(B5:B15=H6,IF(C5:C15=G6-1,A5:A15)),0)>=1,0,1),1)*IF(B5:B15=H6,IF(C5:C15=G6,A5:A15))>0,1,0)*(ROW(A5:A15)-ROW(A5)+1)>0)))

 

[CA_Punit]

I will try to solve without using Dynamic array tommorow as it is already to late

 

[alansidman]

Here is an alternative solution. Using Power Query. Bring your table into PQ. Filter the table for Dallas only. Then duplicate the table. In the first table filter for 2017. In the duplicated table filter for 2018. Now merge the two tables joining them on a left outer join on the User ID. Expand the Table to show the year and Donation amount. The result looks like the following. Backward compatible to 2010.

Book1
	A	B	C	D	E	F
1	DonorID	City	Year	Donation	Table1.Year	Table1.Donation
2	20	Dallas	2018	40	2017	30
3	40	Dallas	2018	20
4	60	Dallas	2018	30
5	60	Dallas	2018	20
Sheet3

 

[KRice]

Could you clarify something, please? Regarding the rule about a donor giving in one year but not another, does it matter at all about the city? For example, donor 60 is shown in three rows, twice in Dallas and once in San Antonio. All three entries are for 2018. If one of the Dallas entries were changed to 2017, then that would negate considering donor 60 for any query regarding Dallas, but would it negate donor 60 for a query involving San Antonio?

 

[NeedInformation]

City is significant. In the end, there will be a report, by city, summarizing the amount of donations and number of donations given in the first year and not the second. It's to help understand if there is a correlation between the location and increase/decrease of donors. Can't just simply add up all donations in a year and subtract the next. It's an apples-to-apples comparison of a donor in a given city over time.

 

[KRice]

I'm not sure you understood my question. For all three Donor 60 entries (which are currently shown for 2018)...If one of the Dallas entries were changed to 2017, would that negate donor 60 for a query involving San Antonio? In other words, Donor 60 would have given in 2018 in San Antonio and in 2017 (in Dallas)...would Donor 60 be disqualified from a San Antonio query? Based on your response, in this example I've described, I am guessing that since Donor 60 gave in a different city in 2017, they would still be included in the tally for San Antonio.

 

[NeedInformation]

Sorry, I did misunderstand. Perhaps I still do.

For donor 60, they gave 3 times in 2018, 2 in Dallas and 1 in San Antonio. Since Donor 60 didn't give in Dallas in 2017 then their two 2018 donations in Dallas count.

Based on your question, if one of the Dallas entries was changed to 2017 then their 2 2018 donations in Dallas would not count and would not, therefore, be considered part of the group who gave in 2018 but not in 2017.

However, if the target city was changed to San Antonio and one of the Dallas contributions was changed to 2017 then Donor 60 would be included becuase they donated in San Antonio in 2018 but not in 2017. Donors 10 and 50 would be counted too, if the target city were changed to San Antonio.