Excel Exctement

Hello all,

I have 13 columns, A to M, and each cell will either contain the number one or be left empty.  In column "N", for each row, I need to know if there are 6 consecutive columns which are empty.

I hope I have explained myself clearly.  Please let me know if you need any more details.

Many thanks, Alan.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There are a few ways, I think, to go about this.  However, to me, the most logical approach is to essentially build a binary representation of the status (blank or non-blank) of the cells A through M, for each row.  Once that is built, you can check to corresponding result to know if there are six consecutive blank columns.

Assuming we use column N to build the binary representation, and then column O to check the result, the formulas would look like this for row 1:

N1 :  =ISBLANK(A1)*1 + ISBLANK(B1)*2 + ISBLANK(C1)*4 + ISBLANK(D1)*8 + ISBLANK(E1)*16 + ISBLANK(F1)*32 + ISBLANK(G1)*64 + ISBLANK(H1)*128 + ISBLANK(I1)*256 + ISBLANK(J1)*512 + ISBLANK(K1)*1024 + ISBLANK(L1)*2048 + ISBLANK(M1)*4096

O1: =if(OR(N1=63,N1=126,N1=252,N1=504,N1=1008,N1=2016,N1=4032,N1=8064),"Found 6","Did not find 6")

The formulas in N1 capitalizes upon the fact that in Excel, in a math formula, TRUE=1 and FALSE=0.

Another way is to use concatenate to build a dummy string of 13 characters and choosing a character to represent a blank and a character to represent a non-blank then search the resulting string for a substring of 6 non-blank representations.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Canders_12Author Commented:

It will work up from 13 columns to 52 making the first solution impractical.  However, that little gem of a paragraph at the bottom my just the solution.  Give me 15 minutes but I think you are on for the points :)

Many thanks.
So after my post, I realized I have mislead you slightly.  My formula for O1 is not correct in all situations, since I only account for times when there are only six blank cells, and they are all contiguous.  I'll see if I can find a quick resolution to this.  My apologies.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Canders_12Author Commented:

Do not worry about the first solution :)

Do you know a quick way to concatenate many cells into one? (upto 52)
Yes Canders_12, and a quick catch!  :)  Just as quick as the author.  I am sure there is a way to check to binary representation, but the concatenate is probably easier.

Try this for a solution:

N1:  =concatenate(if(isblank(A1),"X","Y"),if(isblank(B1),"X","Y"),....,if(isblank(M1),"X","Y"))
O1: =if(iserror(search(N1,"XXXXXX"),"found 6","did not find 6")
Canders_12Author Commented:


Stop any further work.  Using your concept and the function I found below I think I can solve the problem :)

Many thanks.

  'Purpose: Concatenate all cells in a range
  'Inputs:  rRng - range to be concatenated
  '         sDelimiter - optional delimiter
  '            to insert between cell Texts
  'Returns: concatenated string
  Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function
Canders_12Author Commented:
Many many thanks :P
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.