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)

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:836e39f005b4d@uwe...
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
In B3 down is: B, A, H, C, F, I, K, J, H, C and J.

The result which I want is to list or select the data which is NOT exist
in
the second set of data (Col B) when compare to the first set of data.

In my example, the result is D, E, G, and L actomactic appear in cell C3,
C4,
C5 and C6 accordingly.
I need an Excel formula to have this result!

Many thanks,
Wilchong

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