Most Consecutive appearances in a range

[ollyhughes1982]

Hi,

I have the below worksheet (see first image), which lists all of my completed parkruns.

What I want to identify from this range (C4:C2003) is which event that I have done the most times consecutively (rather than the most times in total). I have manually calculated the correct figures (highlighted in yellow) for illustration of what I’m trying to achieve. As well as identifying which event it is, I’d also like to show the earliest and latest date for which this consecutive range took place.

On top of those three calculations, I’d also like to populate the second worksheet (see second image, below) with the actual list of the consecutive events, along with their number in the run and the dates. I’m guessing some sort of FILTER function might be needed for this. I've again highlighted in yellow, the manual results I am trying to automate.

I'd like to avoid adding any helper columns to these worksheets, if at all possible.

I have added a link to the (small) file, here: https://1drv.ms/x/c/f08b781118912fd2/Ee0-4PthSBBOtjXBxvogX0MBjJ76ECnenE_AJLdZyK_Wmg?e=51q17t

Thanks in advance!

Olly.

 

[Cubist]

Give this a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	MY parkruns - ALL COMPLETED RUNS
2	All of my parkruns, including all information / data / calculations. The majority of the other worksheets, in this section, use this worksheet to calculate.
3	Run #	Event (Venue) Run #	Event (Venue)	Country	Date Completed	Finishing Position #	Consecutive Matching Events (Venues)		Event	# Consecutive	Earliest Date	Latest Date
4	1	3	Newport parkrun	United Kingdom	9/4/11	54	1		Newport parkrun	81	9/4/11	21/05/2016
Sheet3

Cell Formulas
Range	Formula
I4:J4	I4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),h,HSTACK(XLOOKUP(m,s,C4:C407),m+1),h)
K4	K4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),XLOOKUP(1,TAKE(s,XMATCH(m,s)),TAKE(E4:E410,XMATCH(m,s))))
L4	L4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),XLOOKUP(m,s,E4:E407))
Dynamic array formulas.

 

[ollyhughes1982]

How about

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(0,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,HSTACK(INDEX(f,r-MAX(s)+1,{1,3}),INDEX(f,r,3)))

Also, is there a way to display each result individually, rather than all as a result of one formula? As I may need to display just one or two of each in particular areas.

 

[ollyhughes1982]

Give this a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	MY parkruns - ALL COMPLETED RUNS
2	All of my parkruns, including all information / data / calculations. The majority of the other worksheets, in this section, use this worksheet to calculate.
3	Run #	Event (Venue) Run #	Event (Venue)	Country	Date Completed	Finishing Position #	Consecutive Matching Events (Venues)		Event	# Consecutive	Earliest Date	Latest Date
4	1	3	Newport parkrun	United Kingdom	9/4/11	54	1		Newport parkrun	81	9/4/11	21/05/2016
Sheet3

Cell Formulas
Range	Formula
I4:J4	I4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),h,HSTACK(XLOOKUP(m,s,C4:C407),m+1),h)
K4	K4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),XLOOKUP(1,TAKE(s,XMATCH(m,s)),TAKE(E4:E410,XMATCH(m,s))))
L4	L4	=LET(s,SCAN(0,C4:C407=C5:C408,LAMBDA(a,b,IF(b,a+1,0))),m,MAX(s),XLOOKUP(m,s,E4:E407))
Dynamic array formulas.

Thanks. Similar to fluff's solution, this almost works, except for the max date, the max date is one too low. The streak should end on 28/05/2011.

 

[Fluff]

How about

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,HSTACK(INDEX(f,r-MAX(s)+1,{1,3}),INDEX(f,r,3)))

 

[ollyhughes1982]

How about

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,HSTACK(INDEX(f,r-MAX(s)+1,{1,3}),INDEX(f,r,3)))

Yes, that works, thanks. Is there any way that I can use the formula to display each part of the data separately?

 

[Fluff]

Like

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r-MAX(s)+1,1))

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r-MAX(s)+1,3))

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r,3))

 

[ollyhughes1982]

Like

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r-MAX(s)+1,1))

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r-MAX(s)+1,3))

=LET(f,FILTER(C4:E2003,C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,r,3))

Thanks, that's great. Any idea on a spill formula for the second worksheet, at all?

 

[Fluff]

How about

=LET(f,FILTER('All Completed Runs'!C4:E2003,'All Completed Runs'!C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,INDEX(f,SEQUENCE(MAX(s),,r-MAX(s)+1),{1,3}))

 

[ollyhughes1982]

Brilliant, that works perfectly - thank you. Is there a way to add in the values for column a, i.e. 1-81? Just number each entry, unless the value of B is "".

 

[Fluff]

How about

=LET(f,FILTER('All Completed Runs'!C4:E2003,'All Completed Runs'!C4:C2003<>""),v,TAKE(f,,1),s,SCAN(1,DROP(v,-1)=DROP(v,1),LAMBDA(a,b,IF(b,a+1,1))),r,XMATCH(MAX(s),s)+1,HSTACK(SEQUENCE(MAX(s)),INDEX(f,SEQUENCE(MAX(s),,r-MAX(s)+1),{1,3})))