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:

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.

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.

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

To

Second option

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

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.

This give us the following result.

Add this line at the beginning of the script.

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.