Consecutive Number of Years

[tbrynard01]

I have a large excel sheet of data with donation information is there an easy way I can get a list of names of people that have given 5 consecutive years
The columns are:
User ID / User Name / Date of Gift / Amount of Gift

Help will be appreciated

I have Excel 365
Thank you
Theresa

 

[kweaver]

Could you post even a small sample of your data using XL2BB?

 

[Peter_SSs]

I have Excel 365

Please update your Account details (click your user name at the top right of the forum) with that information so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Would something like this work for you?

tbrynard_1.xlsm
	A	B	C	D	E	F	G	H
1	User ID	User Name	Date	Amount			Consecutive Yrs:	5
2	ID1		11/07/2016	95
3	ID2		28/07/2015	91			User ID	Yes/No
4	ID3		4/12/2019	41			ID1	Yes
5	ID2		17/10/2013	57			ID2	No
6	ID1		1/04/2013	26			ID3	Yes
7	ID1		14/10/2016	37
8	ID2		25/11/2014	94
9	ID1		22/02/2020	53
10	ID2		22/01/2020	48
11	ID3		26/07/2014	78
12	ID2		13/11/2015	59
13	ID1		9/01/2016	29
14	ID3		30/11/2016	45
15	ID2		19/08/2018	77
16	ID1		20/11/2015	88
17	ID1		14/06/2015	73
18	ID2		18/10/2014	67
19	ID1		12/10/2013	4
20	ID2		18/10/2013	96
21	ID3		20/06/2017	28
22	ID2		31/07/2019	77
23	ID1		30/07/2014	62
24	ID3		9/08/2015	20
25	ID2		30/06/2019	34
26	ID1		19/10/2014	35
27	ID1		29/09/2019	34
28	ID2		21/04/2019	11
29	ID3		23/10/2018	99
30	ID2		28/08/2020	50
31	ID1		29/03/2014	6
32	ID2		24/04/2020	77
33	ID1		18/08/2020	88
34	ID3		19/02/2018	73
35	ID2		21/06/2013	67
36	ID1		26/03/2018	4
37	ID1		20/05/2017	55
38	ID2		9/02/2018	3
39	ID3		19/09/2016	55
40	ID2		23/02/2018	65
41	ID1		9/02/2014	87
42	ID2		7/10/2020	99
Sheet1

Cell Formulas
Range	Formula
G4:G6	G4	=UNIQUE(A2:A42)
H4:H9	H4	=IF(G4="","",LET(yrs,SORT(UNIQUE(FILTER(YEAR(C$2:C$42),A$2:A$42=G4))),seq,SEQUENCE(COUNT(yrs)-1),str,CONCAT(IF(INDEX(yrs,seq+1)-INDEX(yrs,seq)=1,1,0)),IF(ISNUMBER(FIND(REPT(1,H$1-1),str)),"Yes","No")))
Dynamic array formulas.

 

[tbrynard01]

Please update your Account details (click your user name at the top right of the forum) with that information so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Would something like this work for you?

tbrynard_1.xlsm
	A	B	C	D	E	F	G	H
1	User ID	User Name	Date	Amount			Consecutive Yrs:	5
2	ID1		11/07/2016	95
3	ID2		28/07/2015	91			User ID	Yes/No
4	ID3		4/12/2019	41			ID1	Yes
5	ID2		17/10/2013	57			ID2	No
6	ID1		1/04/2013	26			ID3	Yes
7	ID1		14/10/2016	37
8	ID2		25/11/2014	94
9	ID1		22/02/2020	53
10	ID2		22/01/2020	48
11	ID3		26/07/2014	78
12	ID2		13/11/2015	59
13	ID1		9/01/2016	29
14	ID3		30/11/2016	45
15	ID2		19/08/2018	77
16	ID1		20/11/2015	88
17	ID1		14/06/2015	73
18	ID2		18/10/2014	67
19	ID1		12/10/2013	4
20	ID2		18/10/2013	96
21	ID3		20/06/2017	28
22	ID2		31/07/2019	77
23	ID1		30/07/2014	62
24	ID3		9/08/2015	20
25	ID2		30/06/2019	34
26	ID1		19/10/2014	35
27	ID1		29/09/2019	34
28	ID2		21/04/2019	11
29	ID3		23/10/2018	99
30	ID2		28/08/2020	50
31	ID1		29/03/2014	6
32	ID2		24/04/2020	77
33	ID1		18/08/2020	88
34	ID3		19/02/2018	73
35	ID2		21/06/2013	67
36	ID1		26/03/2018	4
37	ID1		20/05/2017	55
38	ID2		9/02/2018	3
39	ID3		19/09/2016	55
40	ID2		23/02/2018	65
41	ID1		9/02/2014	87
42	ID2		7/10/2020	99
Sheet1

Cell Formulas
Range	Formula
G4:G6	G4	=UNIQUE(A2:A42)
H4:H9	H4	=IF(G4="","",LET(yrs,SORT(UNIQUE(FILTER(YEAR(C$2:C$42),A$2:A$42=G4))),seq,SEQUENCE(COUNT(yrs)-1),str,CONCAT(IF(INDEX(yrs,seq+1)-INDEX(yrs,seq)=1,1,0)),IF(ISNUMBER(FIND(REPT(1,H$1-1),str)),"Yes","No")))
Dynamic array formulas.

Thank you so much, that is perfect!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, don't forget to do that account update for your Excel version. ?