How to Find Duplicates in Two Columns in Excel (7 Methods)

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.

Quickest Way to Find Duplicates in Two Columns in Excel

To find duplicates in two columns in Excel,

   

Select the entire data set.

Then go to the Home

Then click on the Conditional Formatting drop-down (under Styles group).

Now go to Highlight Cells Rules > Duplicate Values.

In the Duplicate Values dialog box, check that Duplicate is selected inside the bar.

Then change the Values with the command according to your choice. 

Shortcut Method to Find Duplicates in Two Columns in Excel

Overview

🔴 Method 1: The same texts will result in TRUE, the unique texts will result in FALSE.

🔴 Method 2: If the texts match the result will be DUPLICATE, if the texts don’t match the result will be DIFFERENT.

🔴 Method 3: If the texts (belonging in the same row) are duplicates, they will be highlighted.

🔴 Method 4: Applicable where we need to compare two columns and return a result in the third column.

🔴 Method 5: This technique will find out the duplicate values from the whole data set.

🔴 Method 6: This technique will compare two columns in Excel and show the unique values as the difference between the columns.

🔴 Method 7: Counts how many duplicates are in a datasheet.

🔗 How to Compare Rows in Excel for Duplicates (7 Ways)

Alternative Way #1: Use the True/False Logical Formula to Find Duplicates in Two Columns in Excel

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

Now to check the duplicates follow the following steps:

❶ Create a new column beside the following columns.

I have named the new column ‘Result’.

❷ Then write this formula in the first cell of that column:

=A2=B2

Use the True/False Logical Formula to Find Duplicates in Two Columns in Excel

A2: is the first input of column A.

B2: is the first input of column B.

The ‘=’ sign before the functions demonstrates a comparison between these two columns.

❸ 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 press on the Fill Handle and drag it to the end of the column (C2-C12).

Usage of True/False to Find Duplicates in Two Columns in Excel

The results will come up as TRUE or FALSE indicating match or mismatch respectively.

Indicate Duplicates using True/False in Two Columns in Excel

🔗 How to Find Similar Text in Two Columns in Excel (5 Ways)

Alternative Way #2: Use the IF Function to Find Duplicates in Two Columns in Excel

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,

❶ Create a new column beside the existing columns.

I have named the new column header ‘Result’.

❷ Then write this formula in the first cell of that column:

=IF(A2=B2,”Duplicate”,”Different”)

Use the IF Function to Find Duplicates in Two Columns in Excel

In this formula,

  • A2=B2 depicts a logical test
  • If the value is true, the result will be Duplicate,
  • And if the value is false, the result will be Different.

❸ Then press Enter.

❹  Now hold the Fill Handle and drag it 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

Alternative Way #3: Use 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 coloured cells quickly.

To use highlight for finding duplicates follow these steps:

❶ Select the entire data table.

❷ Then go to the Home tab.

❸ Then click on the Conditional Formatting drop-down (under Styles group).

the Conditional Formatting drop-down

❹ Now hit ‘New Rules’ from the Conditional Formatting drop-down list.

‘New Rules’ 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

Use New Rule to Find Duplicates in Two Columns in Excel

❼ 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.

Format Cells to indicate Duplicates in Two Columns in Excel

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+ Formulas to Find Duplicates in One Column in Excel

Alternative Way #4: Match Two Columns and Output 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).

❶ Type the following formula in the empty column (besides the third column):

=VLOOKUP(E2,$A$2:$B$12,2,FALSE)

Use VLOOKUP Function to Match Two Columns and Output Third in Excel

I have put the formula in column F which I named Price-2.

E2: is the lookup value as the third column is E.

$A$2:$B$12: is the table array.

2: is the column index number.

FALSE: is the range lookup that will give a result of N/A if the value is not found.

❷ After that, press Enter.

❸ Now drag down the Fill Handle over the range of cells F2:F6.

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

Match Two Columns and Output Third in Excel

🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]

Alternative Way #5: Use the ‘Duplicate Values’ Command to Find Duplicates in Excel

This will find out the duplicate values from the whole data set that need not necessarily belong in the same row. The values can be situated anywhere in the data table.

❶  Select the entire data set.

❷ Then go to the Home tab from the ribbon.

❸ Then click on the Conditional Formatting drop-down (under Styles group).

❹ Now drift the cursor on the Highlight Cells Rules command and click on Duplicate Values from the new drop-down list.

Use the ‘Duplicate Values’ Command to Find Duplicates in Excel

The Duplicate Values dialog box will come along.

❺ Check that Duplicate is selected inside the bar.

Duplicate Values dialog box: Use the ‘Duplicate Values’ Command to Find Duplicates in Excel

❻ Then change the Values with command according to your choice.

I choose the Light Red Fill highlight and here is the end result in the image below.

Output of the Usage of the ‘Duplicate Values’ Command to Find Duplicates in Excel

🔗 4+ Methods to Filter Duplicate Values in Excel

Alternative Way #6: Compare Two Lists and Return Differences in Excel

This technique will compare two columns in Excel and show the unique values as the difference between the columns in a separate column.

❶ Create a new column.

I have created a new column in Column C and named it Matches.

To filter out the duplicate values from the two columns, we are going to use the IF function.

The basic syntax of this function is:

=IF(COUNTIF(RANGE, CRITERIA VALUE) Logical TEST, VALUE_IF_TRUE,VALUE_IF_FALSE)

❷ Now write this formula in that column:

=IF(COUNTIF($B$2:$B$12,$A2)=0,””,$A2)

Usage of If function to Compare Two Lists and Return Differences in Excel

COUNTIF: gives the logical test.

RANGE: here in my datasheet, the range is $B$2:$B$12.

CRITERIA VALUE: A2 is my criteria value as the B2-B12 were compared to A2.

❸ Press Enter. It will show the duplicate value.

❹ After that, drag the Fill Handle down from C2 to C12.

Output: Compare Two Lists and Return Differences in Excel

🔗  How to Remove Duplicates in Excel [13 + Different Methods]

Alternative Way #7: 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 from this list,

❶ First, create two new columns to find the duplicate values.

I have named them Brands-2 and Count.

❷ Insert the names that you want to count their repetitions.

Now, a COUNTIFS function will be placed beside that column.

The generic syntax of the COUNTIFS function is:

=COUNTIFS(RANGE, CRITERIA VALUE)

❸ Insert the following function in the D2 cell:

=COUNTIFS(A2:A20,C2)

Use COUNTIFS Function to Count Duplicates in Excel

❹ Press Enter. It will show the result.

❺ Finally, drag the Fill Handle down from D2 to D5.

Output: Count Duplicates in Excel

🔗 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]

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.

(Visited 2,665 times, 3 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *