Excel Find Duplicate Rows Based on Multiple Columns

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to find and highlight duplicate rows based on multiple columns in Excel. It makes the huge dataset attractive and easy to calculate. In this article, we are going to know how to do that with some beautiful examples and explanations.


Excel Find Duplicate Rows Based on Multiple Columns: 6 Quickest Methods

1. Excel COUNTIFS Function for Finding Duplicate Rows Based on Multiple Columns

Excel COUNTIFS function helps us count the number of cells from a range based on multiple criteria. Assuming we have a dataset (B4:D10) of customers with their purchased products and amounts. We are going to use the COUNTIFS function in the Conditional Formatting feature to find duplicate rows from multiple columns.

Excel COUNTIFS Function for Finding Duplicate Rows Based on Multiple Columns

STEPS:

  • First, select the cell range.
  • Next, go to the Home tab and select the Conditional Formatting drop-down.
  • Click on the New Rule option.

Excel COUNTIFS Function for Finding Duplicate Rows Based on Multiple Columns

  • A New Formatting Rule window pops up.
  • Now select a rule type ‘Use a formula to determine which cells to format.
  • In the formula box, type the formula:
=COUNTIFS($B$5:$B$10,$B5,$C$5:$C$10,$C5,$D$5:$D$10,$D5)>1
  • Then select Format.

  • A Format Cells window opens here.
  • Go to the Fill option.
  • After that, from the Background Color group, select the color. We can see the sample of color in the Sample box.
  • Click OK.

  • Again, click OK.
  • Finally, we can see that all the duplicate rows are highlighted with the color we’ve selected before.

Read more: How to Find Duplicate Rows in Excel


2. Finding Duplicate Rows by Excel IF Function Based on Multiple Columns

IF function helps us by returning one value for a TRUE result and another value for a FALSE result. We can use this function with the ISERROR function & MATCH function to find duplicate rows between two columns. Suppose we have two columns of products containing the duplicate product names, LIST 1 (B4:B9) & LIST 2 (D4:D8).

Finding Duplicate Rows by Excel IF Function Based on Multiple Columns

STEPS:

  • Select Cell F5 at first.
  • Now write the formula:
=IF(ISERROR(MATCH(B5,$D$5:$D$8,0)),"",B5)

  • Then hit Enter and use the Fill Handle tool to autofill the next cells.

🔎 How Does the Formula Work?

  • MATCH(B5,$D$5:$D$8,0): This will return the position of Cell B5.
  • ISERROR(MATCH(B5,$D$5:$D$8,0)): This will return the TRUE or FALSE value based on the presence of an error.
  • IF(ISERROR(MATCH(B5,$D$5:$D$8,0)),””,B5): This will display the value if it meets the above criteria otherwise leave the cell blank.

Read more: How to Find Repeated Cells in Excel


3. Array Formula to Find Duplicate Rows Based on Multiple Columns in Excel

Array Formula helps us to do multiple calculations at once on one or more ranges. Let’s say we have multiple columns of products containing the duplicate product names, LIST 1 (B4:B9), LIST 2 (C4:C8) & LIST 3 (D4:D8). Here we are going to apply an array formula with Excel CONCATENATE function, COUNTIF function & IF function to find duplicate rows.

Array Formula to Find Duplicate Rows Based on Multiple Columns in Excel

STEPS:

  • In the beginning, select Cell E5.
  • Next, type the formula:
=CONCATENATE(B5,C5,D5)

Array Formula to Find Duplicate Rows Based on Multiple Columns in Excel

  • Hit Enter and use the Fill Handle to see the bellow result.

Array Formula to Find Duplicate Rows Based on Multiple Columns in Excel

  • Now select Cell F5.
  • Write down the formula:
=IF(COUNTIF($E$5:$E$9,E5)=1,0,1)

Array Formula to Find Duplicate Rows Based on Multiple Columns in Excel

  • Next, press Enter and use the Fill Handle tool to the below cells.

  • Then select Cell G5.
  • Type the formula:
=IF(F5>0,"Duplicate","N/A")

  • In the end, hit Enter and use the Fill Handle tool to see the result.

🔎 How Do the Formulas Work?

  • CONCATENATE(B5,C5,D5): This will combine the text of cells B5, C5 & D5.
  • IF(COUNTIF($E$5:$E$9,E5)=1,0,1): The COUNTIF function will count the number of cells from the range E5:E9 for the cell E5. And the IF function will return the value ‘0’ if it’s TRUE and ‘1’ if it’s FALSE.
  • IF(F5>0,”Duplicate”,”N/A”): This will return “Duplicate” if cell F5 is greater than ‘0’ and “N/A” if it’s not.

