Check Duplicates By Combinations Of Cells In Excel

In this post we will see how we could find if there are duplicates by combinations of cells in excel.

There are situations when we need to check for duplicates in excel by multiple column values, suppose we have two columns with first name and last name and duplicate names are not allowed.

In this case we will have to check for duplicates by a combination of two cells of two columns in excel.

In the below example you could see we have two columns with First Name and Last Name.

We will see two ways of checking duplicates in excel by a combination of cells/multiple cells.

First Way of checking duplicates by a combination of cells in excel.

It’s a very simple way to check the duplicates by multiple cells, first concatenate all the cells for which you want to check the duplicate for into single cell by using concatenation operator “&”.

In our case the number of columns is two, so we will concatenate these two columns in column C.

Enter the formula =A2&B2 in cell C1, remember if we were to check duplicates by more than 2 cell combinations also, we would concatenate all of them.

Now drag this formula till row 5 as we have data available till row number 5.

Now we will create a column called count in which we will calculate the frequency of each of the item in column C using COUNTIF.

So basically this countif formula is checking the count of each of the value in the entire range.

Drag this formula till row number 5.

As you could see “James” has a count of 2 as it appears twice in our data , so the row which has a count of more than 1 is duplicate and we could filter those records.

That was a basic method of checking duplicates by a combination of cells in excel.

Second Way of checking duplicates by a combination of cells in excel.

For the same case that was explained above, we will see another way by creating a column called Duplicate and entering the formula =IF(COUNTIFS(A:A,A2,B:B,B2) > 1,”Y”,”N”) in it as shown below.