Identifying first time that three cells in a row have a non null value

Again, I didn't download your file...

Is it specifically 3 consecutive cells? Can there be 4 or 5 (or more) consecutive cells with 1s and do those count as a string of 3 in a row?

Try this...

Data Range

	B	C	D	E	F	G	H	I	J
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9
2		1	1		1	1	1	1
3
4	Header5

This array formula** entered in B4:

=IFERROR(INDEX(B1:J1,MATCH(TRUE,COUNTIF(OFFSET(B2,0,COLUMN(INDIRECT("1:"&COUNTA(B1:J1)))-1,1,3),1)=3,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by Tony Valko

Try this...

Data Range

	B	C	D	E	F	G	H	I	J
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9
2		1	1		1	1	1	1
3
4	Header5

This array formula** entered in B4:

=IFERROR(INDEX(B1:J1,MATCH(TRUE,COUNTIF(OFFSET(B2,0,COLUMN(INDIRECT("1:"&COUNTA(B1:J1)))-1,1,3),1)=3,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Tony this works amazing but have one issue that i need help with. I have some data where at least three "1's" do not exist consecutively in a row. In this case it's bringing me back Header 1 as a value. I'd like to return a result of "False" or something when there are not three "1's" consecutively in a row. Any help you can provide is greatly appreciated.
thank you,
Mike

Hmmm...

That shouldn't happen. If there aren't at least 3 consecutive 1s the MATCH function will (should) generate a #N/A error which will cause the IFERROR function to return a blank "".

Can you post a SMALL sample file that shows what you described?

OK, the tweak you made was causing a problem but after seeing your data layout I made a little tweak to the formula as well (makes it more efficient).

Array entered** in P3 and copied down:

=IFERROR(INDEX(A$2:K$2,MATCH(TRUE,COUNTIF(OFFSET(A3,0,COLUMN(A$2:K$2)-MIN(COLUMN(A$2:K$2)),1,3),1)=3,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Here's your updated file:

First instance(2).xlsx

how about
=IFERROR(INDEX($A$2:$K$2,MATCH("111",INDEX(A3:I3&B3:J3&C3:K3,0),0)),"")
ah i reread the post they are not really all ones are they ????
if not then
=iferror(INDEX($A$2:$K$2,MATCH("truetruetrue",INDEX((A3:I3<>"")&(B3:J3<>"")&(C3:K3<>""),0),0)),"") will find the first sequence of 3 non blanks

Nice one!

Fewer moving parts but I'd array enter and not use the inner INDEX.

Could get ugly if the condition is to find the first "large number" of consecutive cells.

That gave me an idea.

Since the cells in the sample file DO contain 1s or empty cells...

Data Range

	A	B	C	D	E	F	G	H
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2			1	1		1	1	1
3
4	Jun

Try this array formula**:

=IFERROR(INDEX(A1:H1,MATCH(3,(A2:F2)+(B2:G2)+(C2:H2),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

yep i like that but as you know i find array formulas unnecessary unless really needed
from the work book
=INDEX($A$2:$K$2,MATCH(3,INDEX((A3:F3)+(B3:G3)+(C3:H3),0),0))

Originally Posted by martindwilson

i find array formulas unnecessary unless really needed

And I like to use as few functions as possible to get the desired result.

fair point but when someone accidently just hits enter on a call with the array in it,they often come back here saying "i get value errors" or "it doesn't work now and this was done by some bloke who left 2 years ago"

Then teach people about array formulas!

http://www.excelforum.com/showthread.php?t=993164

i cant see the benefit..is there any plus to using an array formula ? calculation time? simplicity? or just because "look this is clever"
sometimes they are really needed especially in older versions of excel where say countifs is not used and sumproduct would be too long
most times you can avoid arrays using index. also offset is a particular bugbear of mine

Originally Posted by martindwilson

is there any plus to using an array formula ? calculation time? simplicity? or just because "look this is clever"

Depending on what the task is, all of the above are applicable.

As you know, typically array formulas are used for very complex calculations. It may be impossible to use a non-array formula to accomplish the task. Of course, you can load up your file with multitudes of helper formulas.

most times you can avoid arrays using index.

Adding an additional INDEX function to avoid array entry doesn't make the formula more efficient. In fact, it's the opposite. All you're doing is adding another function to the formula. The more functions the longer it takes to calculate.

also offset is a particular bugbear of mine

There's a lot of misunderstanding about array formulas and volatile functions:

Array formulas and volatile functions

well to keep tony happy you just have to add 2 to the match
=IFERROR(INDEX($A$2:$H$2,MATCH(3,(A3:I3)+(B3:H3)+(C3:K3),0)+2),"") array entered
but if you prefer a non array
=IFERROR(INDEX($A$2:$K$2,MATCH("111",INDEX(A3:I3&B3:J3&C3:K3,0),0)+2),"")
if you prefer the first formula same thing applies
=IFERROR(INDEX(A$2:K$2,MATCH(TRUE,COUNTIF(OFFSET(A3,0,COLUMN(A$2:K$2)-MIN(COLUMN(A$2:K$2)),1,3),1)=3,0)+2),"") array entered

Originally Posted by martindwilson

well to keep tony happy you just have to add 2 to the match
=IFERROR(INDEX($A$2:$H$2,MATCH(3,(A3:I3)+(B3:H3)+(C3:K3),0)+2),"") array entered

Now you're getting the idea!

What? Has Martin's account been hacked? Or is that the genuine "scourge of array formulas" himself offering...an array solution?!

Originally Posted by martindwilson

you just have to add 2 to the match

That will only find the last instance of exactly 3 consecutive cells.

If there are 3 or more consecutive cells then that will return an incorrect result.

i understood it to be find the first 3 identify the column header of the first of that triplet then find the header of the the last of that triplet
so if the first is matched in column 6 then the last of that triplet must be in column 8
but for the inserting columns problem one, i'd use tonys first formula and stick a dynamic range in it then you can insert cols before col m one possibility is
=IFERROR(INDEX(A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1),MATCH(TRUE,COUNTIF(OFFSET(A3,0,COLUMN(A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1))-MIN(COLUMN(A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1))),1,3),1)=3,0)),"") array entered
note: i tried using dynamic ranges in the other formulas but they kept giving #value errors when added together either with & or +

Originally Posted by martindwilson

i understood it to be find the first 3 identify the column header of the first of that triplet

Originally Posted by hotelmrrsn

Martin,
You are correct, what I need to be able to do is ID the first instance (from left to right) where there are three consecutive "1's".

Hmmm...

Then what does this mean:

Originally Posted by hotelmrrsn

It's AT LEAST 3, but can be any number of consecutive "1" values. I would want to return the column header of the 1st "1" when the count was 3 or more in a row. Thank you!

If it's only 3 consecutive cells then it's a lot easier.

=iferror(INDEX($A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1),MATCH(TRUE,COUNTIF(OFFSET($A3,0,COLUMN($A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1))-MIN(COLUMN($A$2:INDEX($2:$2,MATCH("grand total",$2:$2,0)-1))),1,3),1)=3,0)+2),"")
mind you if you already know the first one and that result is in say 02 eg 2014 - 06
then you only need to lookup that result
so p2 would be =if(o2="","",index($2:$2,match(o2,$2:$2,0)+2) which would return 2014 - 08

Ok, thanks for the clarification!

Since Martin has taken over I'm moving on.

Good luck!

are you sure you are using the right row references? it gives blank for me