Count Duplicates
 

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

Count Duplicates
 

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

Count Duplicates
 

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

Count Duplicates
 

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

Count Duplicates
 

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" wrote in message
...
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

Count Duplicates
 

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