# Find Duplicates and remove the duplicate entry comparing two columns

1. ## Find Duplicates and remove the duplicate entry comparing two columns

Has been googling this all day long..can somebody help me with this
Looking for a formula to identify duplicate entry in a list of two column. Highlight/delete the duplicate entry and keep only the orginal one.
PFA a sample scenario, PS- A value is duplicate only if the both the case number and version number are same.

2. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

Try.

In G5 and copy down.

=IF(SUMPRODUCT((E5:E5=E5)*(\$F\$5:F5=F5))=1,"Unique","Duplicate")

Then in I5 put this ARRAY formula. Copy down and across.

=IFERROR(INDEX(E\$5:E\$11,SMALL(IF(\$G\$5:\$G\$11="Unique",ROW(E\$5:E\$11)-4),ROW(E1))),"")

3. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

Fotis1991
I think the formula requires a small tweaking coz im getting many entries as duplicate. which actually is not ? Can you jus go through the document attached along

4. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

Row 5 values are not the same as row 8 values. Are these?

This formula?

=IF(SUMPRODUCT((\$B\$5:\$B\$476=B5)*(\$C\$5:\$C\$476=C5))=1;"unique";"duplicate")

5. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

Fotis,

Thanks a lot. Sorry for the late reply.
The new formula is working fine. Is there a way i can say the first instance on a duplicate case number as unique and the second one as a duplicate. For eg in file name - 'Retrospikz duplicate V2.0' Row number 11 (B11-C11) and row number 267 (B267-C267)are duplicates.Row 11 should be mentioned as 'Unique' and Row 267 as a 'duplicate'

Thanks a lot once again.

6. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

=IF(SUMPRODUCT((\$B\$5:B5=B5)*(\$C\$5:C5=C5))=1,"unique","duplicate")

And pls don't reply 2-3 days later. I have to re-think what i did 2-3 days earlier and i spend time and thought for this

7. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

Thanks a lot. Its working wonders...!!
Once again, Im really sorry for the late reply.

8. ## Re: Find Duplicates and remove the duplicate entry comparing two columns

......................

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1