7 Methods to Find Duplicates in Two Columns in Excel

There are great tools and commands to find duplicates in two or more columns in Excel that will ease your work and save a lot of time to treat lengthy datasheets. In this article, I will show you 7 easy methods to find duplicates in two columns in Excel.

Find Duplicates in Two Columns in Excel.xlsx

Table of Contents

Find Duplicates in Two Columns with Conditional Formatting

To find and highlight duplicates in two columns in Excel using Conditional Formatting, follow these steps:

Select the two columns that you want to compare.
Navigate to the Home tab in the Excel ribbon.
Click on Conditional Formatting in the Styles group.
Now, go to Highlight Cells Rules > Duplicate Values.
In the Duplicate Values dialog, check that Duplicate is selected.
Choose a formatting style from the Values with drop-down.
Finally, hit OK.

Find Duplicates in Two Columns in Excel with Logical Formula

To find the duplicate names in columns, I have a list of brands around the world here below.

Now to check the duplicates in two columns in Excel, follow the following steps:

Add a new column. I have named the new column ‘Result’.
Write this formula in the first cell of that column: =A2=B2
Then press ENTER.
If there is a match between the columns, the result will be TRUE. If there is a mismatch, the result will be FALSE.
Now double-click on the Fill Handle.
The results will come up as TRUE or FALSE indicating a match or mismatch respectively.

Find Duplicates in Two Columns in Excel Using IF Function

Using the IF function is another easy way to find the similarities and dissimilarities between two distinct columns. It gives a more expressive result than the first one I personally feel.

To apply the IF function to find duplicates in two columns in Excel:

Create a new column first. I have named the new column header ‘Result’.
Write this formula in the first cell of the new column: =IF(A2=B2,”Duplicate”,”Different”)In this formula, A2=B2 depicts a logical test. If the value is true, the result will be Duplicate. If the value is false, the result will be Different.
Then press ENTER.
Now drag the Fill Handle to the end of the column (C2:C12).

Instead of the words Duplicate and Different, you can use other words also. Such as ‘Match and Mismatch’, ‘Positive and Negative’, ‘Same and Unique’ etc.

Find Duplicates in Two Columns in Excel by Creating New Rule

Highlighting can make your work of sorting duplicate characters very easy. It works great especially when you are in a hurry because you can look at the colored cells quickly.

To find and highlight duplicates in two columns in Excel by creating a new rule, follow these steps:

Select the two columns you want to compare.
Then go to the Home tab.
Click on the Conditional Formatting in the Styles group.
Now hit ‘New Rule’ from the Conditional Formatting drop-down list.
A dialog box called New Formatting Rule will come along.
From the dialog box, select Use a Formula to Determine Which Cells to Format as a rule type.
Now type this formula inside the Format Values Where This Formula Is True bar: =$A1=$B1
Then click on the Format button. The Format Cells dialog box will show up.
Now, change the Background Color under the Fill section as per your preference. For example, I have used yellow to highlight.
Hit OK to save the changes.

This will highlight the cells which have the same characters located in the same row and they are easy to find out in this way if the list is overlong.

Find Duplicates in Two Columns in Excel & Output Third by VLOOKUP

This method is only applicable where we need to compare two columns with some similar names or values and at the end, the comparison will return a result in the third column co-related with the 1st column names/values.

For instance, I have a list of Product Codes in the column named Product Code and their prices in the column Price. Product Code-2 has some of the same products as column A (Product Code) and the final result will return the values in column F (Price-2) corresponding with column B (Price).

To match two columns in Excel and return data from a third column:

Select a cell.
Type the following formula: =VLOOKUP(E2,$A$2:$B$12,2,FALSE)
After that, press the ENTER key.
Now drag down the Fill Handle over the range of cell range F2:F6.

And done! You will get your desired results this way.

Find Duplicates in Two Columns in Excel Using Duplicate Values Command

To find duplicated in two columns in Excel with Duplicate Values command, go through these steps below:

