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.

 

[Fluff]

How about

=IF(GV4="",0,GW3+1)

 

[ollyhughes1982]

How about

=IF(GV4="",0,GW3+1)

Yes, that works perfectly, but I've just realised that I also need to also specifiy that, aswell it being any one of the four possible Ponty Express events, they must also be unique within the sequence. i.e. it must be all 4 of the different events at 4 parkuns in a row.

Is there a way that I can specify that the sequence only continues if the events are unique out of the 4 options? Or would it be possible to just achieve this with a formula in the second worksheet, likely with a FILTER function? I wouldn't need the helper columns in the first worksheet then?

 

[Fluff]

hat exactly do you want to see on the 2nd sheet?

 

[ollyhughes1982]

hat exactly do you want to see on the 2nd sheet?

I have amended the example document with manual entries (highlighted in yellow) to illustrate. Don't worry about column C (# Completed) in the second worksheet - I can add this later on, as it is just a count of how many times that event has been completed. It's A & B that are needed. Thanks for looking at this.

 

[Fluff]

Ok, this is completely different from your original question of counting sequential 1s, so it will need a new thread.

 

[ollyhughes1982]

Ok, this is completely different from your original question of counting sequential 1s, so it will need a new thread.

OK, thanks. Shall I reference this one as well?

 

[Fluff]

Not much point really as it has no bearing on what you are trying to do.

 

[ollyhughes1982]

Not much point really as it has no bearing on what you are trying to do.

OK, thanks. I've just published that

 

[Fluff]

For the helper column you could use

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