Highlight Duplicates in 1 column on large data set

[Nieriel]

Hi All,
I am trying to highlight all duplicates within a single column (E) in Excel on a large set of data (nearly 300k rows) with columns going A-Q, my headers are in Row 1 and data starts in Row 2
I have tried to use Conditional Formatting, this works to highlight the duplicates but when I try and filter or sort it just crashes excel.
I have also found a macro online but this only highlights 1 of the duplicate values, I need both as I then need to move the entire row with the duplicates to a different sheet or workbook.
Everything I find seems to crash the spreadsheet and I end up having to close excel and start again.

Any help would be appreciated
Thanks

 

[SteveO59L]

in a helper column

=COUNTIF(E:E,E2)

then filter for values >1

 

[Fluff]

This will copy the duplicates to a sheet called test2 & then delete them from the original

Sub CopyDupes()

   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("L2", Range("L" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl
         Else
            If Rng Is Nothing Then
               Set Rng = Union(.Item(Cl.Value), Cl)
            Else
               Set Rng = Union(.Item(Cl.Value), Cl, Rng)
            End If
         End If
      Next Cl
   End With
   Rng.EntireRow.Copy Sheets("Test2").Range("A2")
  [COLOR=#0000ff] Rng.EntireRow.Delete[/COLOR]
End Sub

If you don't want them deleted, just remove the line in blue

 

[Nieriel]

Thanks both - the formula unfortunately won't work as when I copy it down all the rows it crashes due to the volume of data but the Macro seems to be working fine.
Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback