Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

We use COUNTIF to identify which items are missing from column B.

INDEX(A$3:A$14,SMALL(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)))

The COUNTIF function returns an array of counts like this:

COUNTIF(B$3:B$13,A3) = 1
COUNTIF(B$3:B$13,A4) = 1
COUNTIF(B$3:B$13,A5) = 2
COUNTIF(B$3:B$13,A6) = 0
COUNTIF(B$3:B$13,A7) = 0
etc
etc

Where the result of that count is 0 we use this expression to calculate what
row the corresponding value is located on.

ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1

That number is then passed to the INDEX function to produce the desired
result.


--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:838837beccb0a@uwe...
Dear Biff,
Thank you so much for your Excel demo file. I finally found out what
wrong
with me! It is because my insufficient knowledge regarding the function
"enter" procedure. The reason I did wrong is because I highlighed the
whole
cell range from C3 to C14 and press CTRL, SHIFT and ENTER at the same
time.
Before, it is the only way I know. Right now, I know CTRL, SHIFT and
ENTER
can enter in one cell first and copy!

Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

I am so appreciate your effort, time and patient!
So thankful!
Wilchong



T. Valko wrote:
Here's a small sample file that demonstrates this:

xExtractMissing.xls 15kb

http://cjoint.com/?fcepu2wnPz

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the
[quoted text clipped - 18 lines]
Many thanks,
Wilchong

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