+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    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
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    03-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    8

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1