How to quickly find duplicate data in Excel

Quickly find and delete duplicate data in Excel with this handy function. (formula) While there are many ways to find duplicate data this is the fastest and most efficient, in my opinion. Make sure to follow the tip on your header row or you will have erroneous results.

Category:

Science & Technology

Tags:

• excel
• duplicates
• find
• data
• how to
• How-to
• Microsoft Excel
• Software Tutorial

[egiftwarehousedotca]

I am using excel 2007 And I copied everything you did i added the suppliers item numbers then my website item numbers to the same column then I followed your video I get Not Duplicate down the entire formula column  even knowing there are duplicate item numbers....is there something I am doing wrong what about values or something

[YVAmmangen]

@egiftwarehousedotca It should def work ~ how are your numbers formatted? I've only ever done it with text strings, not numeric strings.

[MrRriche]

what if the duplicate is written in a different format like for example, i have JOHN, DOE and DOES JOHN, and JOHN, DOES SR.,,,all are the same people how would i highlight them all.

[YVAmmangen]

@MrRriche This will only pick up exact duplicates not similar ones (and I don't know how to do similar ones). I tried a plugin for Excel (that you purchase) once but it didn't work as expected. Sorry. L(

[nhusdable]

fantastic video, thank you very much! After duplicate filter, What command did you use to grab entire range to delete? approx at minute 3:20 from 4:12 minute video. thank you easy to understand instructions. : )

[YVAmmangen]

@nhusdable So sorry, I have no idea how I missed your question/comment. To highlight the entire range of rows, as I did in the video, click on the first "gray" box to the left of the first row and while holding down the shift key click on the gray box to the left of the last desired row. This will highlight the entire area. In this case I then deleted the rows - be careful when doing this because you could inadvertently delete out content you want to keep yet.

[egiftwarehousedotca]

@YVAmmangen Well copy to note pad save note pad then open excel and imported the files into excel...then I took another sheet with other numbers copied and pasted those item numbers directly underneath into the same column...I found a work book has it all worked into the sheet I can use to do exactly the same thing as the video all i do is copy and paste my text into column and it works...

[egiftwarehousedotca]

@YVAmmangen  I have tried it and it says that it is not valid formula...

[YVAmmangen]

@premiumproductions69 Not sure how I missed your comment. I'll have to try that one, much better to lock the range.

[YVAmmangen]

@egiftwarehousedotca Not sure I quite understand? Try this maybe~ from the comment below yours? =IF(MAX(COUNTIF($A$2:$A$27,A2)­­)>1,"Duplicate","Nope")

[egiftwarehousedotca]

I have done everything you did but it does not work i am not sure what I a doing wrong i copied and pasted text into an excel column 'numbers' this is how i will be taking inventory on our website I take our suppliers stock once I find the duplicate item numbers i upload to our site to remove the out of stock items....please help me figure this out what am i doing wrong I have watched the video so many times and tried it but it still is not working

[premiumproductions69]

Hi there. Love your video.

Just thought i might mention that:

=IF(MAX(COUNTIF($A$2:$A$27,A2)­)>1,"Duplicate","Nope")

might be slightly better.

It locks the range (doesn't move the range down), and only checks the item you are looking for (not the range)...if that makes sense.

Keep up the good work!