How to compare two columns in Excel to find duplicates



Excel Functions | Excel Macro

Many times we require to compare two columns’ data and find out all the Duplicates. So in this article, you are going to see how we can compare two columns in Excel and find out duplicates.

There are two ways of comparing two columns in an Excel Worksheet.

1.By Using Excel Formula
2. By Using Excel Macro

Compare two columns by using Excel Formulas:

By using ISERROR and MATCH formula we can compare 2 columns.
Type the below formula in the column where you want the list of all duplicates in both the column

=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)

Where:
A1 – Is the column Which you want to be compared
$C$1:$C$5 – Is the Range which you want compared with

Compare Two Column in Excel Formula

Note: In the above formula, whichever cell it is finding as duplicate in Column A, it will populate in the same row. As shown in the above image.

Compare two columns by using Excel Macro:

1. This function will compare Column A with Column B and list the Duplicates in Column C in a Sequence.


Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant

Range("A1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("A1:" & Selection.Address)
Range("B1").Select
Selection.End(xlDown).Select
Set CompareRange = Range("B1:" & Selection.Address)


'If you want to exchange the columns like if you want to
'compare B  with A then chnage the range
'and selection or vise-versa

i = 1
To_Be_Compared.Select

    For Each x In Selection
        For Each y In CompareRange
            If x = y Then
              Range("C" & i).Value = x
              i = i + 1
            End If
        Next y
    Next x
End Sub

2. This function will compare Column A with Column B and list the Duplicates in Column C but not in a Sequence. It will list exactly in the same row which is duplicate.

Private Sub CommandButton2_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant

Range("A1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("A1:" & Selection.Address)
Range("B1").Select
Selection.End(xlDown).Select
Set CompareRange = Range("B1:" & Selection.Address)

'If you want to exchange the columns like if you want to
'compare B  with A then chnage the range
'and selection or vise-versa

To_Be_Compared.Select

    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 2) = x
        Next y
    Next x
End Sub

3. This function will ask for Column to Compare, Column To be compared and Column to list the Duplicates. This is completely dynamic. It can complare any Column with any Column and list all the duplicates in any of the column.



Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant
str1 = InputBox("Enter Column Name to be Compared")
str2 = InputBox("Enter Column Name to Compare")
str3 = InputBox("Enter Column Name to put the Result")
Range(str1 & "1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range(str1 & "1:" & Selection.Address)
Range(str2 & "1").Select
Selection.End(xlDown).Select
Set CompareRange = Range(str2 & "1:" & Selection.Address)


'If you want to exchange the columns like if you want to
'compare B  with A then chnage the range
'and selection or vise-versa

i = 1
To_Be_Compared.Select

    For Each x In Selection
        For Each y In CompareRange
            If x = y Then
              Range(str3 & i).Value = x
              i = i + 1
            End If
        Next y
    Next x
End Sub

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Jack on January 8, 2012 at 11:30 am

Hi,

I was trying to do this in Macro for so long. By using this i am able to save lots of time now.

Thanks alot.
Reply
- Vishwamitra Mishra on January 8, 2012 at 1:41 pm
  
  Thanks Jack !!!
  Reply
  - Anil on January 14, 2012 at 4:28 am
    
    It helped me too!! Thank you
    Reply
    - Vishwamitra Mishra on January 14, 2012 at 1:13 pm
      
      Welcome Anil 🙂
      Reply
      - mona on April 20, 2012 at 5:48 pm
        
        Hi Vishwamitra,
        
        I have written macro to highlight duplicate between X columns, but am struck with macro to Highlight duplicates if col A and B are the same but not col C. PLease help me with this.
        
        Thanks,
        
        Mona
      - Vishwamitra Mishra on April 21, 2012 at 4:50 am
        
        Hi Mona,
        Thanks for writing. Can you send me a sample Excel(for example), how exactly you want to highlight the Column A, B and C.
        Thanks,
        Vish
mona on April 20, 2012 at 5:42 pm

Hi,

It was really helpful, can you please shed some light on a situation where we want to Highlight duplicates if col A and B are the same but not col C.

Your help is highly appreciated 🙂

Thanks,

Mona
Reply
nur on June 9, 2012 at 6:52 am

Dear Vish Bhaia, i want to get work book. please provide me…
Reply
vicktor schausberger on June 12, 2012 at 12:47 am

My problem is similar, but is about two comblist. my data base is A1:F1900 from this a:f I need to calculate all the gaps of each row first, and after generate a list with the same gaps, so the new list that I have in another sheet has to be compare and remove duplicates, and second from the a:f data need to calculate the differences by columns generate a list with this differences compare if this new list don't have the same interval with a1:f6 and again remove the duplicates from the other sheet. if somebody give me a hand in this, would be nice, is a real challenge.
Reply
satish on December 5, 2012 at 9:50 am

Excel-Sheet1 Excel-Sheet2 Result-in-Sheet3

Name Place Name Place Name Place

—————— ———————- —————-

satish mysore satish mysore TRUE TRUE

ravi bellary ravi Bangalore TRUE FALSE

Reddy pennukonda venu pennukonda FALSE TRUE

Need macros to compare sheet-1 with Sheet-2 and results as True or False.

It should compare Sheet-1 with Sheet-2 from 2nd row to the End of the excel. Please provide me the working Macros. Thanks in advance.
Reply
satish on December 5, 2012 at 9:57 am

Excel-Sheet1

Name Place

————–

satish mysore

ravi bellary

Reddy pennukonda

Excel-Sheet2

Name Place

————–

satish mysore

ravi Bangalore

venu pennukonda

Need to compare the sheet-1 and sheet-2 and to get the result in 3rd Excel sheet-3 as follows. Please provide the Excel Macros for this comparision. It should compare the result from 2nd row to end of the excel sheet where ever the data is present.

Result-Sheet3

Name Place

————-

TRUE TRUE

TRUE FALSE

FALSE TRUE
Reply
kumar on August 28, 2014 at 10:43 am

Hi
I need a excel macro for comparing some 7 column in one file with another updated files, and need to display in saperate sheet what the updated with exixting thing is, what the extra added part to the existing part etc.

regards
kumar
Reply
monty on October 17, 2014 at 12:36 pm

Hi,
How to swap this two numbers in column using macro in excel?
Present in column as:
49.193,-123.172
-120.639, 45.689
Should look like:
-123.172 49.193
45.689 -120.639
(comma should also be removed with a space)
thanks in advance.
Reply
Arun Singh on June 8, 2016 at 6:11 am

Thank you for above codes
Reply
Jessica on March 15, 2017 at 9:44 pm

Awesome article!

I just found a super easy spreadsheet compare tool: https://automatethatshit.com/lab/compare-spreadsheets
Reply
richa on November 30, 2018 at 4:29 pm

I am sorry but unable to use this code it is not providing exact result .
My query is to compare two column value and to paste in other column but it is not working as expected.
Reply