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.

--
Cheers,
Shane Devenshire


"saman110 via OfficeKB.com" wrote:

Hello,

I have Col.A with name of the cities and Col.B with its state. They contain
duplicates. I'm looking for a way that matches col.A and B and look for
duplicates. col A and B shoulb be considered as one becase there may be a
same city name, but in different US state.
I want excel to count the duplicates and put the number of repeating cities
next to them in Col.C and highlight all duplicates.

ie.

A B C
Concord CA
San Ramon CA
Anaheim CA
Concord TX
San Ramon CA
Concord CA
San Ramon CA



Result.

A B C
Concord CA 1
San Ramon CA 2
Anaheim CA 0 <==Zero or nothing
Concord TX 0
San Ramon CA 0
Concord CA 0
San Ramon CA 0

thx.

--
Message posted via http://www.officekb.com