Try this array formula** entered in B1:

=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"<-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10<"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$1)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to B10.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:9b64d58adfe9c@uwe...
Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After
considering your suggestion, I have changed the database in order to make
the
thing simple.

One thing need your help, below is the database, I need a formula to
extract
all the data from A1 to A10.
............A
1.......TY
2.... -
3....ER
4....SX
5.... -
6.... -
7....SX
8....TY
9.... -
10......ER

The formula I wish to extract the data from A1 to A10 will show the result
as
follow: to list the data from B1 to B6:
............B
1...... TY
2... ER
3... SX
4... SX
5... TY
6.... ER
7.... -
8.... -
9.... -
10..... -


And again, from the previous experience you have shown me, I can use "=IF
(ROWS(B$1:B10)<=SUM(--(COUNTIF(..... " and then entered by "Shift +
Control
+ Enter". I also dragged the formula from B1 to B10. Of course, what I
tried
to do was failed. As a result, I need your advice.

Many thanks,
Wilchong








T. Valko wrote:
I don't see how O relates to 12 (OK)?

I thought you wanted the results that correspond to "OK"?

At this point I'm lost!

Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed,
[quoted text clipped - 26 lines]
Thanks for your advice,
Wilchong

--
Message posted via http://www.officekb.com