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

ffinlay

New Member
Joined
Mar 2, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 2, 2017
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,606
Office Version
  1. 365
Platform
  1. Windows
Glad it worked for you. Thanks for the confirmation. :)

.. and thanks for updating your profile. (y)
 

Newcook

New Member
Joined
Aug 10, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 10, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,409

ADVERTISEMENT

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
ABNOPQRSTUVWXYZAAABACADAEAFAG
124123014600000000129
1254570000062189330000
12677880000000123456800
127000123090978000000
128808000088888888888
1497$Q$126
Sheet4
Cell Formulas
RangeFormula
B149B149=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:

Excel Formula:
=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

New Member
Joined
Aug 10, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,409
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

New Member
Joined
Aug 10, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,315
Messages
5,705,693
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top