logical OR 2 columns and conctenate reesult in VBA

[stravo]

[TABLE="class: MsoNormalTable, width: 2"]
<tbody>[TR]
[TD="width: 80, bgcolor: transparent"] xxx<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"]

1<o></o>​

[/TD]
[TD="width: 80, bgcolor: transparent"]

<!--[if gte vml 1]><v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"> <v:stroke joinstyle="miter"/> <vath gradientshapeok="t" o:connecttype="rect"/> </v:shapetype><v:shape id="Text_x0020_Box_x0020_2" o:spid="_x0000_s1026" type="#_x0000_t202" style='position:absolute;left:0;text-align:left; margin-left:-3.35pt;margin-top:-53.9pt;width:46.8pt;height:49.8pt;z-index:251661312; visibility:visible;mso-wrap-style:square;mso-width-percent:0; mso-height-percent:0;mso-wrap-distance-left:9pt;mso-wrap-distance-top:0; mso-wrap-distance-right:9pt;mso-wrap-distance-bottom:0; mso-position-horizontal:absolute;mso-position-horizontal-relative:text; mso-position-vertical:absolute;mso-position-vertical-relative:text; mso-width-percent:0;mso-height-percent:0;mso-width-relative:margin; mso-height-relative:margin;v-text-anchor:top' o:gfxdata="UEsDBBQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKSRvU7DMBSFdyTewfKKEqcMCKEmHfgZgaE8wMW+SSwc27JvS/v23KTJgkoXFsu+P+c7Ol5vDoMTe0zZBl/LVVlJgV4HY31Xy4/tS3EvRSbwBlzwWMsjZrlprq/W22PELHjb51r2RPFBqax7HCCXIaLnThvSAMTP1KkI+gs6VLdVdad08ISeCho1ZLN+whZ2jsTzgcsnJwldluLxNDiyagkxOquB2Knae/OLUsyEkjenmdzbmG/YhlRnCWPnb8C898bRJGtQvEOiVxjYhtLOxs8AySiT4JuDystlVV4WPeM6tK3VaILeDZxIOSsuti/jidNGNZ3/J08yC1dNv9v8AAAA//8DAFBLAwQUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAF9yZWxzLy5yZWxzhI/NCsIwEITvgu8Q9m7TehCRpr2I4FX0AdZk2wbbJGTj39ubi6AgeJtl2G9m6vYxjeJGka13CqqiBEFOe2Ndr+B03C3WIDihMzh6RwqexNA281l9oBFTfuLBBhaZ4ljBkFLYSMl6oAm58IFcdjofJ0z5jL0MqC/Yk1yW5UrGTwY0X0yxNwri3lQgjs+Qk/+zfddZTVuvrxO59CNCmoj3vCwjMfaUFOjRhrPHaN4Wv0VV5OYgm1p+LW1eAAAA//8DAFBLAwQUAAYACAAAACEAxIxa6OUCAAD4BgAAHwAAAGNsaXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWy8VdtS2zAQfe9M/0Gjd3DuQAaHgRSYzqSQSeADNrJsaypLrqTc+PquJOdSCjy0nfohWXmPj86eXcuXV5tKkhU3VmiV0vZpixKumM6EKlL6/HR3ck6JdaAykFrxlG65pVejz58uYVgYqEvBCDIoO4SUls7VwySxrOQV2FNdc4W5XJsKHC5NkWQG1shcyaTTag2SCoSiowPVF3BAlkb8AZXU7DvPxqBWYJFSsuHxnUajZH/PDEO1ujf1vJ4ar5w9rKaGiCyl6JyCCi2iSZNoYLhMXj1VHAg2uak8Xuc52aS00+4PegPk2mLcHXQ6rUjHN44wzPcvel2fZpgfdDseGrcrHz8mYOXthxQoMUrB4Eierb04tfq93m7rbFfxk1d3ozeks6/d44nb4E2cqtBjW0+wR5YoPS5BFfzaGL0uOWTWI6JJ6GbcKBi229N6rsX6m87QXVg6Hfj+iXH7qmFYG+vuua6ID1JqOHNhH1hNrIvydhBvidVSZHdCyrAwxWIsDVmBTOlduJqKfoFJRdYpveh3+tGRdyla4XqLohKOGyJFldLzPQiG3sdblYVRcCBkjLE6qcL0eQu9ULeZh4b6zmRbf2eB/2iv0VgzzhUeBRiU2rxQssYXPKX2xxIMp0R+Vdipi3avhzAXFr3+Gc4nMceZxXEGFEOqlDpKYjh2uGqF6pW+xlbmovE26vCKpHVzt5U8FBPU+v5XYCZBIQazEIAs8MhizgQ6qeY1C72o2ZS52Ip2u48mNT4eI254vsM6G7E7mK3ZIXudu9e49oERoQGAPjf+BpMNypU44inl6uR5jgfoC464f8xz8TzH0YozhcWCE4q4bc1zYDjeY5BiYYSXjFB4L8PsUeZJVNySB74mM12BaspFFZ7DjVAijsxEF4KBJI8zfxphVfgbAFxlUzAwe1M0qv6vog9igkDsvhe7H9al5fN6hvbF9zFOMyL8KZW8OvPDo803yn9YjtejnwAAAP//AwBQSwMEFAAGAAgAAAAhAJxOXiHiBgAAOhwAABoAAABjbGlwYm9hcmQvdGhlbWUvdGhlbWUxLnhtbOxZT28bRRS/I/EdRntv4/+NozpV7NgNtGmj2C3qcbwe704zu7OaGSf1DbVHJCREQRyoxI0DAiq1EpfyaQJ***R+Bd7M7K534jVJ2wgqaA7x7tvfvP/vzZvdy1fuRQwdEiEpjzte9WLFQyT2+YTGQce7NRpcWPeQVDieYMZj0vHmRHpXNt9/7zLe8BlNxhyLySgkEUHAKJYbuOOFSiUba2vSBzKWF3lCYng25SLCCm5FsDYR+AgERGytVqm01iJMY28TOCrNqM/gX6ykJvhMDDUbgmIcgfSb0yn1icFODqoaIeeyxwQ6xKzjAc8JPxqRe8pDDEsFDzpexfx5a5uX1/BGuoipFWsL6wbmL12XLpgc1IxMEYxzodVBo31pO+dvAEwt4/r9fq9fzfkZAPZ9sNTqUuTZGKxXuxnPAsheLvPuVZqVhosv8K8v6dzudrvNdqqLZWpA9rKxhF+vtBpbNQdvQBbfXMI3ulu9XsvBG5DFt5bwg0vtVsPFG1DIaHywhNYBHQxS7jlkytlOKXwd4OuVFL5AQTbk2aVFTHmsVuVahO9yMQCABjKsaIzUPCFT7ENO9nA0FhRrAXiD4MITS/LlEknLQtIXNFEd78MEx14B8vLZ9y+fPUHH958e3//p+MGD4/s/WkbOqh0cB8VVL7797M9HH6M/nnzz4uEX5XhZxP/6wye//Px5ORDKZ2He8y8f//b08fOvPv39u4cl8C2Bx0X4iEZEohvkCO3zCAwzXnE1J2PxaitGIabFFVtxIHGMtZQS/n0VOugbc8zS6Dh6dInrwdsC2kcZ8OrsrqPwMBQzRUskXwsjB7jLOetyUeqFa1pWwc2jWRyUCxezIm4f48My2T0cO/HtzxLom1laOob3QuKoucdwrHBAYqKQfsYPCCmx7g6ljl93qS+45FOF7lDUxbTUJSM6drJpsWiHRhCXeZnNEG/HN7u3UZezMqu3yaGLhKrArET5EWGOG6/imcJRGcsRjljR4dexCsuUHM6FX8T1pYJIB4Rx1J8QKcvW3BRgbyHo1zB0rNKw77J55CKFogdlPK9jzovIbX7QC3GUlGGHNA6L2A/kAaQoRntclcF3uVsh+h7igOOV4b5NiRPu07vBLRo4Ki0SRD+ZiZJYXiXcyd/hnE0xMa0GmrrTqyMa/13jZhQ6t5Vwfo0bWuXzrx+V6P22tuwt2L3KambnRKNehTvZnntcTOjb35238SzeI1AQy1vUu+b8rjl7//nmvKqez78lL7owNGg9i9hB24zd0cqpe0oZG6o5I9elGbwl7D2TARD1OnO6JPkpLAnhUlcyCHBwgcBmDRJcfURVOAxxAkN71dNMApmyDiRKuITDoiGX8tZ4GPyVPWo29SHEdg6J1S6fWHJdk7OzRs7GaBWYA20mqK4ZnFVY/VLKFGx7HWFVrdSZpVWNaqYpOtJyk7WLzaEcXJ6bBsTcmzDUIBiFwMstON9r0XDYwYxMtN9tjLKwmCicZ4hkiCckjZG2ezlGVROkLFeWDNF22GTQB8dTvFaQ1tZs30DaWYJUFNdYIS6L3ptEKcvgRZSA28lyZHGxOFmMjjpeu1lresjHScebwjkZLqMEoi71HIlZAG+YfCVs2p9azKbKF9FsZ4a5RVCFVx/W70sGO30gEVJtYxna1DCP0hRgsZZk9a81wa3nZUBJNzqbFvV1SIZ/TQvwoxtaMp0SXxWDXaBo39nbtJXymSJiGE6O0JjNxD6G8OtUBXsmVMLrDtMR9A28m9PeNo/c5pwWXfGNmMFZOmZJiNN2q0s0q2QLNw0p18HcFdQD20p1N8a9uimm5M/JlGIa/89M0fsJvH2oT3QEfHjRKzDSldLxuFAhhy6UhNQfCBgcTO+AbIH3u/AYkgreSptfQQ71r605y8OUNRwi1T4NkKCwH6lQELIHbclk3ynMquneZVmylJHJqIK6MrFqj8khYSPdA1t6b/dQCKluuknaBgzuZP6592kFjQM95BTrzelk+d5ra+CfnnxsMYNRbh82A03m/1zFfDxY7Kp2vVme7b1FQ/SDxZjVyKoChBW2gnZa9q+pwitutbZjLVlca2bKQRSXLQZiPhAl8A4J6X+w/1HhM/sFQ2+oI74PvRXBxwvNDNIGsvqCHTyQbpCWOIbByRJtMmlW1rXp6KS9lm3W5zzp5nJPOFtrdpZ4v6Kz8+HMFefU4nk6O/Ww42tLW+lqiOzJEgXSNDvImMCUfcnaxQkaB9WOB1+TIND34Aq+R3lAq2laTdPgCj4ywbBkvwx1vPQio8BzS8kx9YxSzzCNjNLIKM2MAsNZ+g0mo7SgU+nPJvDZTv94KPtCAhNc+kUla6rO577NvwAAAP//AwBQSwMEFAAGAAgAAAAhAJxmRkG7AAAAJAEAACoAAABjbGlwYm9hcmQvZHJhd2luZ3MvX3JlbHMvZHJhd2luZzEueG1sLnJlbHOEj80KwjAQhO+C7xD2btJ6EJEmvYjQq9QHCMk2LTY/JFHs2xvoRUHwsjCz7DezTfuyM3liTJN3HGpaAUGnvJ6c4XDrL7sjkJSl03L2DjksmKAV201zxVnmcpTGKSRSKC5xGHMOJ8aSGtHKRH1AVzaDj1bmIqNhQaq7NMj2VXVg8ZMB4otJOs0hdroG0i+hJP9n+2GYFJ69elh0+UcEy6UXFqCMBjMHSldnnTUtXYGJhn39Jt4AAAD//wMAUEsBAi0AFAAGAAgAAAAhALvlSJQFAQAAHgIAABMAAAAAAAAAAAAAAAAAAAAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAAAAAAAAAAAAAAA2AQAAX3JlbHMvLnJlbHNQSwECLQAUAAYACAAAACEAxIxa6OUCAAD4BgAAHwAAAAAAAAAAAAAAAAAgAgAAY2xpcGJvYXJkL2RyYXdpbmdzL2RyYXdpbmcxLnhtbFBLAQItABQABgAIAAAAIQCcTl4h4gYAADocAAAaAAAAAAAAAAAAAAAAAEIFAABjbGlwYm9hcmQvdGhlbWUvdGhlbWUxLnhtbFBLAQItABQABgAIAAAAIQCcZkZBuwAAACQBAAAqAAAAAAAAAAAAAAAAAFwMAABjbGlwYm9hcmQvZHJhd2luZ3MvX3JlbHMvZHJhd2luZzEueG1sLnJlbHNQSwUGAAAAAAUABQBnAQAAXw0AAAAA"/><![endif]--><!--[if !vml]-->​

