concatenate "if"

[Guest]

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?

 

[Guest]

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

 

[Roger Govier]

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,""))))

 

[Roger Govier]

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,""))))

 

[Guest]

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]

That is exactly what it does for me.