Dear Max,

Many thanks for your advice and suggested formula, it works very well.
However, I found one thing which I don't really understand. The suggested
formula will give me the result as : 1, 12 & 0 after I moved the whole series
of data (20, 7, 1, 12 and 8) one cell downward (it means the series data
starting from F2). After I study the formular, I found that the result back
to 7, 1 & 8 after I revised the range in the formular ROWS starting from 1.
I don't understand why it will happen? Is that any way I can revise the
suggested formula even though I move the set data from F1 to F10?

Many thanks,
Wilchong



Max wrote:
Try this in M1, array-entered:
=IF(ROWS($1:1)SUM(COUNTIF(A$1:A$10,F$1:F$5)),"", INDEX(F$1:F$5,SMALL(IF(COUNTIF(A$1:A$10,F$1:F$5),R OW(F$1:F$5)),ROWS($1:1))))
Copy down to M5.
Dear sir,

[quoted text clipped - 22 lines]
Many thanks,
Wilchong

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