Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kelvintaycc
 
Posts: n/a
Default 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.
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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.



  #3   Report Post  
Lars-Åke Aspelin
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Eliminate empty cells in data range Stephen Excel Discussion (Misc queries) 1 April 2nd 05 04:00 AM
Formula to return ADDRESS of cell in range that meets criteria Christie Excel Worksheet Functions 1 March 5th 05 12:13 AM
Convert data type of cells to Text,Number,Date and Time Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"