| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need to add an IF condition to a formula
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
Is "StringConcat" function a user defined function (UDF)?
Could you please post the code for this UDF? -- R. Khoshravan Please click "Yes" if it is helpful. "JMALTO" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
Maybe it's the one from Chip Pearson's site, available he
http://www.cpearson.com/excel/stringconcatenation.aspx Hope this helps. Pete On Feb 5, 6:51*am, Khoshravan wrote: Is "StringConcat" function a user defined function (UDF)? Could you please post the code for this UDF? -- R. Khoshravan Please click "Yes" if it is helpful. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
That is correct ,Pete_UK. They offer a very simple example in their web page
-- When it commes to machinning, I am the BEST "Pete_UK" wrote: Maybe it's the one from Chip Pearson's site, available he http://www.cpearson.com/excel/stringconcatenation.aspx Hope this helps. Pete On Feb 5, 6:51 am, Khoshravan wrote: Is "StringConcat" function a user defined function (UDF)? Could you please post the code for this UDF? -- R. Khoshravan Please click "Yes" if it is helpful. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
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$34 3,""),"")) -- When it commes to machinning, I am the BEST "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
"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, -- When it commes to machinning, I am the BEST "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
"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 -- When it commes to machinning, I am the BEST "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
"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 -- When it commes to machinning, I am the BEST "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need to add an IF condition to a formula
"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 -- When it commes to machinning, I am the BEST "JBeaucaire" wrote: 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. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JMALTO" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Please Help Again-Condition Formula | Excel Worksheet Functions | |||
| Condition Formula | Excel Worksheet Functions | |||
| Add Condition to Formula | Excel Discussion (Misc queries) | |||
| MORE THAN ONE CONDITION IN A FORMULA | Excel Worksheet Functions | |||
| Add condition to formula | Excel Worksheet Functions | |||
Linear Mode
