Compare two columns in Excel

There are a few ways to compare columns in Excel. In this tutorial, I’ll present different ways you can achieve this.

Row by row

The first method is to compare columns row by row – if the rows match then display the information in the third column.

In this case, we are going to use the following formula:

=IF(A1=B1,"Match","")

This formula returns “Match” if the condition is met.

Let’s see it in the following example.

What you have to remember is that this formula is case-insensitive, so Germany=Germany, but also Austria=austria.

In order to make a case-sensitive comparison, we are going to use the EXACT function. This function checks whether the strings are exactly the same and returns TRUE if the condition is met, otherwise it returns FALSE.

=IF(EXACT(A1,B1),"Match","")

Row by row using VBA

Case sensitive

So far, we had to use the third column in order to check matching rows.

This time, we are going to use VBA to highlight matching rows.

Sub CompareColumnsCaseSensitive()
    Set myRange = Selection
    Set firstColumn = myRange.Columns(1)
    Set secondColumn = myRange.Columns(2)
    
    counter = 1
    For Each fcCell In firstColumn.Rows
        Set scCell = secondColumn.Rows(counter)
        If fcCell.Value = scCell.Value And fcCell.Value <> "" Then
            fcCell.Interior.Color = vbGreen
            scCell.Interior.Color = vbGreen
        End If
        counter = counter + 1
    Next
End Sub

Code explanation:

2. We assign the current selection to myRange.

3-4. Assign columns to variables.

6. VBA created a counter variable. We are going to use it to refer to the rows in the second column.

7 – 14. For each Cell (in our case Row) in the first column we are going to compare the cell in the same row, but the second column. What we have to remember is that the comparison is case-sensitive.

There is also a condition (fcCell.Value <> “”) that the cells can’t be empty, otherwise, they would highlight cells in the selection that are empty.

10 – 11. If all conditions are met both cells are highlighted.

As I mentioned earlier, the comparison works only if both values are exactly the same, so Austria is not austria in this case.

Case insensitive

There are two simple ways, you can make case-insensitive comparisons.

First option

While making a comparison make the case of both values lowercase (or uppercase) run the code.

Change

If fcCell.Value = scCell.Value

To

If LCase(fcCell.Value) = LCase(scCell.Value)

Second option

Instead of changing values to lowercase, you can add the following code as the very first line of our code (before subroutine).

Option Compare Text

It’s going to make all text comparisons case-insensitive.

Highlight all matching cells

In the last part of this tutorial, let’s create a subroutine that’s going to highlight every cell that has a match in the other column, but doesn’t have to be restricted to the same row.

Sub CompareColumnsCaseSensitive()
    Set myRange = Selection
    myRange.Interior.Color = xlNone
    Set firstColumn = myRange.Columns(1)
    Set secondColumn = myRange.Columns(2)
    
    For Each fcCell In firstColumn.Rows
        For Each scCell In secondColumn.Rows
            If fcCell.Value = scCell.Value And fcCell.Value <> "" Then
                fcCell.Interior.Color = vbGreen
                scCell.Interior.Color = vbGreen
            End If
        Next
    Next
End Sub

This give us the following result.

Add this line at the beginning of the script.

Option Compare Text

If you want to see more example on how to deal with duplicates, you can read this tutorial. There, you’ll learn how to highlight columns, that have different sizes, with different colors.