Search code examples
excelexcel-formulaexcel-2010spreadsheet

How to fetch duplicate records from Excel sheet

I have list of almost 20000 email addresses in Excel sheet. I am only interested in duplicate values. Can I somehow extract them.

Solution

  • If you are just looking to remove the duplicates completely you could open up VBA by pressing Alt + F11 and enter the following code in to a new macro:

    ActiveSheet.Range("A1:A20000").RemoveDuplicates Columns:=Array(1), Header:=xlNo

    This will remove all duplicates from the A column only, so if you need other (linked) columns removing too then you will need to adjust the code. Thankfully Microsoft provide reasonable support on this function here.

    Similarly you may need to adjust the range to match the amount of data in the list. I believe the range A:A should be sufficient for any length list, but I am unable to test it at the moment.

    Edit: Since adding this answer, Microsoft have rolled out many new functions as part of Microsoft 365 that make this kind of task significantly easier to complete with standard formulae. For example, you can now use the UNIQUE function to return the list of unique values. The help documentation is here, but the formula would look something like this:

    =UNIQUE("A1:A20000")