And Logical Function | Multiple Columns
When working with a large database, you may find duplicates in multiple columns. To identify and remove duplicates is important in your data cleaning and analysis. The following is the method to find and remove duplicates in multiple columns. Please refer to the following two posts if you need to find the duplicates in one or two columns.
– How to Find Duplicates in One Column
– How to Find Duplicates in Two Columns
1. How to Use And Function to Find Duplicates in Multiple Columns
Suppose you have 4 columns of data and you need to find the duplicates when all 4 values in a row are the same.
Step 1: Create a Helper Column, and type the formula =IF(AND(A2=B2,B2=C2,C2=D2), "Duplicate","No Duplicate") in the first cell;
Step 2: Copy down to the bottom, the rows with the same values will display "Duplicate", if not, will display "No Duplicate".
2. How to Use COUNTIF Function to Find Duplicates in Multiple Columns
Suppose you have 4 columns of data and you need to find the duplicates when all 4 values in a row are the same.
Step 1: Create a Helper Column, type the formula =COUNTIF(A2:D2,A2) in the first cell;
Step 2: Copy down to the bottom, each row returns the count of first cell. The duplicate returns the number of columns (e.g., 4 in this example).