find a cells from a range of cell
Assuming A1:A20 contains "YES" in them.
Except A9 & A16 contain "NO". B1:B20 is suppose to find only the "YES" value in A:A column. Result should be in running sequence with no blank in between B:B column. Which is to say B1:B18 return the "YES" TEXT but B19:B20 return "NO". Need them to jump together in sequence in [B:B] when any rows in [A:A] is invalid. Q: Wondering can we made the cell B1 intelligent enough to find the first "YES" for the range in [A:A], B2 to find the second "YES", B3 to find the third "YES" and so forth. Is there a formula to accomplished this? Pls advise. Thanks. |
Can't you just sort the range?
This formula will place all the yes first when copied down but it will return an error for No =INDEX($A$1:$A$20,SMALL(IF($A$1:$A$20="Yes",ROW($A $1:$A$20)),ROW(1:1))) entered with ctrl + shift & enter, copy down until you get a NUM error if indeed the only 2 options are yes and no you can use this =IF(ISERR(SMALL(IF($A$1:$A$20="Yes",ROW($A$1:$A$20 )),ROW(1:1))),"No",INDEX($A$1:$A$20,SMALL(IF($A$1: $A$20="Yes",ROW($A$1:$A$20)),ROW(1:1)))) entered the same way I would personally use autofilter and copy and paste -- Regards, Peo Sjoblom "kelvintaycc" wrote in message ... Assuming A1:A20 contains "YES" in them. Except A9 & A16 contain "NO". B1:B20 is suppose to find only the "YES" value in A:A column. Result should be in running sequence with no blank in between B:B column. Which is to say B1:B18 return the "YES" TEXT but B19:B20 return "NO". Need them to jump together in sequence in [B:B] when any rows in [A:A] is invalid. Q: Wondering can we made the cell B1 intelligent enough to find the first "YES" for the range in [A:A], B2 to find the second "YES", B3 to find the third "YES" and so forth. Is there a formula to accomplished this? Pls advise. Thanks. |
On Sat, 2 Apr 2005 07:43:02 -0800, "kelvintaycc"
wrote: Assuming A1:A20 contains "YES" in them. Except A9 & A16 contain "NO". B1:B20 is suppose to find only the "YES" value in A:A column. Result should be in running sequence with no blank in between B:B column. Which is to say B1:B18 return the "YES" TEXT but B19:B20 return "NO". Need them to jump together in sequence in [B:B] when any rows in [A:A] is invalid. Q: Wondering can we made the cell B1 intelligent enough to find the first "YES" for the range in [A:A], B2 to find the second "YES", B3 to find the third "YES" and so forth. Is there a formula to accomplished this? Pls advise. Thanks. Although I don't really understand the purpose of this, here is a solution to your problem as it is stated. In cells B1:B20 enter the following formula: =IF(COUNTIF($A$1:$A$20;"YES")=ROW();"YES";"NO") Hope this helps |
All times are GMT +1. The time now is 09:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com