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.

 

[ollyhughes1982]

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})))

Brilliant thanks so much. Perfect.

 

[ollyhughes1982]

Thanks, everyone for all of your help.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ollyhughes1982]

Apologies, one last thing; is there a way I can get the 81 count from the formula (below)? It would be helpful, as I wouldn't then need the helper column.

=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})))

 

[ollyhughes1982]

I have used this formula:

=COUNT(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}))))/2

I just added a count and divided by 2. It isn't very pretty, but does work.

 

[Fluff]

You can use

=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))),MAX(s))

 

[ollyhughes1982]

I have used this formula:

=COUNT(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}))))/2

It isn't very pretty, but does work.

You can use

=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))),MAX(s))

Thanks

 

[Fluff]

You're welcome.