Excel Macro to Compare Two Columns (4 Easy Ways)

In data analysis, you may need to compare two columns in Excel. Comparing data is important for many organizations such as banks, corporate offices, e-commerce websites, etc to check the background of a customer depending on the provided name, address, or email. It helps to decide whether to do any business or not with a particular customer. In this article, I’m going to explain how to use Excel macro to compare two columns.

To make these methods clearer I’ve used a sample dataset of two banks having Full Name, Email_ID, and Address. I will use this dataset to check whether the same person does have accounts in both banks or not.

Excel Macro to Compare Two Columns: 4 Useful Ways

1. Using Macro to Highlight Unique Values Comparing Two Columns 

By using VBA Macro, you can Highlight the Unique values of two columns.
To do it I will take the same type of columns together.

Now, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

Next, it will open Microsoft Visual Basic for Applications.
From there, open Insert >> select Module.

A Module will open then type the following code in the opened 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

Here, I declared the Sub procedure Highlighting_Comparing_2Columns where Twocolumns is Range type and i is Integer type variables.

I used an IF statement to compare the values of the selected cell range, also I used a FOR loop to check all the rows.

Then, if the compared value is unique by row, then it will Highlight it with Yellow color as I used ColorIndex = 6.

Finally, Save the code and go back to the worksheet.

Then, select the cell range to apply the VBA
➤ Here, I selected the range B4:C10.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Now, from the Macro name select the Highlighting_Comparing_2Columns also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will Highlight all the unique values of two columns.

Again, you can run the Macro to compare Addresses.

Here both columns’ unique values are Highlighted.

Read More: Excel formula to compare two columns and return a value

2. Using Macro to Find Matches Comparing Two Columns

To compare two columns and to find matches you can use the VBA Macro as well.

Here, I want to find the matches of the Email_ID column.
To begin the procedure, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

Then, it will open Microsoft Visual Basic for Applications.
Now, open Insert >> select Module.

A Module will open then type the following code in the opened 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

Here, I declared the Sub procedure Find_Matches_Comaring2Columns where CompareRange, x, and y are Variant type variables.

Provided the cell range F5:F11 to Compare values.

I used an IF statement to compare whether the value of x and y is equal or not. Also used, FOR loops to check all values of Selection and CompareRange.

Then, if the compared value is equal then it will put the values in the adjacent column.

Finally, Save the code and go back to the worksheet.

Then, select the cell range to apply the VBA
➤ Here, I selected the range C5:C11.
Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Now, from the Macro name select the Find_Matches_Comaring2Columns also select the workbook within Macros in.
Finally, Run the selected Macro.

Therefore, it will put all the matched values of two selected columns in a new column named Matches.

3. Using Macro to Extract Unique Values From Two Columns

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

Here, I have introduced two extra columns named Unique Name and Unique Address.

To begin with, open the Developer tab >> select Visual Basic
You also can use the ALT + F11 keyboard to open the VBA editor.

Now, it will open Microsoft Visual Basic for Applications.
Then, open Insert >> select Module.

A Module will open then type the following code in the opened 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

Here, I declared the Sub procedure Extract_Uniques_2Columns where rngCell is Range type variable.

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

Used the FOR loop twice to compare two different types of values.

Finally, Save the code and go back to the worksheet.

Then, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Now, from the Macro name select the Extract_Uniques_2Columns also select the workbook within Macros in.
Finally, Run the selected Macro.

Thus, it will extract all the unique values of given column ranges.

4. Using Macro to Highlight Duplicate Values From Different Sheets

You also can Highlight the duplicate values from different sheets by comparing two columns while using Macro.

Here, I have taken two extra sheets to demonstrate the procedure.

Now, open the Developer tab >> select Visual Basic (you also can use ALT + F11).

Then, it will open Microsoft Visual Basic for Applications.
Next, open Insert >> select Module.

A Module will open then type the following code in the opened 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

Here, I declared the Sub procedure Highlight_Comapring_2sheetsColumn where Last_sheet1_Row and Last_sheet2_Row are Long type variables. Also, i, j are two Integer type variables.

Then used an IF statement to check whether the compared values are equal or not within nested FOR loops.

Then, if the compared value is equal or the same then it will Highlight the values of the Email_ID column.

Here, I used the rgbRed as Font.Color. If you want, you can use any color to Highlight values.

Finally, Save the code and go back to the worksheet.

Next, open the View tab >> from Macros >> select View Macros

➤ A dialog box will pop up.

Now, from the Macro name select the Highlight_Comparing_2sheetsColumn also select the workbook within Macros in.
Finally, Run the selected Macro.

Hence, it will Highlight all the duplicate values of the Email_ID column of both sheets.

Highlighted duplicate values of sheet1.

Highlighted duplicate values of sheet2.

Practice Section

I’ve given a practice sheet in the workbook to practice these explained ways to compare two columns. You can download it from the above.

Download To Practice

Conclusion

In this article, I’ve explained 4 easy and quick ways for Excel macro to compare two columns. These different ways will help you to compare two columns. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment below.

<< Go Back to Columns | Compare | Learn Excel