Conditional Concatenate

Can anybody tell me how to do a conditional concatenate. I have to columns
of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
TRUE/FALSE (B2:B6)

A B
1 TRUE
2 FALSE
3 TRUE
4 FALSE
5 TRUE

in B7 I want to do an conditional concatenate using "&" character on column
A using the booleans in column B. The result would like like this: &1&3&5.
Note there is a "&" before the first number as well.

Thanks

How about a UDF? Paste this into a VBA module in your workbook and then
enter into B7:
=CondConcat(B1:B5)

Function CondConcat(myRange As Range) As String
Application.Volatile
Dim myCell As Range
For Each myCell In myRange
If myCell.Value Then
CondConcat = CondConcat & myCell.Offset(0, -1).Value
End If
Next
End Function


"ExcelMonkey" wrote:

> Can anybody tell me how to do a conditional concatenate. I have to columns
> of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
> TRUE/FALSE (B2:B6)
>
> A B
> 1 TRUE
> 2 FALSE
> 3 TRUE
> 4 FALSE
> 5 TRUE
>
> in B7 I want to do an conditional concatenate using "&" character on column
> A using the booleans in column B. The result would like like this: &1&3&5.
> Note there is a "&" before the first number as well.
>
> Thanks

Thanks. I actually posted this in the wrong discussion group. I actually
want to avoid using a UDF if possible. Otherwise, I will consider this.

Thanks for your time.

"Martin" wrote:

> How about a UDF? Paste this into a VBA module in your workbook and then
> enter into B7:
> =CondConcat(B1:B5)
>
> Function CondConcat(myRange As Range) As String
> Application.Volatile
> Dim myCell As Range
> For Each myCell In myRange
> If myCell.Value Then
> CondConcat = CondConcat & myCell.Offset(0, -1).Value
> End If
> Next
> End Function
>
>
> "ExcelMonkey" wrote:
>
> > Can anybody tell me how to do a conditional concatenate. I have to columns
> > of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
> > TRUE/FALSE (B2:B6)
> >
> > A B
> > 1 TRUE
> > 2 FALSE
> > 3 TRUE
> > 4 FALSE
> > 5 TRUE
> >
> > in B7 I want to do an conditional concatenate using "&" character on column
> > A using the booleans in column B. The result would like like this: &1&3&5.
> > Note there is a "&" before the first number as well.
> >
> > Thanks

=if(B2,A2,"")&if(B3,A3,"")&if(b4,A4,"")&if(B5,A5,"")&if(B6,A6,"")

Concatenation is supported by array formulas if that is what you were hoping
for. Laurent Longre has a free addin which provides this capability:


http://xcell05.free.fr/

look for Morefunc.xll

--
Regards,
Tom Ogilvy



"ExcelMonkey" wrote:

> Can anybody tell me how to do a conditional concatenate. I have to columns
> of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
> TRUE/FALSE (B2:B6)
>
> A B
> 1 TRUE
> 2 FALSE
> 3 TRUE
> 4 FALSE
> 5 TRUE
>
> in B7 I want to do an conditional concatenate using "&" character on column
> A using the booleans in column B. The result would like like this: &1&3&5.
> Note there is a "&" before the first number as well.
>
> Thanks

Won't happen if you want the array to be variable and the formula to cope
with it, as CONCATENATE won't support arrays, so you need a UDF

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:FA400C83-C1E9-455E-AFD7-22FEE45B6E19@microsoft.com...
> Can anybody tell me how to do a conditional concatenate. I have to
columns
> of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
> TRUE/FALSE (B2:B6)
>
> A B
> 1 TRUE
> 2 FALSE
> 3 TRUE
> 4 FALSE
> 5 TRUE
>
> in B7 I want to do an conditional concatenate using "&" character on
column
> A using the booleans in column B. The result would like like this:
&1&3&5.
> Note there is a "&" before the first number as well.
>
> Thanks

Thanks all.

"Bob Phillips" wrote:

> Won't happen if you want the array to be variable and the formula to cope
> with it, as CONCATENATE won't support arrays, so you need a UDF
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
> news:FA400C83-C1E9-455E-AFD7-22FEE45B6E19@microsoft.com...
> > Can anybody tell me how to do a conditional concatenate. I have to
> columns
> > of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
> > TRUE/FALSE (B2:B6)
> >
> > A B
> > 1 TRUE
> > 2 FALSE
> > 3 TRUE
> > 4 FALSE
> > 5 TRUE
> >
> > in B7 I want to do an conditional concatenate using "&" character on
> column
> > A using the booleans in column B. The result would like like this:
> &1&3&5.
> > Note there is a "&" before the first number as well.
> >
> > Thanks
>
>
>