Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMA LL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)



--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:83709ae42b123@uwe...
Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed
to
work. May be this is my problem of understanding the formula.

In your note, it indicates that A3:A14 = rng1 & B3:B13 = rng2. As a
result,
in the function of COUNTIF(rng2,rng1), I convert it to COUNTIF(B3:B13,A3),
am
I right? Please help me on this!

The second question is the function of ROW(rng1), I converted it to
ROW(A3:
A14), am I right?

Many thanks for your advice!
Wilchong




T. Valko wrote:
Try this:

A3:A14 = rng1
B3:B13 = rng2

Enter this array formula** in C3 and copy down until you get blanks:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTI F(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

There are 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L
[quoted text clipped - 11 lines]
Many thanks,
Wilchong

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200804/1