2

I have about 80,000 emails in one column of MS Excel 2010.

Some of them are duplicates and I need to find and pull those duplicates. I want the duplicates in a separate document.

Right now I can find them and highlight them in red using the Conditional Formatting function that's built right into Excel (Home tab > conditional formatting > Highlight Cell Rules > Duplicate Values) but that literally just highlights them.

There's also Remove Duplicates, which obviously just deletes them; Not what I want either.

Any suggestions?

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
ash
  • 187
  • 1
  • 6
  • 13
  • 1
    Using VBA you can enter them into a dictionary. Have a look at the excellent answers to this question: http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array – mechanical_meat Apr 24 '13 at 16:09
  • Can you use a helper column? Should be easy to do this with a helper column, and autofilter. – David Zemens Apr 24 '13 at 16:11
  • I'll give that a look, but I was told that VBA runs extremely slowly, especially for the amount I want it to sift through. Thanks though! – ash Apr 24 '13 at 16:33
  • 1
    Highlight the duplicates as you describe above. Go to Data->Sort->Sort On->Cell Color. Copy/paste those out to another column or whatever. So easy. – Mark Bubel Aug 15 '13 at 18:44

1 Answers1

7

Here is a pretty easy way to do it:

=IF(COUNTIF($A$1:$A$15,A1)>1,A1,"")

Example1 Autofill that down column B.

And then just do a remove dulicates on column B: Example2

And then you can sort any cell in column B with values to the top. Good Luck.

Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • This formula is exactly the lines I am working with, however, could this formula be modified so that in column B it concatenates the duplicate value it matches too? In my sheet the some of the duplicates have a few extra zeros in front of the value it is matching and I would like to be able to reference in the same cell the duplicate it is matching – VikingScript Aug 26 '19 at 12:18