How to filter duplicate rows in Google Sheets

In this post, we’re going to look at how we can identify duplicate rows and also extract both unique rows and duplicate ones using a single formula.

We’re going to look at different ways to do this and how we can use one formula which will extract a list of duplicate rows and and also sort them.

Using a COUNTIF formula to identify duplicate rows

Below we want check for duplicates in the email column C.

We can do that by using the following formula. We enter it into cell E2 and then copy it down the rows in column E.

=IF(COUNTIF(C2:C20, C2)>1, “Duplicate”, “Unique”)

This looks in the range in column C, then counts how many times the value in cell C2 appears in that range. This will return a number. Then the IF statement will check if that number is more than 1, then it’ll add the text “Duplicate”, if it’s 1 then it adds “Unique”.

The formulas in the other rows, do the same for the other cells in column C, i.e. C3, C4, etc.

As you can see it’s identified the rows with the duplicate emails.

Using BYROW and LAMBDA to identify duplicate rows

The above formula is a simple way to identify the duplicates, but we can achieve the same with a single formula in a single cell. This mean we don’t have to copy the formula down to the other rows, and if we have a large dataset will help the sheet run quicker.

This time we write the following formula in cell F1.

=BYROW(C2:C20, LAMBDA(c, IF(COUNTIF(C2:c20, c)>1, “duplicate”, “Unique”)))

The part on the right works in the same way as I’ve described above but you will see that rather than stating a specific cell, like C2, we’re using “c”.

This comes from the LAMBDA function which will represent the cells in column C. We also use BYROW, which will state the range we’ll looking at.

So, basically, this is applying the IF and COUNTIF formula to each of the cells in column C, without having to enter a formula in cells C3 to C20. As we can see it produces the same result.

How to produce a list of unique results using FILTER

We can take the above one step further by creating a separate list which shows just the unique email addresses.

In cell G1, we write the following formula:

=FILTER(A2:D20,E2:E20=”Unique”)

This uses the FILTER function, and here we state the range to be filtered, then the condition to be met. Here, it will display columns A to D, where the value in column E is “Unique”. As you can see, we have the list of unique values.

How to produce a list of duplicate rows using FILTER

We can also do the opposite, and return a list of the duplicate rows.

Here, all we need to do is change the condition to return those cells in column E that have “Duplicate”.

=FILTER(A2:D20,E2:E20=”Duplicate”)

As we can see, it returns the rows with duplicated email addresses.

How to return a list of unique rows with one single formula

In the above examples, we needed a helper column, i.e. the one stating if the email was unique or a duplicate, to filter the data, but we can do it all with one single formula.

In cell F1, we write the following formula. It’s a combination of what we’ve been looking at so far.

=FILTER(A2:D20,BYROW(C2:C20, LAMBDA(c,COUNTIF(C2:C20,c)=1)))

We look at column C and see if any have a count of 1, if they do we then return columns A to D with the rows that match 1.

How to return a list of duplicate rows with one single formula

Here, we’re going to modify the formula above so that it returns the duplicate rows, and to make it easier to read, we’re also going to sort the results.

In cell K2, we write the following formula:

=SORT(FILTER(A2:D20,BYROW(C2:C20,LAMBDA(c,COUNTIF(C2:C20,c)>1))),3,TRUE)

This time we’re check to see if the number of times the emails appear is more than 1, i.e. a duplicate. We then wrap the results up in a SORT function, to sort it by the third column, so the duplicates are grouped by email.

With just one formula we can can extract a list of unique rows or a list of duplicate ones.

Check out my post on how to quickly remove duplicate rows.

Also, check out my post on how to remove and separate duplicate rows using Apps Script.

Learn more about more advanced Google Sheets functions like BYROW and LAMBDA in my book “Google Sheets Functions 2“.