# 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?

Commented:
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.
0

Author Commented:
Crumbs,

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.
0

Commented:
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.
0

Author Commented:
AgeOfEmpires:

Do not worry about the first solution :)

Do you know a quick way to concatenate many cells into one? (upto 52)
0

Commented:
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")
0

Author Commented:

AgeOfEmpires:

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
0

Author Commented:
Many many thanks :P
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.