Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter. So,
I tried to modify your suggested formula. After revised your formula, it
should have two functions as follow:
1) Detect any €œOK€� in column F;
2) Once able to identify €œOK€� which refer to €œ12€�, the array formula will
base on the below database show €œO€� in the cell G1.

The desire result is show below (of course, I failed to combine both
functions in your array formula).
RESULT:-
..........E..........F..........G
1.......11......... - ......... O
2.......12........OK........ -
3.......13......... - ......... -
4.......14......... - ......... -

The revised formula is placed in G1 and drag it to G4. Please note that the
revised formula is not necessary place in G1 and correspondent to column F,
that is why I want to put an array formula.

DATABASE:-
€œO€� refers to €œ11€�; €œE€� refers to €œ12€�; etc.
€¦.............E............F
21..€¦€¦...11....€¦€¦O
22€¦€¦€¦.12€¦.€¦€¦E
23€¦€¦€¦.13€¦€¦€¦O
24€¦€¦€¦.14€¦€¦€¦E
25€¦€¦€¦.15€¦€¦€¦O
26€¦€¦€¦.16€¦€¦€¦E
27€¦€¦€¦.17€¦€¦€¦O
28€¦€¦€¦.18€¦€¦€¦E
29€¦€¦€¦.19€¦€¦€¦O
30€¦€¦....20€¦....€¦E

Once again, many thanks for your advice,
Wilchong







T. Valko wrote:
OK, I think I uderstand what you want.

I would put all the table data in a contiguous range like this:

..........E..........F..........G
1.......11.........T..........-
2.......12.........G........OK
3.......13.........R..........-
4.......14.........E..........-

Then:

Formula in H1:

=COUNTIF(G1:G10,"OK")

Then this array formula** in H2 and copied down:

=IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G $1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$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.

Dear T. Valko,
Thanks, your suggested function work very well. With your suggested
[quoted text clipped - 30 lines]
Thanks for your advice,
Wilchong

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