How to fetch duplicate records from Excel sheet

Question

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

Answer 1

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")

Answer 2

The easiest way would be to use conditional formatting. Select all of your cells that you're looking for duplicates in, then select Conditional Formatting > Highlight Cells Rules > Duplicate Values. Click ok, so all duplicates will be highlighted with a light red. From there, you can use a filter to only show these values, or show all values that are not duplicates and delete them.