Select the two columns you want to compare.
Then go to the Home tab from the ribbon.
Click on the Conditional Formatting drop-down in the Styles group.
Now put the cursor on the Highlight Cells Rules command and click on Duplicate Values from the new drop-down list.
The Duplicate Values dialog box will come along.
Check that the “Duplicate” option is selected.
Change the formatting style in the Values with drop-down.
I choose the Light Red Fill highlight and here is the end result in the image below:

Find Duplicates in Two Columns by IF & COUNTIF Functions Returning Difference

These steps will compare two columns in Excel and show the unique values as the difference between the columns:

Create a new column. I have created a new column in Column C and named it Matches.
Now write this formula on the top cell of the column: =IF(COUNTIF($B$2:$B$12,$A2)=0,””,$A2)The formula checks if the value in cell A2 exists in the range from B2 to B12. If it’s found, it copies the value from A2; if not, it leaves the cell blank.
Press ENTER.
After that, drag the Fill Handle down from cell C2 to cell C12.

How to Count Duplicates in Excel

Suppose you have a lengthy list of names and you want to count how many times one name is repeated. This method will do that for you.

Here is a long list of different brand names. To count the duplicates within a list, follow these steps:

First, create two new columns to find the duplicate values. I have named them Brands-2 and Count.
Insert the values that you want to count their number of occurrences.
Insert the following formula in cell D2: =COUNTIFS(A2:A20,C2)
Press ENTER. It will show the formula result.
Finally, drag the Fill Handle down from D2 to D5.

Conclusion

I hope you got an overall idea about all possible techniques to find the duplicate values in columns in Excel. Use whichever method you feel comfortable with. Also if you have any questions or confusion regarding this topic, feel free to comment here. I will try to respond as soon as possible.

Frequently Asked Questions

What is the fastest way to find duplicates in Excel?

The fastest way to find duplicates in Excel is by using the Conditional Formatting feature:

Select the column or range where you want to find duplicates.
Go to the Home tab.
Click on Conditional Formatting.
Choose Highlight Cells Rules and then Duplicate Values.

Excel will instantly highlight duplicate values in the selected range, allowing for quick identification and analysis.

How do I filter only duplicates?

To filter only duplicates in Excel, use the following steps:

Select the column containing potential duplicates.
Go to the Data tab.
Click on Remove Duplicates.
Choose the column(s) where you want to find duplicates.
Click OK.

This will filter and display only the duplicate values in the selected column(s). Verify and manage duplicates as needed.

5/5 - (3 votes)

2 Comments

Mark G says:
November 15, 2023 at 3:19 pm
This is really good information but hasn’t helped what I am trying to accomplish. Maybe you may be able to get me in the right direction.
I have a master list of part numbers with the quantities three different locations have on hand. On another workbook I have a list of the parts each location has on order.
I am trying to compare the master list of items to the list of orders. I can run different formulas but I think having the same part number ordered is throwing the formulas off. I was simply trying to run a rule to get a true/false next to the item number on the master list if it has been ordered.
1. Rhidi Barma says:
  November 16, 2023 at 5:56 am
  Thank you for your comment and for sharing your specific scenario. I understand that comparing part numbers between the master list and the list of orders can be a bit tricky, especially if the formulas are impacted by the presence of the same part number in both lists.
  To achieve your goal of marking items on the master list as “ordered” or “not ordered,” you can use a combination of formulas or methods in Excel. Here’s a suggestion:
  On the master list sheet, you can use the VLOOKUP function or INDEX and MATCH functions to check if each part number in the master list exists in the list of orders.
  This would involve creating a new column next to the item numbers with a formula like:
  =IF(ISNUMBER(MATCH(A2, Orders!$A$2:$A$100, 0)), “Ordered”, “Not Ordered”)
  Here, A2 is the reference to the part number in the master list, and Orders!$A$2:$A$100 is the range of part numbers on the orders sheet.
  Please adjust the cell references and ranges based on the actual layout and size of your data. If you encounter any specific challenges or have additional requirements, feel free to provide more details, and I’d be happy to assist you further.