Find a sequence of 4 particular values, by criteria

[ollyhughes1982]

Hi,

I have two worksheets in the linked file: The first contains the source data. The the second (in the image, below) is the one I need to populate with a function - likely sort of FILTER one. I need to populate columns A and B as per the manually entered entries (highlighted in yellow).

What I need to do is, identify the first sequence where 4 particular parkrun events have been completed in 4 consecutive parkruns. With the relevant data then populating the second worksheet, as per the image.

The criteria for identifying the four parkruns is as per below:

IF(OR(LEFT(C4,6)="Pont y",LEFT(C4,5)="Ponte",LEFT(C4,5)="Ponty"),1,"")

Link to the example file (small size and opens quickly) is here: Example.xlsx

Thanks in advance!

Olly.

 

[ollyhughes1982]

On another thread Fluff has now kindly provided a formula in the source data worksheet (in column GW) that identifies where the criteria for this challenge are met and numbers them 1-4. So, what I need in the FILTER formula (or whatever type is required) will be to identify the first instance where there is a run of 1-4 in column GW and then put the relevant Event (Venue) name and Date Completed in the 4 rows of the target worksheet. I have highlighted in the image (below) where this first instance occurs.

The other thread can be found here: Find (and number) consecutive values of '1'

Thanks.

 

[ollyhughes1982]

Also now posted on Excel Forum: Find first sequence of 1-4

 

[ollyhughes1982]

I think I have now solved this. Not the most sophisticated solution, but it seems to do what I need. I used the following formulas:

In Column A:
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$C$4:$C$2003),-3,0),""),"")
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$C$4:$C$2003),-2,0),""),"")
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$C$4:$C$2003),-1,0),""),"")
=IFERROR(IF('All Completed Runs'!C4<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$C$4:$C$2003),0,0),""),"")

In Column B:
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$E$4:$E$2003),-3,0),""),"")
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$E$4:$E$2003),-2,0),""),"")
=IFERROR(IF(A7<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$E$4:$E$2003),-1,0),""),"")
=IFERROR(IF('All Completed Runs'!C4<>"",OFFSET(XLOOKUP(4,'All Completed Runs'!$GW$4:$GW$2003,'All Completed Runs'!$E$4:$E$2003),0,0),""),"")

Thanks.