find a cells from a range of cell

[kelvintaycc]

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.

 

[Peo Sjoblom]

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

 

[Lars-Åke Aspelin]

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