All,

Thank you for responding.


My result in the ex. returns 0 because I want to know what city has been
repeated for how many times. If I get others to show me the repeated dups, I
would get confused and won't know which one to delete later. This is Ok if I
could get the formula right to do it your way, but when I use your formula
all my entries returns 1 even those who has not been repeated.

ShaneDevenshire wrote:
Hi Saman,

Your sample data does not match your description. For example, the second
time that San Ramone CA shows up it is a duplicate and should show 2 off to
the right of it, but your sample shows 0.

If what you want to do is mark ALL duplicates then this formula will work in
column C:

=SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1

The same issue arrises when you want to mark the duplicates - do you mean
ALL rows that appear more than once or do you mean all occurances after the
first one?

To marks ALL rows that contain duplicates you can set up conditional
formatting such as

Formula Is , =$C10

Select the entire range first with the active cell on row 1 and then choose
Format, Conditional Formatting.

Hello,

[quoted text clipped - 28 lines]

thx.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200709/1