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.


    Jack
    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

    Cells.Select

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

    Range("a1").Select
    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

    Else

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

    myCount = 1

    End If

    Loop

    ScreenUpdating = True

    c = ""
    myCount = ""

    myEnd:
    ' 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!

Register Yourself Login