Delete or Merge duplicate row entries

  • I was running a report from another program to an excel spreadsheet. In doing so, I've noticed multiple duplicate entries. I have nearly 5000 lines to go through.

    Does Excel recognize duplicate entries and allow them to be deleted or merged in one fell swoop? If so, please tell me how.

    Thanks! :)

  • Hi T,

    Welcome to the board :)

    Have you considered using the Advanced Filter? Go to the Data menu, Filter, Advanced Filter and check the 'Unique Records Only' option,

    As always when deleting data - make sure you have a backup first!

  • Two add to the advice from Richie

    A working strategy

    Back up copy first
    Conditional format to highlight dups colour fill red
    Check them off to see what you getting
    Delete as needed, transfer, edit, copy ????

    I like always to data test before delete just a good tip.

    I run code against the data and sort of ease, as a copy I can pull in copy and re save back up and run delete so I have original in tacked.

    BTW Sorry to over push the back up bit, I know many gurus mess up on this one, even Jack in recent weeks have messed up on back ups sometimes not Excel related, I cant stress enough.

    MARKER – BACK UP [feed marked for recall under search]

    Code: To sort and fill red is = hit [match]

    <font face=Courier New&gt;<SPAN style="color:darkblue"&gt;Sub</SPAN&gt; JackintheUK_Find_Duplicate_Rows_RED()<br&gt;<SPAN style="color:darkblue"&gt;Dim</SPAN&gt; myCount <SPAN style="color:darkblue"&gt;As</SPAN&gt; <SPAN style="color:darkblue"&gt;Long</SPAN&gt; <SPAN style="color:green"&gt;'Integer</SPAN&gt;<br&gt;ScreenUpdating = <SPAN style="color:darkblue"&gt;False</SPAN&gt;<br&gt;c = ActiveCell.Value<br&gt;myCount = 1<br&gt;<SPAN style="color:darkblue"&gt;On</SPAN&gt; <SPAN style="color:darkblue"&gt;Error</SPAN&gt; <SPAN style="color:darkblue"&gt;Resume</SPAN&gt; <SPAN style="color:darkblue"&gt;Next</SPAN&gt;<br&gt;<SPAN style="color:green"&gt;' just in case</SPAN&gt;<br&gt;Cells.<SPAN style="color:darkblue"&gt;Select</SPAN&gt;<br&gt;Selection.Sort Key1:=Range("A1"), _<br&gt;Order1:=xlAscending, _<br&gt;Header:=xlGuess, _<br&gt;OrderCustom:=1, _<br&gt;MatchCase:=False, _<br&gt;Orientation:=xlTopToBottom<br&gt;Range("a1").<SPAN style="color:darkblue"&gt;Select</SPAN&gt;<br&gt;i = ActiveCell.Offset(1, 0).Value<br&gt;<SPAN style="color:darkblue"&gt;Do</SPAN&gt; <SPAN style="color:darkblue"&gt;While</SPAN&gt; ActiveCell <> ""<br&gt;<SPAN style="color:darkblue"&gt;If</SPAN&gt; c = i <SPAN style="color:darkblue"&gt;Then</SPAN&gt;<br&gt;ActiveCell.Offset(myCount, 0).Interior.Color = RGB(255, 0, 0)<br&gt;myCount = myCount + 1<br&gt;i = ActiveCell.Offset(myCount, 0).Value<br&gt;<SPAN style="color:darkblue"&gt;Else</SPAN&gt;<br&gt;ActiveCell.Offset(myCount, 0).<SPAN style="color:darkblue"&gt;Select</SPAN&gt;<br&gt;c = ActiveCell.Value<br&gt;i = ActiveCell.Offset(1, 0).Value<br&gt;myCount = 1<br&gt;<SPAN style="color:darkblue"&gt;End</SPAN&gt; <SPAN style="color:darkblue"&gt;If</SPAN&gt;<br&gt;<SPAN style="color:darkblue"&gt;Loop</SPAN&gt;<br&gt;ScreenUpdating = <SPAN style="color:darkblue"&gt;True</SPAN&gt;<br&gt;c = ""<br&gt;myCount = ""<br&gt;myEnd:<br&gt;<SPAN style="color:green"&gt;' some code - jituk</SPAN&gt;<br&gt;<SPAN style="color:darkblue"&gt;Exit</SPAN&gt; <SPAN style="color:darkblue"&gt;Sub</SPAN&gt;<br&gt;<SPAN style="color:darkblue"&gt;End</SPAN&gt; <SPAN style="color:darkblue"&gt;Sub</SPAN&gt;<br&gt;<br&gt;<br&gt;</FONT&gt;

  • Error in code ???

    Try Again

    Sub[/color] JackintheUK_Find_Duplicate_Rows_RED()

    Dim myCount As Long 'Integer

    ScreenUpdating = False
    c = ActiveCell.Value

    myCount = 1

    On Error Resume Next
    ' just in case


    Selection.Sort Key1:=Range("A1"), _
    Order1:=xlAscending, _
    Header:=xlGuess, _
    OrderCustom:=1, _
    MatchCase:=False, _

    i = ActiveCell.Offset(1, 0).Value

    Do While ActiveCell <> ""

    If c = i Then
    ActiveCell.Offset(myCount, 0).Interior.Color = RGB(255, 0, 0)
    myCount = myCount + 1
    i = ActiveCell.Offset(myCount, 0).Value


    ActiveCell.Offset(myCount, 0).Select
    c = ActiveCell.Value
    i = ActiveCell.Offset(1, 0).Value

    myCount = 1

    End If


    ScreenUpdating = True

    c = ""
    myCount = ""

    ' some code - jituk
    Exit Sub

    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!