Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Concatenate
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Concatenate
Responses in programming.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "ExcelMonkey" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Concatenate
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 Regards "ExcelMonkey" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Concatenate
When you say "copy this down", where do you mean to copy it down to?
Thanks "Ashish Mathur" wrote: 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 Regards "ExcelMonkey" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |