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.
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.
- 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).
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.
STEPS:
- In the beginning, select Cell E5.
- Next, type the formula:
=CONCATENATE(B5,C5,D5)
- Hit Enter and use the Fill Handle to see the bellow result.
- Now select Cell F5.
- Write down the formula:
=IF(COUNTIF($E$5:$E$9,E5)=1,0,1)
- 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.
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.
- 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.
STEPS:
- Select the cell range at first.
- Then go to the Data tab.
- Now from the Sort & Filter group, select Advanced.
- 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.
- 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.
STEPS:
- First, select the active worksheet from the sheet bar and right-click on it.
- Click on the View Code option.
- 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.
- A Macros confirmation box pops up. Select the sheet and click on Run.
- 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.
