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.
Canders_12Asked:
Who is Participating?
 
AgeOfEmpiresCommented:
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
 
Canders_12Author 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
 
AgeOfEmpiresCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Canders_12Author Commented:
AgeOfEmpires:

Do not worry about the first solution :)

Do you know a quick way to concatenate many cells into one? (upto 52)
0
 
AgeOfEmpiresCommented:
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
 
Canders_12Author 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
 
Canders_12Author 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.

All Courses

From novice to tech pro — start learning today.