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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
AgeOfEmpires:
Do not worry about the first solution :)
Do you know a quick way to concatenate many cells into one? (upto 52)
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
O1: =if(iserror(search(N1,"XXX
Try this for a solution:
N1: =concatenate(if(isblank(A1
O1: =if(iserror(search(N1,"XXX
ASKER
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
ASKER
Many many thanks :P
ASKER
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.