| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate "if"
I am trying to concatenate a range of cells based on a condition. It would
be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate "if"
I see that my colums did not line up. Maybe this helps.
ROW 1 Column E=IF (F2=F3,1,0) Col F ...... Col J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C "Golfking" wrote: I am trying to concatenate a range of cells based on a condition. It would be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate "if"
Hi
The following formula entered into D4 and copied down, would satisfy your criteria based upon the sample shown. I'm not sure that it would work for all of your cases, however, as it would depend upon the number of products for each company. =IF(C4<0,"",IF(AND(C2=1,C3=1), F2&", "&F3&", "&F4, IF(C3=1,F3&", "&F4,IF(C3=0,F4,"")))) -- Regards Roger Govier "Golfking" wrote in message ... I am trying to concatenate a range of cells based on a condition. It would be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate "if"
Sorry, all the F ranges should be J in the formula
=IF(C4<0,"",IF(AND(C2=1,C3=1), J2&", "&J3&", "&J4, IF(C3=1,J3&", "&J4,IF(C3=0,J4,"")))) -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi The following formula entered into D4 and copied down, would satisfy your criteria based upon the sample shown. I'm not sure that it would work for all of your cases, however, as it would depend upon the number of products for each company. =IF(C4<0,"",IF(AND(C2=1,C3=1), F2&", "&F3&", "&F4, IF(C3=1,F3&", "&F4,IF(C3=0,F4,"")))) -- Regards Roger Govier "Golfking" wrote in message ... I am trying to concatenate a range of cells based on a condition. It would be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate "if"
It doesn't concatenate like I want. I was hoping for the "product" used by
ABC Corp for example to be concatenated together in D4. "Product A, Product B, Product F". I'll try to make it work. Thanks for the help. "Roger Govier" wrote: Sorry, all the F ranges should be J in the formula =IF(C4<0,"",IF(AND(C2=1,C3=1), J2&", "&J3&", "&J4, IF(C3=1,J3&", "&J4,IF(C3=0,J4,"")))) -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi The following formula entered into D4 and copied down, would satisfy your criteria based upon the sample shown. I'm not sure that it would work for all of your cases, however, as it would depend upon the number of products for each company. =IF(C4<0,"",IF(AND(C2=1,C3=1), F2&", "&F3&", "&F4, IF(C3=1,F3&", "&F4,IF(C3=0,F4,"")))) -- Regards Roger Govier "Golfking" wrote in message ... I am trying to concatenate a range of cells based on a condition. It would be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
concatenate "if"
That is exactly what it does for me.
-- Regards Roger Govier "Golfking" wrote in message ... It doesn't concatenate like I want. I was hoping for the "product" used by ABC Corp for example to be concatenated together in D4. "Product A, Product B, Product F". I'll try to make it work. Thanks for the help. "Roger Govier" wrote: Sorry, all the F ranges should be J in the formula =IF(C4<0,"",IF(AND(C2=1,C3=1), J2&", "&J3&", "&J4, IF(C3=1,J3&", "&J4,IF(C3=0,J4,"")))) -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi The following formula entered into D4 and copied down, would satisfy your criteria based upon the sample shown. I'm not sure that it would work for all of your cases, however, as it would depend upon the number of products for each company. =IF(C4<0,"",IF(AND(C2=1,C3=1), F2&", "&F3&", "&F4, IF(C3=1,F3&", "&F4,IF(C3=0,F4,"")))) -- Regards Roger Govier "Golfking" wrote in message ... I am trying to concatenate a range of cells based on a condition. It would be nice to have a function = concatenate if(Range, Criteria, Concatenate Range) Here is an example of the sheet1. 1 Column E=IF (F2=F3,1,0) Column F ........ Column J 2 1 ABC Corp 85252 Product A 3 1 ABC Corp 85252 Product B 4 0 ABC Corp 85252 Product F 5 1 BCD Inc. Product B 6 0 BCD Inc. Product K 7 0 FJK Company Product A 8 1 LMN Inc. Product A 9 1 LMN Inc. Product B 10 0 LMN Inc. Product C I'd like the resultant to show up in the row that has the last of the company names which has the 0 in it in column D. So in D4 the result would be "Product A, Product B, Product F" in D6 the result would be "Product B, Product K" and so on. Does this make sense? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Linear Mode
