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.

1. Find and Highlight Duplicates in Two Columns in Excel Using Conditional Formatting

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

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

2. Find Duplicates in Two Columns in Excel Using True/False 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:

1. Add a new column. I have named the new column ‘Result’.
2. Write this formula in the first cell of that column: =A2=B2
3. 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.
4. Now double-click on the Fill Handle. The results will come up as TRUE or FALSE indicating a match or mismatch respectively.

3. Find Duplicates in Two Columns in Excel Using the 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:

1. Create a new column first. I have named the new column header ‘Result’.
2. 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.
3. Then press ENTER.
4. 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.

•  5 Ways to Find Matching Values in Two Worksheets in Excel
• How to Compare Rows in Excel for Duplicates (7 Ways)
• How to Find Similar Text in Two Columns in Excel (5 Ways)

4. Create a New Rule to Find Duplicates in Two Columns in Excel

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:

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

5. Match Two Columns and Output the Third in Excel

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:

1. Select a cell.
2. Type the following formula: =VLOOKUP(E2,$A$2:$B$12,2,FALSE)
3. After that, press the ENTER key.
4. Now drag down the Fill Handle over the range of cell range F2:F6. And done! You will get your desired results this way.

6. Use the “Duplicate Values” Command to Find Duplicates in Two Columns in Excel

1. Select the two columns you want to compare.
2. Then go to the Home tab from the ribbon.
3. Click on the Conditional Formatting drop-down in the Styles group.
4. 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.
5. Check that the “Duplicate” option is selected.
6. 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:

• 4+ Methods to Filter Duplicate Values in Excel
• Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]
• 5+ Formulas to Find Duplicates in One Column in Excel

7. Compare Two Columns in Excel and Return the Differences Using IF & COUNTIF Functions

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

1. Create a new column. I have created a new column in Column C and named it Matches.
2. 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.
3. Press ENTER.
4. 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:

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

• 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel
• Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]
• How to Remove Duplicates in Excel [13 + Different Methods]

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.