Hi,

here is a formula that will return the answers you are showing:

=IF(AND(SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-10,SUMPRODUCT(--(A1&B1=$A$1:A1&$B$1:B1))-1=0),SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1,0)
--
Thanks,
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