How to find consecutive blanks in an array of 1 row and 384 columns

[ffinlay]

I'm working with an application where I have a table of multiple rows each with 384 columns. Data in each row is filled with either a text string, or with "" nulls. I need to find the first column that has more than n consecutive nulls. I'd prefer this to be a formula rather than a macro. I've tried to do this with Offset(), but I can't find a good way to generate the offset distance without nesting it multiple times.

My current attempt is as follows, but this only gets me past the first set of nulls with the minimum quantity of nulls is defined in $J$1:

=IF(COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),"")<$J$1,XLOOKUP("",OFFSET(K3:OD3,0,COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),"")+MATCH("",K3:OE3,0)-1),OFFSET(K2:OD2,0,MATCH("",K3:OE3,0)-1+COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),"")),"None",0,1)&","&COUNTIF(OFFSET(K3:OE3,0,MATCH("",OFFSET(K3:OD3,0,MATCH("",K3:OE3,0)-1+COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),""),1,32),0)+COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),"")+MATCH("",K3:OE3,0)-1-1,1,25),""),XLOOKUP("",K3:OE3,$K$2:$OE$2,"None",0,1)&","&COUNTIF(OFFSET(K3:OE3,0,MATCH("",K3:OE3,0)-1,1,19),""))

Any help will be appreciated

 

[ffinlay]

.

This is why I love this board. I rarely need to ask a question, but when I do, the responses are outstanding. Usually there's a solution for my problems already on the board. Peter's function seems to be the most compact and provides the answer I'm looking for. I tried most of the others, and they seem to work, except I got a #NAME error on the "LET() function on the first response. Thank you to all.

And yes, I'm using Office 365 and have updated my profile to reflect that. I guess I need to install XL2BB as well!

 

[Peter_SSs]

Glad it worked for you. Thanks for the confirmation.

.. and thanks for updating your profile.

 

[Newcook]

I realize this is an old post, but the question is exactly what I need. However, I can't seem to find which final formula suggest by Peter to use. I have rows O124 to JF128 containing numbers and zeros. Cell A149 contains the n minimum number of zeros. I'm looking for the first series of zeros greater than n that is all on one row.

 

[Newcook]

I realize this is an old post, but the question is exactly what I need. However, I can't seem to find which final formula suggest by Peter to use. I have rows O124 to JF128 containing numbers and zeros. Cell A149 contains the n minimum number of zeros. I'm looking for the first series of zeros greater than n that is all on one row.

I will pose the question on a new thread

 

[Eric W]

Welcome to MrExcel!

A new thread would have been better, but I noticed your question anyway. Finding the first 0-string on multiple rows is a bit trickier, but this should work:

Book2
	A	B												N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
124															1	2	3	0	1	4	6	0	0	0	0	0	0	0	0	1	2	9
125															4	5	7	0	0	0	0	0	6	2	1	8	9	33	0	0	0	0
126															77	88	0	0	0	0	0	0	0	1	2	3	4	5	6	8	0	0
127															0	0	0	1	2	3	0	9	0	9	7	8	0	0	0	0	0	0
128															8	0	8	0	0	0	0	8	8	8	8	8	8	8	8	8	8	8
149	7	$Q$126
Sheet4

Cell Formulas
Range	Formula
B149	B149	=ADDRESS(ROW(O124)-1+MATCH(MIN(IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),"")),IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),""),0),COLUMN(O124)-1+MIN(IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(O124:JF128=0,"0",".")),SEQUENCE(ROWS(O124:JF128),,1,COLUMNS(O124:JF128)),COLUMNS(O124:JF128))),"")))

If you have the LET function, that can theoretically be shortened to:

=LET(rng,O124:JF128,r,ROWS(rng),c,COLUMNS(rng),tl,INDEX(rng,1,1),fa,IFERROR(FIND(REPT("0",A149),MID(CONCAT(IF(rng=0,"0",".")),SEQUENCE(r,,1,c),c)),""),ADDRESS(ROW(tl)-1+MATCH(MIN(fa),fa,0),COLUMN(tl)-1+MIN(fa)))

 

[Newcook]

thank you Eric, that worked very well. I saw that if I change the the O124 say to AL124, I get the right answer. I did also pose the question elsewhere because I realized that also need to find the first occurence but within a date range. I'm not sure how to adjust the O in 124 to fall within two dates.

 

[Eric W]

Are you using the full version of the formula or the LET version? I'll see how to incorporate the date requirement into it, and post to the other thread, if no one beats me to it.

 

[Newcook]

I have used your exact formula copy/pasted. I have my dates in O4 to JF4, and my specific start date in B149 and end date in C149. I just realized I don't really need the end date because your above formula already takes into account how many consecutive zeros I need.