Count longest streak

[droot]

Hi,

I have a large dataset and I'm trying to count the longest streak (consecutive years) for the individual for a specific country and sport.
I have no idea how to approach this so any help will be greatly appreciated.

Thanks in advance.

Example data (streak column shows the expected answer) -

Year	Country	Sport	Name	streak
2012​	Australia	Football	Alan
2013​	Australia	Football	Alan
2014​	Australia	Football	Alan
2015​	Australia	Football	Alan
2016​	Australia	Football	Alan
2017​	Australia	Football	Alan	6​
2018​	Australia	Hockey	Alan
2019​	Australia	Hockey	Alan
2020​	Australia	Hockey	Alan	3​
2019​	China	Tennis	Alex
2020​	China	Tennis	Alex	2​
2008​	Brazil	Hockey	Brian	1​
2013​	United Kingdom	Football	John
2014​	United Kingdom	Football	John
2015​	United Kingdom	Football	John
2017​	United Kingdom	Football	John
2018​	United Kingdom	Football	John
2019​	United Kingdom	Football	John
2020​	United Kingdom	Football	John	4​
2020​	Australia	Tennis	Daniel	1​

 

[Snakehips]

@droot
Assuming you require a formula solution?
The below requires a 'Helper' column which you can hide. As to it's speed / efficiency on your large dataset, I cannot say.

Book1
	A	B	C	D	E	F
1	Year	Country	Sport	Name	streak	Helper
2	2012	Australia	Football	Alan		1
3	2013	Australia	Football	Alan		2
4	2014	Australia	Football	Alan		3
5	2015	Australia	Football	Alan		4
6	2016	Australia	Football	Alan		5
7	2017	Australia	Football	Alan	6	6
8	2018	Australia	Hockey	Alan		1
9	2019	Australia	Hockey	Alan		2
10	2020	Australia	Hockey	Alan	3	3
11	2019	China	Tennis	Alex		1
12	2020	China	Tennis	Alex	2	2
13	2008	Brazil	Hockey	Brian	1	1
14	2013	United Kingdom	Football	John		1
15	2014	United Kingdom	Football	John		2
16	2015	United Kingdom	Football	John	3	3
17	2017	United Kingdom	Football	John		1
18	2018	United Kingdom	Football	John		2
19	2019	United Kingdom	Football	John		3
20	2020	United Kingdom	Football	John	4	4
21	2020	Australia	Tennis	Daniel	1	1
22
Sheet16

Select E2 & F2 to expose the formulas.
Hope that helps.