List longest streak values

[ollyhughes1982]

Hi all,

I have the below worksheet (‘All Completed Runs - WILSON-I.’) where I list all of the Event (Venue) #s that I have completed (by doing a VLOOKUP of the ‘All Completed Runs’ worksheet). What I want to do is, list (from cell F4 downwards) the longest streak of consecutive numbers. I already have a formula that counts the number in the longest streak, which is below, but I actually want to list the values.

Formula that calculates the count of the longest streak:

=IF('All Completed Runs'!B4="","",IFERROR(MAX(FREQUENCY(SORT(UNIQUE('All Completed Runs'!B4:B2003)),IF(ISNA(MATCH(SEQUENCE(MAX('All Completed Runs'!B4:B2003)),'All Completed Runs'!B4:B2003,)),SEQUENCE(MAX('All Completed Runs'!B4:B2003))))),1))

So, what I want to do is find a formula to list the actual values that are being included within the above count. (he result is 11 - Event (Venue) #s: 253-263). If there are streaks of equally long length then take the earliest streak.

Additionally, if possible, would it be possible to get a conditional formatting formula that highlights the rows of the longest streak, within the main ‘MY parkruns - ALL COMPLETED RUNS - WILSON-INDEX’ (A1:D2003) table.

Link to file (small file, opens quickly):
Example.xlsx

Thanks in advance,

Olly.

 

[ollyhughes1982]

Also posted on Excel Forum: List longest streak values

 

[ollyhughes1982]

This has now been solved with the following:

longest streak

=LET(z,D4:D2003,r,ROW(z),MAX(FREQUENCY(IF(z<>"",r),IF(z="",r))))

List longest streak

=LET(z,D4:D2003,r,SEQUENCE(ROWS(z)),b,IF(z="",r),f,FREQUENCY(IF(z<>"",r),b),m,MAX(f),s,SMALL(b,MATCH(m,f,)),INDEX(z,SEQUENCE(m,,s-m)))

CF formula applies to =$A$4:$D$2003

=MATCH(TRUE,ISTEXT($D4:$D50),)-XMATCH(TRUE,ISTEXT(OFFSET($D4,,,-MIN(50,ROWS($D$3:$D4)))),,-1)+MIN(50,ROWS($D$3:$D4))-2=$F$4

Thanks.