9 Unique Cases to Compare Rows for Duplicates in Excel

Users frequently need to locate or compare different rows to search for all the duplicates within them in Excel. It’s quite straightforward and easy to compare rows to look for duplicates. You may simply accomplish this by using basic formulas or features available in Excel. I’ll demonstrate how we can easily compare different rows in excel for duplicates with examples.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Learn to Compare Rows for Duplicates in Excel with These 9 Unique Cases

This article shows you three distinct ways to search for duplicate rows in Excel. While doing so, I’ll show you how to compare these duplicates if they are in one row or in different rows in our datasets. Again, I will show you how to compare different rows to find all the duplicates from the whole dataset at the same time.

I’ll be using this sample dataset for this demonstration.

compare rows in excel for duplicates Dataset


Condition 1

1. Comparing Duplicates in Rows from Different Columns

First, I’ll show you here how you can find duplicates from different columns. We can use different Excel formulas to do that. Details will be given in each example.

Different Columns

1.1. Finding Duplicates Through Equal Operator

Using the equals operator is another easy way to compare columns in Microsoft Excel. You can do it by following these steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Make a new column for the results and add the formula for comparing each cell, as shown below.

Finding Duplicates Through Equal Operator

  • As shown below, Excel will display the result as FALSE if the comparison doesn’t work, and TRUE if it does.

Different Columns

1.2. Using IF Function

Here, I’ll use IF function now to identify which rows have duplicates. The IF function lets you compare a value with what you expect in a logical way. Follow the steps below to better understand.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we introduce a brand new column (D) with the name Decision Column.
  • Then we put the below formula:

=IF(B6=C6,”Same”,”Not Same”)

  • Later we AutoFill the formula down to the end of the column.

compare rows in excel for duplicates (2BU) Using IF Function

Different Columns

1.3. Utilizing VLOOKUP Function

We can use the VLOOKUP function to compare two different cells. Let’s follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • Add the VLOOKUP Formula to a new result column to compare different cells, as shown below.

=IFERROR(VLOOKUP(B6,$C$6:$C$22,1,0),”Not Found”)

  • Now, you can copy-paste or drag the updated formula to all the cells to obtain the final, error-free result, as shown below.

Utilizing VLOOKUP Function

🔨  Formula Breakdown

👉 Vlookup function will look for the value first from the range of C6:C22 for the value of each cell in B Column.

👉 0 is indicating we are looking for the exact match.

👉 Basically, after that IFERROR function will return the value if the similarity is found, otherwise, it will show Not Found.

Different Columns

1.4. Combining IF and COUNTIF Functions

Again, I’ll add an extra column or make a new one to display rows with duplicates. Here, I’ll use a formula that combines IF and COUNTIF functions to show the results. COUNTIF function is used to count how many cells meet certain criteria. For example, to count how many times a certain city shows up on a list of customers. The IF function lets you compare a value with what you expect in a logical way. Follow these steps to get a better idea of what’s going on.

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s select cell C6 and insert this formula.

=IF(COUNTIF($D$6:$D$22,B6)>0,”Match”,”Don’t Match”)

  • Then we put Enter and drag down the cursor to fill up the remaining cells of the column.

Combining IF and COUNTIF Functions

🔨  Formula Breakdown

👉 COUNTIF function will look for the value first from the range of D6:D22 for the value of each cell in B Column.

👉 0 is indicating we are looking for the exact match.

👉 Basically, after that IF function will return the value as Match if the similarity is found, otherwise it will show Don’t Match.

Different Columns

1.5. Setting New Rule for Conditional Formatting

I won’t put duplicates in between separate column now. Instead, I’ll highlight them here in the main data set. See the steps below for how to do that.

Using Equal Operator

Here we will find duplicates in the same row by using the EQUAL operator in New Rule. For that we will follow steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of cells B6:C22 from the main data set.

  • Second, on the ribbon, select the Home tab and click on Conditional Formatting from the Styles group.
  • After that, choose New Rule from the drop-down menu.

  • Third, you’ll see a box called “New Formatting Rule.”
  • In this case, to use a formula Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
  • Then, put the formula in the formula box to type:

=$B6=$C6

  • Then, after using the above formula, click the Format button to change the way the cells look.
  • After setting the criteria for highlighting, click OK.

  • Last, you’ll be able to identify the duplicates, which will be highlighted now in the data set as shown in the image below.

Using Equal Operator

Using COUNTIF Operator

This time we will find all the duplicates that belong to a different row by using COUNTIF operator in New Rule. For that we will follow the steps below:

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of cells B6:C22 from the main data set.

  • Second, on the ribbon, go to the Home tab and click on Conditional Formatting.
  • Then, choose New Rule from the drop-down menu.

  • Third, you’ll see a box called “New Formatting Rule.”
  • In this case, to use a formula Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
  • Then, put the formula in the formula box to type:

=COUNTIF($C$6:$C$22,B6)>0

  • Then, after using the above formula, click the Format button to change the way the cells look.
  • After setting the criteria for highlighting, click OK to close the dialog box.

  • Last, you’ll see the duplicates, which will be highlighted in the data set like in the image below.

