This ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just
Enter) ) returns the values you posted:
C1:
=MAX(SUM(--(IF(($A$1:$A$7&$B$1:$B$7=A1&B1),MATCH(A1&B1,$A$1:$ A$7&$B$1:$B$7,0),0)=ROW()))-1,0)
Copy C1 and paste into C2:C7
Is that something you can work with?
--------------------------
Regards,
Ron (XL2003, Win XP)
Microsoft MVP (Excel)
"saman110 via OfficeKB.com" <u35670@uwe wrote in message
news:78d13efce5a84@uwe...
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