​

<tbody> [TD="width: 85, bgcolor: white"]<!--[endif]--><!--[if !mso]-->

<tbody> [TD="bgcolor: transparent"]<!--[endif]-->

After Logical OR<o></o>​

<!--[if !mso]--> [/TD]
</tbody>

<!--[endif]--><!--[if !mso & !vml]--> <!--[endif]--><!--[if !vml]--> [/TD]
</tbody>

​

<!--[endif]-->xxx<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"] xx<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"]

<o></o>​

[/TD]
[TD="width: 80, bgcolor: transparent"]

<o> </o>​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"] x<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"]

1<o></o>​

[/TD]
[TD="width: 80, bgcolor: transparent"]

x<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"] <o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"]

<o></o>​

[/TD]
[TD="width: 80, bgcolor: transparent"]

<o> </o>​

[/TD]
[/TR]
[TR]
[TD="width: 80, bgcolor: transparent"] xx<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"]

1<o></o>​

[/TD]
[TD="width: 80, bgcolor: transparent"]

xx<o></o>​

[/TD]
[/TR]
</tbody>[/TABLE]
Col A col B colC<o></o>
Col A and Col B can be variable length columns (but they arethe same size)<o></o>
I need to perform a logical OR on col A & B (whichgenerates Col C). I want to take the output of the Logical OR (col C) andconcatenate all the non-blank cells separated by a delimiter (a space for thisexample). So the final result is a single cell with the contents “xxx x xx”which I’ll call the ANSWER<o></o>
Since this is part of a user form I don’t want to generateCol C in the spreadsheet. I want it to be virtual with only a single cell result(hence it either needs to be a cell formula or a VBA function). I can do thiswith a cell formula using the concatenate function but for large columns, thecell formula won’t fit as it takes too many characters. I have a routine thatcan take a range of variable type data that will remove the blank cells andgenerate the ANSWER. Here it is: <o></o>
<o></o>
Function ConCat(Delimiter As Variant, ParamArrayCellRanges() As Variant) As String<o></o>
Dim CellAs Range, Area As Variant<o></o>
IfIsMissing(Delimiter) Then Delimiter = ""<o></o>
<o> </o>
For EachArea In CellRanges<o></o>
IfTypeName(Area) = "Range" Then<o></o>
For Each Cell In Area<o></o>
If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value<o></o>
Next<o></o>
Else<o></o>
ConCat = ConCat & Delimiter& Area<o></o>
EndIf<o></o>
Next<o></o>
ConCat =Mid(ConCat, Len(Delimiter) + 1)<o></o>
End Function<o></o>
<o></o>
I can generate Col C with this formula {=IF((B1:B5=1),IF((A1:A5)="","",(A1:A5)),""))}<o></o>
If I use the ConCat function on Col C, {=concat(“ “,C1:C5)}results in the ANSWER “xxx x xx”<o></o>
If I type the values of ColC into the ConCat function{=concat(“ “,”xxx”,””,”x”,””,”xx”) I get the ANSWER<o></o>
However if I combine the two to do this in 1-step:<o></o>
{=concat(" ",IF((B1:B5=1),IF((A1:A5)="","",(A1:A5)),""))}I get #VALUE! Error<o></o>
Looking at the debugger, the resultant of the above IF statementsthat passes the values to ConCat is exactly {=concat(“ “,”xxx”,””,”x”,””,”xx”)which then evaluates to the #VALUE! Error. What is happening? Why won’t itwork?<o></o>
Some clues from spending a lot of debugging time. TheCaonCat function bombs at the step in yellow when it is trying to concatenate thevariant variable AREA (which TypeName (AREA) says is a string. Apparently theIF statements when parsed into the input to ConCat are placed into AREA as amulti-dimensional Array and it is not compatible with concatenate. The othercalls , {=concat(“ “,C1:C5)} and {=concat(“ “,”xxx”,””,”x”,””,”xx”) are parsedas individual cells and loop through one at a time. The combined function istrying to do it all in a single pass and not one cell at a time. <o></o>
Anyone have any ideas on how to fix this?<o></o>
Thanks<o></o>
mark<o></o>
<o> </o>

 

[mikerickson]

What are your inputs in columns A and B and the desire out puts.

OR is a logical function that acts on boolean values, none of the entries that you show are boolean.

The formula that you posted seems to be an array version of =REPT(A1, (B1=1))

In words, what do you want ConCat to do?

 

[mikerickson]

Perhaps what you want is this UDF. The arguments of ConcatIf mirror those of SumIf.

If you want to concatenate the values in column A, whenever column B is 1, with a space delimiter, the formula =ConcatIf(B:B, 1, A:A, " ") should work.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[stravo]

hi mike, thanks I'll try it. What i'm doing is taking a changing (due to new formulations, test results, regulations, etc.) list of fungicides for my vineyard that have different effectiveness for each of several or so fungi that I need to protect against (e.g. black rot, phomopsis, powdery mildew, downey mildew, botrytis, anthracnose, etc) that are active at particular times and conditions during the growing season. The relative effectiveness is given for each fungus as either a "blank", "x", "xx", "xxx" where blank means no control and "xxx" means excellent control. I have a list of 40 or so fungicides that I can use. So I set up a spreadsheet that lists all the chemicals down column 1 with dates across the first row. So after determining what I need to spray for, I pick several fungicides to put in my sprayer. I indicate this by putting a "1" in the cell for the fungicide and date. I leave the first six rows for calculating the overall "spray effectiveness" for each fungus for what I am planning to spray. This is the output of the subroutine that I want so at a glance I can see what I am protecting my vines against. Having "xxx" in a box means I have essentially provided protection for that disease for the next 2 weeks. Too many "xxx" in one disease means I'm wasting money and having "x" or blank means I am taking a risk for protecting the vines (I may not want to protect against a disease if it isn't active so that is desirable too). There are other factors that I need to track such as re-entry times, pre-harvest intervals, maximum rates per acre per the manuf label, compatibility with mixing fungicides together, and several other factors. At the end of the season I have a full spray matrix of what I used and when which I can quickly figure out my inventories at the end of each year so I can put my order in for the season in the spring each year. I had put together this spreadsheet just using the concatenate function but it was a lot of work to add new fungicides, and other aspects of my grand plan. I was looking for a better (and easier way to do this). I will copy your code into the spreadsheet and try it out later today. I appreciate your help. I was stymied by this for a while.

 

[stravo]

Hi Mike, I just tested it. It works great. Thank you so much.Mark