Compare Two Columns Excel – Highlight Match – Bulk Data Duplicates

Excel Compare Two Columns & Report Differences

Learn how to compare multiple columns in Excel with huge volume of data.

1. Find duplicate entry in 2 columns using Conditional Formatting
2. Compare for duplicates using countif.
3. Compare Two columns & find duplicates using Excel VBA

Out of these three methods, the third one is very faster. This methods can be used to compare huge column of data within less time.

Are you not excited to get such a easy solution?. Read more…

Compare & Highlight Matching Duplicate Records

Assume the data to compare are in Columns A & B. Just to see the duplicate entries with colour difference follow this method.

1. Select all columns that has data.
2. Click Home in Excel menu.
3. Choose “Conditional Formatting” -> “Highlight Cells Rules” -> “Duplicate Values”
4. Choose the font colour & background to highlight.
5. Click Ok. Values with repetition will be highlighted.

No, Excel will compare the data in selected data range and highlight all the duplicate entries.

The problem with this method, is that it highlights all the differences with same colour. Also we cannot select only the duplicate entries.

Is that it? No. we have few advanced options too.

Find Duplicate Entries in Two Columns with Excel Formula

It can be done with a Lookup or Countif Formula. Countif is simpler compared to a lookup formula.

How to compare two columns with Countif?

i.e., Get value from column B, compare it with Column A & find whether any value is repeated.

1. Assuming Column A & B are to be compared.
2. Enter this formula in cell C1.
3. Formula ‘=countif(“$A:$A”,$B1)’
4. Copy paste this formula in Column C till end of data rows.
5. Unique values will have count as ‘0’.
6. Repeating or duplicate entries will have count greater than ‘0’.

Now we have the comparison data in Column C for B->A verification. The data in Column C can be filtered for unique or duplicate entries.

If this same process has to be done for A->B comparison, then the formula should be reversed as ‘=countif(“$B:$B,$A1)’

Compare & Find duplicate entries in Two columns with Excel VBA Macro

In Excel VBA, this can be done by plain comparison of each cell with two for loops, which is very efficient for low volume of data.

In case, if you have huge volume of data, then the above loop will keep on execute for long hours or days to complete. There are also possible chances that Excel application will hang in middle of execution & result in data loss.

To compare a huge volume of data in your Excel sheet, use this method to find duplicate values and report the differences.

Public Sub Compare_Two_Columns_Highlight_Duplicates()
    Dim iRow As Double, oRow As Double, wsI As Worksheet
    Dim Val1 As Variant, Val2 As Variant
    Dim iTotRecsA As Double, iTotRecsB As Double
    
    'Iniate Variables
    iRow = 1
    Set wsI = ThisWorkbook.Sheets("InputSheet")
    
    'Get Total number of records in Column
    iTotRecsA = wsI.Range("A" & Rows.Count).End(xlUp).Row
    iTotRecsB = wsI.Range("B" & Rows.Count).End(xlUp).Row
        
    'Loop thru each data in Column A
    While wsI.Cells(iRow, 1) <> ""
        
        'Compare Column A value with Column B
        Val1 = wsI.Cells(iRow, 1)
        wsI.Cells(iRow, 1).Interior.Color = vbWhite
        If Application.WorksheetFunction.CountIf(wsI.Range("B1:B" & iTotRecsB), Val1) > 0 Then
            'Highlight when Count is Greater than 0
            wsI.Cells(iRow, 1).Interior.Color = vbYellow
        End If
        
        'Process next row data
        iRow = iRow + 1
    Wend
        
    MsgBox " Data Comparison Completed"
End Sub

This code will not take much time. It executes much faster than a normal comparison.

Much better than this.

In case if you don’t want to compare all these columns data & in a hurry to remove all duplicate entries. Then Excel has built in option Menu -> Data -> “Remove duplicates’

Just select all the cells with data and click this option. It will remove all duplicate entries in seconds.

Duplicates are fine. But how to find unique values?

Well. It is easy.

Just use the same VBA code or formula method with countif. Or, you can follow this link to find a technique that uses “Advanced Filter” option within Excel itself.