Dear Biff,
Many thank for your explanation. Actually, another point of the Excel
function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)),
INDEX(€¦€¦€¦€¦€¦!

What is the purpose of "--" if mix with Excel function!

Thank for your time and effort again!
Wilchong





T. Valko wrote:
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.

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what
[quoted text clipped - 28 lines]
Many thanks,
Wilchong

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