Find (and number) consecutive values of '1'

[ollyhughes1982]

Hi,

I have the formula (below) in column GV (GV162).

=IF(E162<>"",(IF(OR(LEFT(C162,6)="Pont y",LEFT(C162,5)="Ponte",LEFT(C162,5)="Ponty"),1,"")),"")

This formula checks whether the value in column C meets the stated criteria of the formula and puts a 1 (conditionally formatted in orange) in the cell if it does, but appears blank if it doesn’t.

This works fine, but what I want to know how to do is, add a formula in the next column (GW) that consecutively numbers any consecutive series of 1s. e.g. In the screenshot I would want to see a 1 in GW161 as it is a run of just one, a blank (or a 0?) in GW162, a 1 in GW163, a 2 in GW164 and a 3 in GW165. GW165 would then be a blank (or a 0?) again, as the run of 3 is over, and so on…

I’ve tried a couple of things, but can’t get them to work.

My endgame is to be able to identify the first time I achieve a run of 4 (or more) in a row (a challenge called ‘The Ponty Express’) and populate the data in the second worksheet (shown below), possibly using some sort of FILTER(SORT formula.

So, this worksheet would only show anything, once a full run of all 4 in 4 consecutive parkruns has been completed.

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

Thanks in advance!

Olly.

 

[ollyhughes1982]

For the helper column you could use

=IF(GV4="",0,IF(ISNA(MATCH(C4,INDEX(C:C,ROW()-GW3):C3,0)),GW3+1,1))

Great. What would I then need to do in the second worksheet?

 

[Fluff]

You need to stick to your other thread for that.

 

[ollyhughes1982]

Great. What would I then need to do in the second worksheetHi again

You need to stick to your other thread for that.

OK. With your last formula, should I be getting all 1s in column GW? Should I be expecting to see 1, 2, 3 and 4 in GW251, GW252, GW253, GW254, as this is the first instance where 4 do indeed appear in a row of 4. I have included (below) what I'm getting in the example file. It seems to be doing the same as in the GV column, except with 0s rather than blanks. Thanks again.

 

[Fluff]

Make sure that you have calculation set to automatic.

 

[ollyhughes1982]

Hi

Make sure that you have calculation set to automatic.

Yes, I always have that turned on. I have added it to the Example file, if you might be able to have a look, please. Thanks again

 

[Fluff]

The formula in col GW is not what I suggested.

 

[ollyhughes1982]

The formula in col GW is not what I suggested.

I did amend it slightly, to blank out if nothing on the row and changed the C:C to C4:C2003, I'm guessing it's that second part that's messed it up. I usually try and avoid the whole column references to save memory / storage etc in my large main file. I'll change it back now

 

[ollyhughes1982]

I've now amended to =IF(E4<>"",IF(GV4="",0,IF(ISNA(MATCH(C4,INDEX(C:C,ROW()-GW3):C3,0)),GW3+1,1)),"") and it works correctly. Apologies for that. Thanks.

 

[Fluff]

Glad to help & thanks for the feedback.