Pod cast



  • (assumption: The primay key (in your case, it is the telephone number) columnn are same on both sheets)

    • Merge the two sheets by copying one and pasting at the end of the other

    • Activate the developers tab on excel

    File >Options >Customize Ribbons [check Developer Check Box] >OK

    • Click Developer Tab > Macro Security > Enable all Macro..

    • Click Developer Tab > Visual Basic

    Paste the following code (between dashed lines) into blank Book1 - Sheet1 windows

    Note: the following code assume the duplicate identifying column (Primary key) is A. Change letter "A" in the following to column of your identifier if needed.


    Sub Delete_Duplicate()

    'This procedure deletes all dublicates, a duplicate marked XX in a specified columm is kept as compared to unmarked

    'Column O, Q, and U will be compared, Column N will be check before deleting

    Dim r As Long

    Dim r2 As Long

    Dim CurrentVal As String

    Dim DuplicateFound As Boolean

    For r = 1 To Range("A65536").End(xlUp).Row

    CurrentValo = Range("O" & r).Value

    CurrentValq = Range("Q" & r).Value

    CurrentValu = Range("U" & r).Value

    DuplicateFound = False

    For r2 = r + 1 To Range("A65536").End(xlUp).Row

    If Range("O" & r2).Value = CurrentValo And Range("Q" & r2).Value = CurrentValq And Range("U" & r2).Value = CurrentValu Then

    DuplicateFound = True

    If Range("N" & r2).Value <> "XX" Then

    Range("A" & r2).EntireRow.Delete

    End If

    r2 = r2 - 1

    End If

    Next r2

    If DuplicateFound = True And Range("N" & r).Value <> "XX" Then

    Range("A" & r).EntireRow.Delete

    r = r - 1

    End If

    Next r

    End Sub


    Press F5 key. You are done.

    • Click Developer Tab > Macro Security > Disable all Macro..

  • administrators

    This post is deleted!