# Finding the location of the last string of 26+ consecutive blanks in a Row

1. ## Finding the location of the last string of 26+ consecutive blanks in a Row

I've attached an example of the data I am trying to solve this problem for. Basically I am looking for strings of 26 blanks or more in a row, but I want to determine the ending location of the last string of 26+ blanks. I've been able to determine the location of the maximum string of blanks, but that max group of blanks isn't always the last set over 26.

Can anyone think of an approach to solve this issue and find the location of last string of blanks of 26 or more? The example I attached is a good example since there are two strings of 26+ blanks with the last one being shorter. I've also include the answer I'm looking at the end of the data in the example in case anyone is confused as to what I'm looking for.

I'd appreciate any help on this as I've been struggling with trying to find a solution for a while now.

Thanks!

2. ## Re: Finding the location of the last string of 26+ consecutive blanks in a Row

I used a helper row:
In cell A8:
=IF(ISBLANK(A7),1,0)
In cell B8:
=IF(ISBLANK(B7),A8+1,0)
then drag this forumula out to DW8

In cell DX4:
=MAX(COLUMN(A8:DW8)*(A8:DW8>25))
Enter this as an array formula by hitting Ctrl-Shift-Enter

3. ## Re: Finding the location of the last string of 26+ consecutive blanks in a Row

That won't work because I have hundreds of thousands of rows to compute. But I solved it with this equation:

MAX(COLUMN(A8:DW8)*(A8:DW8="")*(SUBTOTAL(2,OFFSET(A8:DW8,,COLUMN(A8:DW8),,26))=0))+26-1

There are currently 1 users browsing this thread. (0 members and 1 guests)