Count Duplicates

  • Thread starter Thread starter saman110 via OfficeKB.com
  • Start date Start date
S

saman110 via OfficeKB.com

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.
 
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 , =$C1>0

Select the entire range first with the active cell on row 1 and then choose
Format, Conditional Formatting.
 
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)
 
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))-1>0,SUMPRODUCT(--(A1&B1=$A$1:A1&$B$1:B1))-1=0),SUMPRODUCT(--(A1&B1=$A$1:$A$7&$B$1:$B$7))-1,0)
 
Here's a slightly longer (by 3 characters), but NON-array version:
C1:
=MAX(SUMPRODUCT(--((($A$1:$A$7&$B$1:$B$7=A1&B1)*MATCH(A1&B1,$A$1:$A$7&$B$1:$B$7,0))=ROW()))-1,0)
Copy that formula down through C7

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

Ron Coderre said:
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)
Click to expand...
 
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.
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 , =$C1>0

Select the entire range first with the active cell on row 1 and then choose
Format, Conditional Formatting.
Hello,
Click to expand...
[quoted text clipped - 28 lines]
thx.
Click to expand...
Click to expand...
 
Back
Top