I need to add an IF condition to a formula

[JMALTO]

I am using a very good function called "StringConcat" where I'm comparing
cells on columns F and G, and when any cell in column H match it, then I
collect data from that very same row in column A. Example: Because data that
match in columns G1, G4 with the data on column H, then I pull data from the
corresponding rows in column A. I1=A1 and A4. Notice that also A7 match cell
H1, but I don't want it because cell B7 is not equal to 1.
So, that is what I want to add to the formula a condition that IF cells in
column B=1 then I want the corresponding data on cells in column A.
This is what I have so far.

=StringConcat(",
",IF($F$1:$F$7=H1,$A$1:$A$7,""),IF($G$1:$G$7=H1,$A$1:$A$7,""))


A B C D E F G H I
WSt USA Ch Srv Ob LHD TD TL USA
1 100 1 30 24 24 100, 103
2 101 1 33 30 100, 102, 105
3 102 1 30 33 101, 103
4 103 1 33 24 50 105
5 104 1 84 85 74
6 105 1 30 50 84
7 106 1 33 24 85

When it commes to machinning, I am the BEST

 

[Khoshravan]

Is "StringConcat" function a user defined function (UDF)?
Could you please post the code for this UDF?

 

[JBeaucaire]

Try this version:

=StringConcat(",",IF($F$1:$F$7=G1,IF($B$1:$B$7=1,$A$1:$A$7,""),""))

Ctrl-Shift-Enter to confirm the array.

 

[Pete_UK]

Maybe it's the one from Chip Pearson's site, available here:

http://www.cpearson.com/excel/stringconcatenation.aspx

Hope this helps.

Pete

 

[JMALTO]

That is correct ,Pete_UK. They offer a very simple example in their web page

 

[JMALTO]

Awsome, "JBeaucaire", you were so close. With a minor modification I got it
to work. -------Thanks man-------.
This is the final formula

=StringConcat(",
",IF($F$7:$F$343=$H7,IF(B$7:B$343=1,$A$7:$A$343,""),""),IF($G$7:$G$343=$H7,IF(B$7:B$343=1,$A$7:$A$343,""),""))

 

[JMALTO]

"JBeaucaire"
What if the data on cells A B F and G are in Sheet2?
It seems like doesn't want to pull data from different sheets

Thanks,

 

[JMALTO]

"JBeaucaire"
What if the data on cells A B F and G are in Sheet2?
It seems like doesn't want to pull data from different sheets

 

[JMALTO]

"JBeaucaire"
What if the data on cells A B F and G are in Sheet2?
It seems like doesn't want to pull data from different sheets

 

[JMALTO]

"JBeaucaire"
What if the data on cells A B F and G are in Sheet2?
It seems like doesn't want to pull data from different sheets