compare rows in excel for duplicates using COUNTIF Operator

Different Columns

1.6. Embedding VBA

Now we will use VBA to compare duplicate rows. Before talking about the method, first, we have enabled the Developer tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking it here. Let’s follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s press Alt + F11 to bring up the VBA Window. You can also do this by going to the Developer tab and clicking on Visual Basic.

compare rows in excel for duplicates with VBA

  • Then we choose Module from Insert from the menu. This will bring up the window for the VBA Module. Here is where we will put our code.

Embedding VBA

  • Now we type the code below:
Sub CompareColumns()

    For i = 6 To 22
    If Cells(i, 2) = Cells(i, 3) Then
    Cells(i, 2).Interior.ColorIndex = 37
    Cells(i, 3).Interior.ColorIndex = 37
    End If
    Next

End Sub

  • Now we save and run the code.

  • Now we will get the result.

Embedding VBA to compare rows in excel for duplicates

Condition 2

📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel

2. Comparing Duplicate Rows of Same Column

This time I will show you how to locate duplicates within the column itself in this part. I’ll find all the duplicates inside every cell value within the column.

Same Column

2.1. Showing Duplicates in New Column

I will add a column to the main dataset. Then use a formula that combines IF function with COUNTIF function to find out which value in a column has been used more than once.  COUNTIF function is used to count how many cells meet certain criteria. For example, to count how many times a certain city shows up on a list of customers. The IF function lets you compare a value with what you expect in a logical way. Follow these steps to get a better idea of what’s going on.

⬇️⬇️ STEPS ⬇️⬇️

  • First, let’s create a column in C where the formula will be used and the result will be shown.
  • Then, type the formula in cell C6.

=IF(COUNTIF(B$6:B$22,B6)>1,”Have Duplicates”,”Don’t Have Duplicates”)

  • Now press Enter.
  • Later we AutoFill the cells with the formula to find the results.

Showing Duplicates in New Column

🔨  Formula Breakdown

👉 COUNTIF function will look for the value first from the range of C6:C22 for the value of each cell in B Column.

👉 1 is indicating we are looking for the excat match.

👉 Basically, after that IF function will return the value as Match if the similarity is found, otherwise it will show Don’t Match.

Same Column

📕 Read More: 5 Ways to Create a Top 10 List with Duplicates in Excel

2.2. Highlighting Duplicates in Same Column

Now instead of comparing it with other columns, I’ll use the Conditional Formatting feature to mark the cells having duplicates in the column itself. To do that, I have gone through the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of cells B6:B22 from the main data set.

compare rows in excel for duplicates to highlight the duplicates

  • Second, on the ribbon, go to the Home tab and click on Conditional Formatting.
  • Then, choose New Rule from the drop-down menu.

  • Third, you’ll see a box called “New Formatting Rule.”
  • In this case, to use a formula Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
  • Then, put this formula in the box:

=COUNTIF($B$6:$B$22,B6)>1

  • Then, after using the above formula, click the Format button to change the way the cells look.
  • After setting the criteria for highlighting, click OK to close the dialog box.

  • Last, you’ll be able to see the duplicates, which will be highlighted in the data set like in the image below.

Highlighting Duplicates in Same Column to compare rows in excel for duplicates

Same Column

📕 Read More: 5 Ways to Find Duplicates in Column and Delete Row in Excel

2.3. Comparing Rows for Duplicates Using Conditional Formatting

I’ll look for duplicates in the whole set of data by using Conditional Formatting. See the steps below for how to do that:

⬇️⬇️ STEPS ⬇️⬇️

  • First, choose the range of data B6:B22 to find duplicates in the whole set of data.

  • Second, choose Conditional Formatting again from the Home tab, and then choose Highlight Cells Rules from the drop-down menu.
  • Then, choose Duplicate Values from the second drop-down menu.

compare rows in excel for duplicates using Conditionsal Format

  • Third, in the Duplicate Values dialog box, set the formatting rules and choose the text color and fill color for the final result.

  • In the end, you’ll see that the duplicate values in your cells are highlighted, like in the image below:

Comparing Rows for Duplicates Using Conditional Formatting

📕 Read More: 7 Ways to Find Similar Text in Two Columns in Excel


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️ At the end of the Excel file, there is a sheet where they can practice.


📝  Takeaways from This Article

📌  The reader can compare various rows for all the duplicates easily in Excel.

📌  Readers can find duplicates in the same row but in different columns to compare rows for all the duplicates.

📌  Readers can also find duplicates in different rows to compare rows for all the duplicates.

📌  Moreover, users can look for duplicates in the whole dataset.

📌  Again readers can use the VLOOKUP function for finding duplicates.

📌  VBA can be useful for the readers to compare rows in excel for duplicates.


Conclusion

That concludes today’s session. These are the methods for comparing rows for all the duplicates within them in Excel easily. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.

(Visited 135 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo