|
IF - THEN - OTHERWISE -......UNLESS! ???
Hi, this one is a doosie! If anyone can help it'd be really great.
I have three columns of data....A1:A10, B1:B10 and C1:C5. Column A is Numeric Data in each cell, Column B contains text in each cell, and I want my function to work on column C. I want the fuction to check down along column A, from A1, until it finds a value greater than zero, at which time it will copy that A cell's corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any time a value is entered into C1, then I want the function to copy the next value it finds into C2 instead, and so on into C3 etc. With this sheet, column B has about 100 text entries, but only 10 or so of those entries corresponding numbers in column A will be greater than 0. I want all those text entries organised at the top of column C for easy referance, so putting a seperate function into each of 100 column C cells is not an option. Does anyone have any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
Put this in C1 and copy down
=IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dim" wrote in message ... Hi, this one is a doosie! If anyone can help it'd be really great. I have three columns of data....A1:A10, B1:B10 and C1:C5. Column A is Numeric Data in each cell, Column B contains text in each cell, and I want my function to work on column C. I want the fuction to check down along column A, from A1, until it finds a value greater than zero, at which time it will copy that A cell's corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any time a value is entered into C1, then I want the function to copy the next value it finds into C2 instead, and so on into C3 etc. With this sheet, column B has about 100 text entries, but only 10 or so of those entries corresponding numbers in column A will be greater than 0. I want all those text entries organised at the top of column C for easy referance, so putting a seperate function into each of 100 column C cells is not an option. Does anyone have any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
Thanks Bob but I've tried that and it doesn't exactly do what I want.
Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? "Bob Phillips" wrote: Put this in C1 and copy down =IF(ISERROR(SMALL(IF($A$1:$A$100,ROW($A$1:$A$10)) ,ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$1 00,ROW($A$1:$A$10)),ROW($A1)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dim" wrote in message ... Hi, this one is a doosie! If anyone can help it'd be really great. I have three columns of data....A1:A10, B1:B10 and C1:C5. Column A is Numeric Data in each cell, Column B contains text in each cell, and I want my function to work on column C. I want the fuction to check down along column A, from A1, until it finds a value greater than zero, at which time it will copy that A cell's corresponding B cell into C1. (e.g: =IF(A10,B1) ) However, if at any time a value is entered into C1, then I want the function to copy the next value it finds into C2 instead, and so on into C3 etc. With this sheet, column B has about 100 text entries, but only 10 or so of those entries corresponding numbers in column A will be greater than 0. I want all those text entries organised at the top of column C for easy referance, so putting a seperate function into each of 100 column C cells is not an option. Does anyone have any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
|
IF - THEN - OTHERWISE -......UNLESS! ???
Believe Bob's array works, but you probably didn't array-enter it correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
Thanks Don and Max,
I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
But it's a macro, so it doesn't recalculate if the data changes.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don Guillett" wrote in message ... The macro approach is much less overhead for your file -- Don Guillett Microsoft MVP Excel SalesAid Software "dim" wrote in message ... Thanks Don and Max, I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
could
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bob Phillips" wrote in message ... But it's a macro, so it doesn't recalculate if the data changes. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don Guillett" wrote in message ... The macro approach is much less overhead for your file -- Don Guillett Microsoft MVP Excel SalesAid Software "dim" wrote in message ... Thanks Don and Max, I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
Hi again,
I was using Bob's array formula fine in my test sheet, but now I've opened a new file and copied it acorss. I changed some of the values to referance the correct cells, and pressed ctrl-shift-enter to activate it, but its not working!!! :-( This is what I have in the cell and dragged down to the cells below: {=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))} Whats wrong? "Don Guillett" wrote: could -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob Phillips" wrote in message ... But it's a macro, so it doesn't recalculate if the data changes. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don Guillett" wrote in message ... The macro approach is much less overhead for your file -- Don Guillett Microsoft MVP Excel SalesAid Software "dim" wrote in message ... Thanks Don and Max, I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
dim,
my formula was specific to the data being in A1 etc. To be more versatile, use =IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15)) ,ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1),"",INDEX($B$6:$B$15,SMALL( IF($A$6:$A$150,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dim" wrote in message ... Hi again, I was using Bob's array formula fine in my test sheet, but now I've opened a new file and copied it acorss. I changed some of the values to referance the correct cells, and pressed ctrl-shift-enter to activate it, but its not working!!! :-( This is what I have in the cell and dragged down to the cells below: {=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))} Whats wrong? "Don Guillett" wrote: could -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob Phillips" wrote in message ... But it's a macro, so it doesn't recalculate if the data changes. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don Guillett" wrote in message ... The macro approach is much less overhead for your file -- Don Guillett Microsoft MVP Excel SalesAid Software "dim" wrote in message ... Thanks Don and Max, I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
IF - THEN - OTHERWISE -......UNLESS! ???
GREAT! :-D Thanks a million Bob, that's working perfect now.
"Bob Phillips" wrote: dim, my formula was specific to the data being in A1 etc. To be more versatile, use =IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15)) ,ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1),"",INDEX($B$6:$B$15,SMALL( IF($A$6:$A$150,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "dim" wrote in message ... Hi again, I was using Bob's array formula fine in my test sheet, but now I've opened a new file and copied it acorss. I changed some of the values to referance the correct cells, and pressed ctrl-shift-enter to activate it, but its not working!!! :-( This is what I have in the cell and dragged down to the cells below: {=IF(ISERROR(SMALL(IF($A$6:$A$150,ROW($A$6:$A$15) ),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$ 150,ROW($A$6:$A$15)),ROW($A6))))} Whats wrong? "Don Guillett" wrote: could -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob Phillips" wrote in message ... But it's a macro, so it doesn't recalculate if the data changes. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don Guillett" wrote in message ... The macro approach is much less overhead for your file -- Don Guillett Microsoft MVP Excel SalesAid Software "dim" wrote in message ... Thanks Don and Max, I didn't realise there could be so many ways to achieve the same result! :-) I read your two posts, and when Max said that Bob's array works, I went back and had a look at it. I had it working fine (I thought) but it just wasn't doing what I wanted. Anyway, I realised what the problem was....I entered his array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops! So I have a way to achieve what I wanted, but now Im going to try out your two ways to see how they work aswell. Thanks again. "Max" wrote: Believe Bob's array works, but you probably didn't array-enter it correctly. Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to confirm the formula. You should see curly braces { } wrapped around the formula by Excel (in the formula bar). If you don't see the curlies, then it hasn't been array-entered properly. You gotta try the CSE again. Once correctly array-entered, then just copy C1 down to C10. Anyway, here's an alternative play which also achieves it using non-array formulas In C1: =IF(ROW()COUNT(D:D),"",INDEX(B:B,SMALL(D:D,ROW()) )) In D1: =IF(A1="","",IF(A10,ROW(),"")) Select C1:D1, copy down to cover the max expected extent of data in col A, say down to D100? Hide away col D. Col C will return the required results from col B, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "dim" wrote: Thanks Bob but I've tried that and it doesn't exactly do what I want. Your formula takes the first value greater than zero that it finds, shows the corresponding text for that cell, and then it stops. After it shows the text for cell B1 because A1 was greater than zero, I need it to keep moving down the A column, and then the next time it finds a value greater than zero, to show that corresponding text in cell C2, then keep checking down and show the next one in cell C3 and so on. So at the end of it checking hundreds of values in column A, I might have seven or eight (Or three, or fifty etc) cells at the top of column C filled in with text from column B. I hope that makes sense.....any ideas? |
| All times are GMT +1. The time now is 07:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com