Find Duplicate Row in Excel - Part 2

How to find duplicate value in excel?

In first part of this tutorial we see how to find duplicate record in one column. Sometime we need to find duplicate record in combination of two rows. One of the most use case is for table with composite key or say multiple primary key. Excel is very helpful to find duplicate record in multiple rows but we need to do some extra steps to make it easy. Lets see how we can we deal with this type of condition.

CASE 2: Find duplicate in a group of columns (Find duplicate country and capital)

If we follow the steps as CASE 1 we will get misleading result, so we need to follow different approach.
With Approach of CASE 1: 

1. select both columns
2. Go to Conditional Formatting
3. Go to Highlight Cells Rules
4. Click Duplicate Values...
5. Click OK

Result we get seems correct but if we see carefully we will find error. You will see result for Brazil is correct because in both row country is Brazil and Capital is Brasilia but for India it doesn't hold true. Because in one row India has New Delhi as capital where in other row India has Delhi as capital, both rows are different. But our approach shows incorrect result. 

To solve this problem, we need to take different approach as follows:

Step 1: Make a new column C by Merging column A and B by using & as =A2&B2. Now we will use this column to test duplicate row using CASE 1 approach.

Step 2: Select Column C.
Step 3: Select Conditional Formatting from Home tab in ribbon.
Step 4: Select Highlight Cells Rules.
Step 5: Select Duplicate Values...

Step 6: Click OK.

To view duplicate record use filter by color by doing following steps:

Step 1: select duplicate record and right click on that cell.
Step 2: select Filter from list.
Step 3: Select Filter by Selected Cell's Color

We will only get duplicate record which are duplicate in both column A and Column B.

Note: To clear formatting rules from entire sheet:

Step 1: Select conditional formatting in Home ribbon.
Step 2: Select Clear Rules.

Step 3: Clear rules from entire Sheet