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" <[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> 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