Using an Excel Macro to Compare Two Columns - 4 Methods

The sample dataset showcases Bank data: Full Name, Email_ID, and Address.

To check whether the same person has accounts in both banks:

Method 1 – Using Macro to Highlight Unique Values Comparing Two Columns

Go to the Developer tab >> select Visual Basic
You an also press ALT + F11 to open the VBA editor.

In the Microsoft Visual Basic for Applications, select Insert >> choose Module.

Enter the following code in the Module.

Sub Highlighting_Comparing_2Columns()
    Dim Twocolumns As Range, i As Integer
    Set Twocolumns = Selection
    With Twocolumns
        For i = 1 To .Rows.Count
            If Not StrComp(.Cells(i, 1), .Cells(i, 2), vbBinaryCompare) = 0 Then
                Range(.Cells(i, 1), .Cells(i, 2)).Interior.ColorIndex = 6
            End If
        Next i
    End With
End Sub

The Sub procedure Highlighting_Comparing_2Columns was declared: Twocolumns as Range type and i as Integer type variables.

The IF statement compares the values in the selected range and a FOR loop checks all the rows.

If the compared value is unique in a row, it will be highlighted it in Yellow (ColorIndex = 6).

Save the code and go back to the worksheet.
Select the cell range to apply the VBA. Here, B4:C10.
Go to the View tab >> Macros >> select View Macros

A dialog box is displayed.

In Macro name select Highlighting_Comparing_2Columns
Select the workbook in Macros in.
Run the selected Macro.

It will Highlight all unique values in the two columns.

Run the Macro again to compare Addresses.

Unique values are Highlighted.

Method 2 – Using a Macro to Find Matches Comparing Two Columns

Go to the Developer tab >> select Visual Basic
You an also press ALT + F11 to open the VBA editor.

In the Microsoft Visual Basic for Applications, select Insert >> choose Module.

Enter the following code in the Module.

Sub Find_Matches_Comaring2Columns()
Dim CompareRange As Variant, x As Variant, y As Variant
Set CompareRange = Range("F5:F11")
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

The Sub procedure Find_Matches_Comaring2Columns was declared: CompareRange, x, and y as Variant type variables.

F5:F11 is used to compare values.

The IF statement checks whether the value of x and y is equal. FOR loops check all values in Selection and CompareRange.

If the compared value is equal, it will place the values in the adjacent column.

Save the code and go back to the worksheet.
Select the cell range to apply the VBA. Here, C5:C11.
Go to the View tab >> Macros >> select View Macros

A dialog box is displayed.

In Macro name select Find_Matches_Comaring2Columns.
Select the workbook in Macros in.
Run the selected Macro.

All matched values of the two selected columns are displayed in a new column: Matches.

Method 3 – Using a Macro to Extract Unique Values From Two Columns

Extract unique values by comparing two columns. In this section, I’ll show you how to do that using VBA macro.

Two columns were added: Unique Name and Unique Address.

Go to the Developer tab >> select Visual Basic
You an also press ALT + F11 to open the VBA editor.

In the Microsoft Visual Basic for Applications, select Insert >> choose Module.

Enter the following code in the Module.

Sub Extract_Uniques_2Columns()
    Dim rngCell As Range
    For Each rngCell In Range("B2:B8")
        If WorksheetFunction.CountIf(Range("F2:F8"), rngCell) = 0 Then
            Range("D" & Rows.Count).End(xlUp).Offset(1) = rngCell
        End If
    Next
    For Each rngCell In Range("C2:C8")
        If WorksheetFunction.CountIf(Range("G2:G8"), rngCell) = 0 Then
            Range("E" & Rows.Count).End(xlUp).Offset(1) = rngCell
        End If
    Next
End Sub

The Sub procedure Extract_Uniques_2Columns was declared: rngCell is the Range type variable.

To compare the values, an IF statement within the FOR loop was used.

The FOR loop is used twice to compare two different types of values.

Save the code and go back to the worksheet.
Go to the View tab >> Macros >> select View Macros

A dialog box is displayed.

In Macro name select Extract_Uniques_2Columns.
Select the workbook in Macros in.
Run the selected Macro.

It will extract all unique values in the column ranges.

Method 4 – Using a Macro to Highlight Duplicate Values From Different Sheets

Go to the Developer tab >> select Visual Basic
You an also press ALT + F11 to open the VBA editor.

In the Microsoft Visual Basic for Applications, select Insert >> choose Module.

Enter the following code in the Module.

Sub Highlight_Comapring_2sheetsColumn()
Dim Last_sheet1_Row As Long
Dim Last_sheet2_Row As Long
Dim i As Integer
Dim j As Integer
Last_sheet1_Row = Sheets("sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Last_sheet2_Row = Sheets("sheet2").Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To Last_sheet1_Row
  For j = 2 To Last_sheet2_Row
    If Sheets("sheet1").Cells(i, 2).Value = Sheets("sheet2").Cells(j, 2).Value And _
    Sheets("sheet1").Cells(i, 3).Value = Sheets("sheet2").Cells(j, 3).Value Then
      Sheets("sheet2").Cells(j, 3).Font.Color = rgbRed
      Sheets("sheet2").Cells(j, 3).Copy Sheets("sheet1").Cells(i, 3)
    End If
  Next j
Next i
End Sub

The Sub procedure Highlight_Comapring_2sheetsColumn was declared: Last_sheet1_Row and Last_sheet2_Row are Long type variables. i, j are two Integer type variables.

An IF statement was used to check whether the compared values are equal within nested FOR loops.

If the compared value is equal, it will highlight the values in the Email_ID column.

rgbRed was used as Font.Color.