Conditional Formatting | IF Function | Adjacent Multiple Columns | Multiple Columns | Vlookup Match
When working with a large database, you may find many duplicates in one column or multiple columns. To identify and remove duplicates is crucial in your data cleaning and analysis. The following is the method to find and remove duplicates in two columns. Please refer to the following two posts if you need to find the duplicates in one or multiple columns.
– How to Find Duplicates in One Column
– How to Find Duplicates in Multiple Columns
1. Conditional Formatting to Find Duplicates in Two Columns
Suppose to find the duplicate values in column A and column B in the following example, and format the duplicate values in column A if duplicates are in column A and column B.
Step 1: Highlight the column A;
Step 2: Go to Home Tab, click "Conditional Formatting" in the Styles section, then click "New Rule…";
Step 3: In the "New Formatting Rule" window, select "Use a formula to determine which cells to format"; then in the "Format values where this formula is true" box, click cell A2, then type =, then click B2".
Step 4: Change absolute value to relative value: change formula "=$A$2=$B$2" to "=A2=B2", then click "Format";
Step 5: In the "New Formatting Rule" window, you can select Font Color, or select cell color in the Fill Window.
Step 6: Click OK, the color of duplicate values in column A will be changed.
Step 7: If you need to find the unique value, please change formula =A2=B2 to =A2<>B2.
2. How to Use IF Function Find Duplicates in Two Columns
Step 1: Create a Helper Column, and type the formula =IF(A2=B2,"Duplicate","No Duplicate") in the first cell;
Step 2: Copy down to the bottom, the rows with duplicates in column A and column B will display "Duplicate" and non-duplicates will display "No Duplicate".
Step 3: Please note that the above formula is NOT case sensitive (e.g., Jason is the same jason). If you need to find values with exact match with case sensitive, you can use formula =IF(EXACT(A2,B2),"Duplicate","No Duplicate")
3. How to Use IF Function to Find Duplicates in Multiple Adjacent Columns
Suppose you have 4 columns of data and you need to find the duplicates when any two adjacent values in a row are the same.
Step 1: Create a Helper Column, and type the formula =IF(OR(A2=B2,B2=C2,C2=D2), "Duplicate","No Duplicate") in the first cell;
Step 2: Copy down to the bottom, the rows with duplicates in any two adjacent columns will display "Duplicate" and non-duplicates will display "No Duplicate".
4. How to Use IF Function to Find Duplicates in any Two Columns
Suppose you have 4 columns of data and you need to find the duplicates when any two values in a row are the same.
Step 1: Create a Helper Column, and type the formula =if(or(a2=b2,b2=c2,c2=d2,a2=c2,a2=d2,b2=d2),"Duplicate","No Duplicate") in the first cell;
Step 2: Copy down to the bottom, the rows with duplicates in any two columns will display "Duplicate" and non-duplicates will display "No Duplicate".
Note: to find values in one column but not in the second column, please refer to the VLOOKUP Function.