software

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 quot;amp;quot; character on column
A using the booleans in column B. The result would like like this: amp;1amp;3amp;5.
Note there is a quot;amp;quot; before the first number as well.

ThanksResponses in programming.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;ExcelMonkeyquot; gt; wrote in message
...
gt; Sorry, I originally posted this in wrong discussion group (programming).
I
gt; would like to do this using existing Excel functions (non UDF).
gt;
gt; Can anybody tell me how to do a conditional concatenate. I have to
columns
gt; of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
gt; TRUE/FALSE (B2:B6)
gt;
gt; A B
gt; 1 TRUE
gt; 2 FALSE
gt; 3 TRUE
gt; 4 FALSE
gt; 5 TRUE
gt;
gt; in B7 I want to do an conditional concatenate using quot;amp;quot; character on
column
gt; A using the booleans in column B. The result would like like this:
amp;1amp;3amp;5.
gt; Note there is a quot;amp;quot; before the first number as well.
gt;
gt; Thanks
gt;
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)),quot;quot;,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

E7amp;E8amp;E9

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

quot;ExcelMonkeyquot; wrote:

gt; Sorry, I originally posted this in wrong discussion group (programming). I
gt; would like to do this using existing Excel functions (non UDF).
gt;
gt; Can anybody tell me how to do a conditional concatenate. I have to columns
gt; of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
gt; TRUE/FALSE (B2:B6)
gt;
gt; A B
gt; 1 TRUE
gt; 2 FALSE
gt; 3 TRUE
gt; 4 FALSE
gt; 5 TRUE
gt;
gt; in B7 I want to do an conditional concatenate using quot;amp;quot; character on column
gt; A using the booleans in column B. The result would like like this: amp;1amp;3amp;5.
gt; Note there is a quot;amp;quot; before the first number as well.
gt;
gt; Thanks
gt;

When you say quot;copy this downquot;, where do you mean to copy it down to?

Thanks

quot;Ashish Mathurquot; wrote:

gt; Hi,
gt;
gt; Please try the following
gt;
gt; First of all kindly arrange your data in range A1:B6 instead of A2:B6.
gt;
gt; In cell D7, enter TRUE. In cell E7, enter the following array formula
gt; (Ctrl Shift Enter)
gt;
gt; =IF(ISERROR(INDEX($D$1:$E$5,SMALL(IF($E$1:$E$5=$D$ 7,ROW($E$1:$E$5)),ROW(1:1)),1)),quot;quot;,INDEX($D$1:$E$5 ,SMALL(IF($E$1:$E$5=$D$7,ROW($E$1:$E$5)),ROW(1:1)) ,1))
gt;
gt; Copy this down
gt;
gt; In cell E11, enter the following formula
gt;
gt; E7amp;E8amp;E9
gt;
gt; Hope this solves your problem. If you have any further queries, please feel
gt; free to contact me at
gt;
gt;
gt;
gt; Regards
gt;
gt; quot;ExcelMonkeyquot; wrote:
gt;
gt; gt; Sorry, I originally posted this in wrong discussion group (programming). I
gt; gt; would like to do this using existing Excel functions (non UDF).
gt; gt;
gt; gt; Can anybody tell me how to do a conditional concatenate. I have to columns
gt; gt; of data. Column A has numbers: 1-5 (A2:A6). Column B has booleans:
gt; gt; TRUE/FALSE (B2:B6)
gt; gt;
gt; gt; A B
gt; gt; 1 TRUE
gt; gt; 2 FALSE
gt; gt; 3 TRUE
gt; gt; 4 FALSE
gt; gt; 5 TRUE
gt; gt;
gt; gt; in B7 I want to do an conditional concatenate using quot;amp;quot; character on column
gt; gt; A using the booleans in column B. The result would like like this: amp;1amp;3amp;5.
gt; gt; Note there is a quot;amp;quot; before the first number as well.
gt; gt;
gt; gt; Thanks
gt; gt;