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.

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.

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.

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

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:

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

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.

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

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.

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

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:

• 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 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:

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

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.

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

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

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.

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.

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

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.

• 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:

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

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.

• 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:

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