Compare Two Columns Excel VBA Macro

In a previous post we saw how to Compare Two Worksheets in Excel using VBA macros. In this post we see how to compare two columns with data. Each column is treated as a list of items, and the goal is to compare which items or values are not in common. Thus, the macro compares the two lists or columns in the active sheet, and finds the discrepancies between the two. In this macro example, the discrepancies are highlighted in yellow and also added to another sheet. The lists are supposed to have unique entries, but the macro also works is entries are repeated. In that case though, all of them will be highlighted and also added to the other sheet, thus having repeated terms there. The macro can be easily tweaked to avoid that and show only unique entries if used to compare two columns or lists with repeated entries.

Macro/VBA code:

 

  Sub CompareTwoColumns()

      Dim col1 As Range, col2 As Range, prod1 As String, prod2 As String, lr As Long

      Dim incol1 As Variant, incol2 As Variant, dif1 As Integer, dif2 As Integer, r As Long

 

      Set col1 = Columns("A")

      Set col2 = Columns("C")

      lr = Columns("A:C").SpecialCells(xlCellTypeLastCell).Row 

     

      For r = 2 To lr

          prod1 = Cells(r, col1.Column).Value

          prod2 = Cells(r, col2.Column).Value

   

          'check if prod1 is in col2

          If prod1 <> "" Then

              Set incol2 = col2.Find(prod1)

              If incol2 Is Nothing Then

                  Cells(r, col1.Column).Interior.Color = vbYellow

                  dif1 = dif1 + 1

                  'Sheets("difs").Cells(dif1, 1).Value = prod1  'needs sheet difs added

              End If

          End If

   

          'check if prod2 is in col1

          If prod2 <> "" Then

              Set incol1 = col1.Find(prod2)

              If incol1 Is Nothing Then

                  Cells(r, col2.Column).Interior.Color = vbYellow

                  dif2 = dif2 + 1

                  'Sheets("difs").Cells(dif2, 2).Value = prod2  'needs sheet difs added

              End If

          End If

      Next r

  End Sub

 

Macro explained:

• We declare pairs of variables to represent the first and second columns to be compared in Excel. These include object variables to store the column Range object, string variables for entries in the columns, and numerical to count the number of discrepancies between the two Excel lists or columns being compared. The variables incol1 and incol2 are used to find and set each value from one column into the other.
• Then we set col1 and col2 to the two columns being compared in Excel. Note that we refer to the columns are in the active sheet. If the columns are in different sheets, the respective worksheets need to be referenced. That would be for example: Set col1 = Sheets("targetSheet1").Columns("A"). In this example, these are column A and C. However, that can be changed as needed. Furthermore, the code could be modified to accept a selection of two columns instead (either a manual selection or through input box too).
  
• After getting the last row with content from both columns (they may not have the same length), we start a loop to go through every row with data. Note that we start on row 2 to skip the headers.
• For each row, we get the value in both columns or lists being compared, and assign it to the variables prod1 and prod2. Again, if the columns are in different sheets, the relevant worksheet reference needs to be added before Cells. The next two blocks of code check if each value (or product in this example) is found in the other column. Each of those blocks of code is checking the following:
• First checks whether the cell has a value (if is different than an empty string)
• Then tries to find the entry in the other column and assigns that to the corresponding variable (incol1 or incol2)
• If the value is not found (incol1 or incol2 are nothing), then it highlights the cell with that value (if columns are in different sheets, add the worksheet reference before Cells)
  
• Additionally, it copies the value to another sheet that has previously been added. In the example above that’s the sheet “difs”. IMPORTANT: That needs to be added previously.
  
• The loop continues until the last row with content. The end result highlights the cells with values or products that only appear in one of the Excel lists or columns. Furthermore, the discrepancies are added to other sheet (optional).

This is how we compare two columns or lists in Excel with VBA macros.

 

Other examples:

• How to Protect All Selected Workbooks with Excel VBA macros
  
• How to Import Data From Google Sheets with Excel VBA macros
  
• How to Show Chart For Selected Row with Excel VBA macros