How to compare two columns in Excel to find duplicates
Many a 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
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 viseversa 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 viseversa 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 viseversa 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
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 
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.
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
ExcelSheet1
Name Place
————–
satish mysore
ravi bellary
Reddy pennukonda
ExcelSheet2
Name Place
————–
satish mysore
ravi Bangalore
venu pennukonda
Need to compare the sheet1 and sheet2 and to get the result in 3rd Excel sheet3 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.
ResultSheet3
Name Place
————
TRUE TRUE
TRUE FALSE
FALSE TRUE
ExcelSheet1 ExcelSheet2 ResultinSheet3
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 sheet1 with Sheet2 and results as True or False.
It should compare Sheet1 with Sheet2 from 2nd row to the End of the excel. Please provide me the working Macros. Thanks in advance.
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.
Dear Vish Bhaia, i want to get work book. please provide me…
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
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.
Thanks Jack !!!
It helped me too!! Thank you
Welcome Anil
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
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