Excel Magic Trick 432: Find & Extract Duplicate Records

See how to use the COUNTIF function, Sort, Advanced Filter Extract Unique Records to find and extract duplicate records from a data set.

Category:

Science & Technology

Tags:

• Excel
• COUNTIF
• function
• Sort
• Advanced
• Filter
• Extract
• Unique
• Records
• to
• find
• and
• extract
• duplicate
• records
• from
• data
• set
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[7SevenDeuce2]

This is a great trick, I love it!

However, I have a sheet that has, Last Name, First Name, Street Number, Street Name.

I need to only find the duplicates that have the same Street Number and Street Name. This trick here doesn't work for it, maybe I'm missing something.

Thanks.

[ExcelIsFun]

Are street number and name in different columns (cells) or the same column (cell)? Each would have a different method for extracting the duplicates.

[ExcelIsFun]

If the street number and name are in the same cell, this trick should work.

If they are not, we can concatenate using the &, and then do this trick.

[ExcelIsFun]

If they are not, we can concatenate using the &, and then do this trick, but instead of COUNTIF, we would probably have to use a SUMPRODUCT forumla for counting. Something like this:

=SUMPRODUCT(--($B$14:$B$22&$C$­14:$C$22=B14&C14))

[egiftwarehousedotca]

Thank you so much I downloaded your workbook 432 it has helped me so much THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!­!!

[thuynguyenable]

This video is great. I am just wondering this trick perfectly works when you want to delete both the original data and its duplicate. It does not work when you want to keep the original data and only delete its duplicate. Do I understand this correctly?

In column B, I have:

1234

5678

1569

1234

This trick will delete both 1234 in the first cell and 1234 in the last cell too. Only 5678 & 1569 remain in the date base. What I want is keeping 1234 and delete the other 1234. Please advise.

[7SevenDeuce2]

The SumProduct worked Perfectly, a little taxing on the processor, even with 8Gigs Ram and 4 Cores.

I also saw another video about using CONCATENATE to join the two cells. =CONCATENATE(G1," ",H1) and then using your COUNTIF to double check it.

Thanks brother, you're a lifesaver!