Read More: How to Find Repeated Numbers in Excel


4. Excel Finding Duplicate by Conditional Formatting Based on Multiple Columns in Excel

Conditional Formatting is an Excel built-in feature. It’s an important feature to make our work easier. We can use this to find duplicate values based on multiple columns. Here we have a dataset (B4:D10) of customers with their purchased products and amounts.

Excel Finding Duplicate by Conditional Formatting Based on Multiple Columns in Excel

STEPS:

  • First, select the dataset.
  • Now go to the Home tab and click on the Conditional Formatting drop-down.
  • Then go to the Highlighted Cells Rules group and select Duplicate Values.

Excel Finding Duplicate by Conditional Formatting Based on Multiple Columns in Excel

  • The Duplicate Values message box pops up.
  • After that, from the drop-down, select the color which will indicate the duplicate cells.
  • Click OK.

  • Finally, we can see all the duplicate rows in yellow filled with the dark yellow text.

Read more: How to Filter Duplicates in Excel


5. Using Advanced Filter Feature to Find Duplicate Rows Based on Multiple Columns in Excel

To filter a dataset, we can use the Advanced Filter feature in Excel. In the below dataset (B4:D10) of customers with their purchased products and amounts, we are going to use the Advanced Filter feature to find the dataset without duplicate rows.

Using Advanced Filter Feature to Find Duplicate Rows Based on Multiple Columns in Excel

STEPS:

  • Select the cell range at first.
  • Then go to the Data tab.
  • Now from the Sort & Filter group, select Advanced.

Using Advanced Filter Feature to Find Duplicate Rows Based on Multiple Columns in Excel

  • An Advanced Filter window pops up.
  • After that, check the box ‘Copy to another location.’
  • Make sure the List range is already input with the data range.
  • Next, select the cell reference in the Copy to box, where we want to see the duplicate rows. Here we input Cell F5.
  • Tick on the ‘Unique records only’ option.
  • Select OK.

Using Advanced Filter Feature to Find Duplicate Rows Based on Multiple Columns in Excel

  • Finally, we can see the dataset without duplicate rows in the range E5:H9.

Read More: How to Compare Rows for Duplicates in Excel


6. Excel VBA for Finding Duplicate Rows Based on Multiple Columns

We can easily find duplicate rows from two columns by using Excel Visual Basic for Application code. Assuming we have two columns of products, LIST 1 (B4:B9) & LIST 2 (D4:D7). We are going to find duplicate rows from LIST 1 column based on the LIST 2 column.

Excel VBA for Finding Duplicate Rows Based on Multiple Columns

STEPS:

  • First, select the active worksheet from the sheet bar and right-click on it.
  • Click on the View Code option.

Excel VBA for Finding Duplicate Rows Based on Multiple Columns

  • A VBA Module window opens here. We can also open it by pressing the ‘Alt + F11’ keys. Then click on Insert > Module.
  • Next type the code:
Sub DuplicateRows()
Dim Rng1 As Range, Rng2 As Range, R1 As Range, R2 As Range, outRng As Range
xTitleId = "DuplicateRowsInExcel"
Set Rng1 = Application.Selection
Set Rng1 = Application.InputBox("Rng1 :", xTitleId, Rng1.Address, Type:=8)
Set Rng2 = Application.InputBox("Rng2:", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each R1 In Rng1
xValue = R1.Value
For Each R2 In Rng2
If xValue = R2.Value Then
If outRng Is Nothing Then
Set outRng = R1
Else
Set outRng = Application.Union(outRng, R1)
End If
End If
Next
Next
outRng.Select
Application.ScreenUpdating = True
End Sub
  • Click on the Run option or press the F5 key.

Excel VBA for Finding Duplicate Rows Based on Multiple Columns

  • A Macros confirmation box pops up. Select the sheet and click on Run.

Excel VBA for Finding Duplicate Rows Based on Multiple Columns

  • Now we can see the DuplicateRowsInExcel message box. Here input the Rng1, in which we will highlight the duplicate rows from the worksheet.
  • Then select OK.

  • Another DuplicateRowsInExcel message box pops up. Select the Rng2 which will use as the finding column.
  • After that, click on OK.

  • Finally, we can see the duplicate rows are highlighted in the LIST 1 column.

Read More: How to Compare Two Excel Sheets for Duplicates


Practice Workbook

Download the following workbook and exercise.


Conclusion

By using these ways, we can easily find duplicate rows based on multiple columns in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings


<< Go Back to Find Duplicates in Excel Column | Find Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo