How to Compare Data in Two Columns to Find Duplicates in Excel

When we use a worksheet, sometimes we need to compare two columns and find the duplicate values.

For example, I have two long columns of student names, and now, I want to compare them and find out the same names. If I compare them cell by cell, it will be tiresome and waste a lot of time.

So... for those of you who have ever needed to find the difference between two columns, this is for you.

Look at the following screenshot, I have column A (A2:A15) and column C (C2:C15) which contain some duplicate names.

The are 2 methods that can be used to compare two columns and display the duplicate values.

METHOD 1: (Use of a Worksheet Formula) Please do with the following steps:

1. In cell B2, input this formula: =IF(ISERROR(MATCH(A2,$C$2:$C$15,0)),"",A2).

Note: In the above formula: A2 is the column which you want to be compared. $C$2:$C$15 is the range that you want to be compared with. You can change the variables for what you are using.

2. Press the ENTER key. Select cell B2, and then drag the fill handle over cell B15. And all of the duplicate names will be displayed in column B. See screenshot:

Download: Sample (Method 1) 2 Column Duplicate Sheet

METHOD 2: (Use of a Visual Basic Macro) Please do with the following steps:

1. Start Excel with your data in it.

2. Press ALT+F11 to start the Visual Basic Editor (see image below).

3. On the Insert menu, click Module.

4. Enter the following code in a module sheet:

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("C2:C15")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Simpleng Tips"). _
    '   Worksheets("Sheet2").Range("C2:C15")
    '
    ' 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

5. Press ALT+F11 to return to Excel.

6. Using our data above, select the range A2:A15.

7. In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Macros.

In Excel 2007, click the Developer tab, and then click Macro in the Code group.

8. Click Find_Matches, and then click Run.

The duplicate numbers are displayed in Column B. The matching data will be put next to the first column.

Download: Sample (Method 2) 2 Column Duplicate Sheet

If this helped you then please give a thumbs up, or leave a comment, or link back to this blog.

Thanks!

Simpleng Tips