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

 

[jasonb75]

Your profile doesn't show which version of excel you are using, however I note that you have tried to use XLOOKUP so I will assume that you have an up to date version of office 365.

This formula is based on my understanding of your layout. Given that we have no sample to work with this may not be correct. Note that it will only see null strings, not empty cells.

=ADDRESS(ROW(K3),LET(f,FREQUENCY(IF(K3:OE3="",COLUMN(K3:OE3)),IF(K3:OE3<>"",COLUMN(K3:OE3),-COLUMN(K3:OE3))),XMATCH($J$1,f,1,1)-XLOOKUP($J$1,f,f,,1,1)+COLUMN(K1)-1),4)

 

[Domenic]

Or, maybe...

=SUBSTITUTE(CELL("address",INDEX(K3:OE3,MATCH(TRUE,COUNTIF(OFFSET(K3:OE3,,ROW(INDIRECT("1:"&COLUMNS(K3:OE3)-J1+1))-1,,J1),"")>=J1,0))),"$","")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Peter_SSs]

@jasonb75
Your formula appears to be looking for an exact number of "" cells whereas I believe the OP is looking for "at least" ..

@ffinlay
I am also assuming you have Excel 365 due to the use of XLOOKUP. I also note that K:OE is 385 columns, not 384.
Anyway, give this a try

=ADDRESS(ROW(K3),COLUMN(K3)-1+FIND(REPT(1,J$1),CONCAT(IF(K3:OE3="",1,0))),4)

 

[jasonb75]

Your formula appears to be looking for an exact number of "" cells

Yes and no, Peter. I realised the error in my logic as soon as I read your reply.

With the options that I used for XMATCH and XLOOKUP it worked correctly with my smaller test range. My error was using a range that was too small and that only had one set of cells meeting the criteria, a group of 5 "" cells with a criteria of 4.

I believe that I should have used 2 for the final argument of both functions, although without testing I could still be wrong.

 

[kvsrinivasamurthy]

clarification required. How counting done.
n=4
In the row there are 7 consecutive nulls . Say K5:Q5.
HOW you count consecutive 4's.
K5:N5, L5:O5, M5:P5, N5:Q5. Total=4
OR
K5:N5 Total =1, because O5:R5 is not fully null.

 

[Peter_SSs]

clarification required. How counting done.
n=4
In the row there are 7 consecutive nulls . Say K5:Q5.
HOW you count consecutive 4's.
K5:N5, L5:O5, M5:P5, N5:Q5. Total=4
OR
K5:N5 Total =1, because O5:R5 is not fully null.

Does this answer your question?

I need to find the first column that has more than n consecutive nulls.

 

[kvsrinivasamurthy]

G3=n where n is the number of consecutive nulls to check.

Data Range used is K3:O40. Change it as you want.

ARRAY formula is used. Result is the first cell address of the column.
Eg; If column M is the first column having the n consecutive blank cells Result is $M$3
Any clarifications welcome.

=IFERROR(ADDRESS(ROW($K$3),SMALL(IF(MMULT(1*TRANSPOSE(ROW(INDIRECT("$K$3:$K$"&40-$G$3+1))>0),1*(COUNTIF(OFFSET($K$3,ROW(INDIRECT("$K$3:$K$"&40-$G$3+1))-ROW($K$3),(COLUMN($K$3:$O$3)-COLUMN($K$3)),$G$3),"")=$G$3))>0,COLUMN($K$3:$O$3),""),1)),"")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[kvsrinivasamurthy]

Formula in the above post is slightly modified to give column name.

=IFERROR(SUBSTITUTE(ADDRESS(ROW($K$3),SMALL(IF(MMULT(1*TRANSPOSE(ROW(INDIRECT("$K$3:$K$"&40-$G$3+1))>0),1*(COUNTIF(OFFSET($K$3,ROW(INDIRECT("$K$3:$K$"&40-$G$3+1))-ROW($K$3),(COLUMN($K$3:$O$3)-COLUMN($K$3)),$G$3),"")=$G$3))>0,COLUMN($K$3:$O$3),""),1),4),ROW($K$3),""),"")

 

[jasonb75]

Formula in the above post is slightly modified to give column name.

I tried your mega long formula out of curiosity and it took me ages to get a result from it. Not sure if you got it wrong, or if the rest of us did but everything in post 1 points to

find the first column that has more than n consecutive nulls.

as meaning the first column in the row, which was the direction that you appeared to be taking in post 6, rather than the first column that has n consecutive nulls vertically which your formula is doing.