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