find duplicates in a list

Posted by Allen on March 20, 2001 9:47 AM

Is there a way to find duplicates in rows ( like names listed in a column of 1500 )

Thank You

Posted by Ian on March 20, 2001 9:53 AM

Do you want to identify duplicates or delete duplicates?

Posted by Allen on March 20, 2001 10:48 AM

to identify duplicates

Posted by Ian on March 20, 2001 12:17 PM

Re: to identify duplicates

I was hoping you were going to say to eliminate them. But one way (and certainly not the best) would be simply to sort the data. You can then add a column next to it with an if formula at the second value such as:

=IF(A3=A2,1,0)

If you copy this down, it would identify duplicates with a one.

I'd check back as I'm sure a much simplier and better way will be posted.

Good luck

Posted by cpod on March 20, 2001 12:30 PM

Re: to identify duplicates

If your list is in column A then copy this down in an empty column:

=SUM(IF($A$1:A1=A2,1,0))

This will count the number of duplications in your list, excluding the first instance.

This is an array function and must be entered using Control+Shift+Enter

You could also use this as a conditional format for column A.

Posted by Dave Hawley on March 20, 2001 2:29 PM

Re: to identify duplicates

If you follow my link to my website then click the "Handy Hints" link, you will see a few ways to deal with duplicates.

Dave

OzGrid Business Applications

Posted by Mo on March 20, 2001 4:17 PM

Re: to identify duplicates

Hello,
Assume your data is in A1 to A1000.
In B1 array enter this formula
=countif(A1:A1000,A1:A1000)
To array enter, press control + shift + enter.
Then proceed to fill down this formula to B1000.
Hope it works

Posted by Mo on March 20, 2001 4:22 PM

Forgot to mention

Forgot to mention , all the unique ones will result in a 1 being displayed, the duplicates will be greater than one. to view just the unique ones, auto filter column B.