Excel - How to compare data in two columns to find duplicates

[wwj]

How to compare data in two columns to find duplicates in Excel.

http://support.microsoft.com/kb/213367

Code: Select all

=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)

Method 2: Use a Visual Basic macro

Code: Select all

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub