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:
1 |
=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.
1 |
=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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
1 |
If fcCell.Value = scCell.Value |
To
1 |
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).
1 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
1 |
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.