How to find duplicate values in two columns in Excel?
When we use a worksheet, sometimes we need to compare two columns and find the same values. For example, I have two long columns of student names, and now, I want to compare them and find out the same names. If I compare them cell by cell, it will be tiresome and waste a lot of time. Are there any quick ways to finish this task?
Find duplicate values in two columns with Formula
Find duplicate values in two columns with VBA code
Quickly find duplicate values in two columns with Kutools for Excel
Office Tab: Enable Tabbed Editing and Browsing in Office, Just Like Chrome, Firefox, IE 8/9/10. Read more...
Classic Menu for Office: Bring Classic Menus and Toolbars of Office 2003/XP/2000 Back to Office 2007, 2010 and 2013. Read more...
Find duplicate values in two columns with Formula
Look at the following screenshot, I have column A (A1:A15) and column C (C1:C13) which contain some duplicate names.
By using a formula, we can compare two columns and display the duplicate values. Please do with the following steps:
1. In cell B1, input this formula: “=IF(ISERROR(MATCH(A1,$C$1:$C$13,0)),"",A1)”.
A1 is the column which you want to be compared.
$C$1:$C$13 is the range that you want to be compared with.
You can change the variables for what you are using.
2. Press the Enter key. Select cell B1, and then drag the fill handle over cell B15.
3. And all of the duplicate names will be displayed in column B. See screenshot:
Find duplicate values in two columns with VBA code
The following VBA code can also help you to find out the duplicate values between two columns.
1. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:
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)
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
2. Click 
button to execute the code, and some prompt boxes will pop out, please input the relevant information into the box. Enter the column name A to be compared. See screenshot:
3. Click OK to continue. Enter the column name C to compare.
4. Click OK. Enter the column name E to put the result. See screenshot:
5. Go on with OK. And all of the duplicate values have been displayed in column E.
 |
 |
 |
Quickly find duplicate values in two columns with Kutools for Excel
With the help of Kutools for Excel, you can find the duplicate values quickly and comfortably.
Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now
After installing Kutools for Excel, please click Kutools > Compare Ranges.
1. In the Compare Ranges dialog box, click the first 
button to select the source column to be compared. And click the second button to select the column you are compared with. Then choose Same Values from Rules drop down list. See screenshot:
2. Then click OK, a prompt box will pop out to remind you how many cells have been selected.
3. Click OK, the same values which both in column A and in column C have been selected in column A. See screenshot:
With this utility, you can also find the different values between two columns.
Notes:
- Compare same range: It will select the duplicate values in the same column or range.
- My data has headers: If the data you are compared has headers, you can check this option, and the headers will not be compared.
- Select entire rows: With this option, the entire rows which contain the same values will be selected.
- The two comparing ranges must contain the same number of columns.
You can click here to know more about this feature.
Related Article:
How to find unique values between two columns in excel?
Kutools for Excel
More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.
