Excel Macro To Compare Two Columns (4 Easy Ways)

Trang chủ » Visual Basic Excel Compare Cell Values » Excel Macro To Compare Two Columns (4 Easy Ways)

Có thể bạn quan tâm

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

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

Sample Dataset

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

Using VBA 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

Using VBA Macro to Highlight Unique Values Comparing Two Columns

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.

Using VBA Macro to Highlight Unique Values Comparing Two Columns

  • 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.

Using VBA to Highlight Unique Values Comparing Two Columns

  • Run the Macro again to compare Addresses.

Using VBA Macro to Highlight Unique Values Comparing Two Columns

Unique values are Highlighted.

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

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.

 

Using VBA Macro to Find Matches Comparing Two Columns

  • 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

Using VBA to Find Matches Comparing Two Columns

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.

Using VBA Macro to Find Matches Comparing Two Columns

  • 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.

Using VBA Macro to Extract Unique Values From Two Columns

  • 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

Using VBA Macro to Extract Unique Values From Two Columns

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

Using VBA to Extract Unique Values From Two Columns

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.

Using VBA Macro to Extract Unique Values From Two Columns

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

Using VBA 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

Using VBA Macro to Highlight Duplicate Values From Different Sheets

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.

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

A dialog box is displayed.

Using VBA Macro to Highlight Duplicate Values From Different Sheets

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

It will highlight all duplicate values in the Email_ID column of both sheets.

The highlighted duplicate values in sheet1.

Using VBA Macro to Highlight Duplicate Values From Different Sheets

The highlighted duplicate values in sheet2.

Practice Section

Practice here.

Download To Practice

Excel Macro to Compare Two Columns.xlsm

<< Go Back to Columns | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Từ khóa » Visual Basic Excel Compare Cell Values