View Duplicate entries

[Oscar]

What would I need to do to view duplicate entries. I have a mailing list, that i get new data in excel every week Id like to view duplicates on a separate sheet so I wont stain my eyes looking thru thousands of entries any help is appreciated.

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[oscar]

I tried using the duplicate entry but all I get is #NAME? =IF(COUNTIF(Range1,???)>1,"Duplicate","") I changed Range1 to my column header address and ??? to H2 which was my first entry. I also tried the highlight method but nothing highlighted
=IF(COUNTIF(ADDRESS, H2)>1,TRUE,FALSE)
What am I doing wrong.

 

[Frank Kabel]

Hi
you sould use countif like the following
=IF(COUNTIF($A$1:$A$1000,A1)>1,"Duplicate","")
if your data is stored in column A

 

[Oscar]

It works now i didnt put a label on my address at first Im not even sure if that is the right term to use. Man youve been a great help let me ask you this also if you dont mind.

Like I mentioned I get data every week about a hundred or so. One of my colums is an import date. Is there a way for me, I d probably have to change the formula weekly, To only show the duplicates from the newest imported list compared to the old not all the duplicates?
Thanks

 

[Frank Kabel]

Hi
if column A stores the date and column B the values to check for
duplicates try the following
- select column B
- goto conditional format and enter the following formula
=SUMPRODUCT(($A$1:$A$1000<$A1)*($B$1:$B$1000=$B1))
- choose a format

This will highlight cells in column B if there is the same entry prior
to the date in the corresponding column A

 

[oscar]

Thanks but I was able to find what I was looking for just by sorting by the import date
Let me ask you this, Is it possible to add a CHECK BOX, I dont mail out to everyone, so that the ones that are checked i can create mailing labels for, probably not huh.

 

[Andy Brown]

Response in worksheet.functions.

Thanks but I was able to find what I was looking for just by sorting by the import date.
Let me ask you this, Is it possible to add a CHECK BOX, I dont mail out to

everyone, so that the ones that are checked i can create mailing labels for,
probably not huh.

 

[Ruan]

Frank,

That formula work great, but when you are tagging duplicates where the range
has formulas in them, then the formula ends up getting tagged as a duplicate
and not the result. Any ideas how I can get around that issues?

Ruan

 

[Frank Kabel]

Hi
it should only tag them as duploicates if they return the same values

 

[Ruan]

Hello Frank,

Sometimes my formulas will return a blank value.

Example - I have the following formula on Sheet 2 in a column that has 400
rows.
=IF(DMC_SSN="","",DMC_SSN)

DMC_SSN is the range name on Sheet 1

So, what is happening is that it is tagging all my blanks

Ruan

 

[Frank Kabel]

Hi
try
=IF(AND(COUNTIF($A$1:$A$1000,A1)>1,A1<>""),"Duplicate","")