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

=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

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

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" wrote in message
...
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" wrote in message
...
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