Conditional Concatenate

[Guest]

Sorry, I originally posted this in wrong discussion group (programming). I
would like to do this using existing Excel functions (non UDF).

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

 

[Bob Phillips]

Responses in programming.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi,

Please try the following

First of all kindly arrange your data in range A1:B6 instead of A2:B6.

In cell D7, enter TRUE. In cell E7, enter the following array formula
(Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($D$1:$E$5,SMALL(IF($E$1:$E$5=$D$7,ROW($E$1:$E$5)),ROW(1:1)),1)),"",INDEX($D$1:$E$5,SMALL(IF($E$1:$E$5=$D$7,ROW($E$1:$E$5)),ROW(1:1)),1))

Copy this down

In cell E11, enter the following formula

E7&E8&E9

Hope this solves your problem. If you have any further queries, please feel
free to contact me at

(e-mail address removed)

Regards

 

[Guest]

When you say "copy this down", where do you mean to copy it down to?

Thanks