HomeGoogle DocsSpreadsheetHow to Find Duplicates in Google Sheets Using Dynamic Formula

How to Find Duplicates in Google Sheets Using Dynamic Formula

Published on

You can follow a formula based approach to find duplicates in Google Sheets. There are three different approaches to find duplicate words/text strings or duplicate rows in Google Sheets.

I will try to explain to you all the three different approaches for finding duplicates in Sheets with examples below. Some of them are for extracting uniques means for removing duplicates.

The Three Formula Approaches to Find Duplicates in Google Sheets

  1. Using the UNIQUE function – Extracting unique values.
  2. With the help of the FILTER function – For testing duplicates.
  3. Using CONCATENATE and IF logical (dynamic formula) in combined form – This is for finding/marking a single value in multiple rows.
    1. COUNTIFS – This is for finding/marking multiple values in multiple rows (a killer formula).

Before going to the examples, let me explain to you how can we use these three approaches to find duplicate words, text or rows in Google Sheets.

In the above three methods, each one of them produces different outputs.

UNIQUE Formula – For Extracting Unique Values

If you follow the UNIQUE function method to find duplicates, you will be a little bit dissatisfied. The reason, this formula is not actually finding duplicates. It returns the unique rows after removing duplicates.

Filter Formula – For Filtering Specific Duplicates

This approach for finding duplicate values is entirely different. Here you can apply a filter to a range by providing certain conditions.

The formula will return the content matching your specified condition. For example, you can filter a string “sports” to get the rows containing the string “sports”.

Dynamic Formula for Finding Duplicates in Google Sheets

A Combination of CONCATENATE, ARRAYFORMULA and IF Logical Function

Mark the Duplicates of a Single Value in all the Rows

This may probably new to you. With this combination formula, you can easily check a string in a cell or strings in a row and mark all the rows that contain that string/strings. With this formula, you can check or verify any rows for the duplicate.

You can rewrite the above combination with CONCATENATE, ARRAYFORMULA and SWITCH Function.

Countifs for Finding/Marking All Duplicates

Mark the Duplicates of Multiple Values in all the Rows: You can read more about this at the example section below.

Examples – Find Duplicates in Google Sheets Using Formulas

Below is the sample data for the examples. In this sample data set, you can clearly see that there are multiple rows with the same content.

sample data for finding duplicate with formula

Find Duplicates in Google Sheets Using UNIQUE Formula

Find duplicate words, text strings, or duplicate rows in spreadsheets using the UNIQUE formula. This is a straightforward approach.

As told above, it’s not for finding duplicates, instead, it’s for removing duplicates and returning the unique content.

In any cell other than the above data range (use cell D15), apply the UNIQUE formula as below. Make sure that there are sufficient rows and columns to expand the result.

=UNIQUE(D1:F11)

Here D1:F11 is the entire data range. The result will be as follows after omitting the duplicates.

unique formula to remove duplicates in Google Doc

When you check the sample data, you can see that one book of Agatha Christie, “A Pocket Full of Rye”, repeats five times.

So the UNIQUE formula will return only one row from that. It’s applicable to all other books.

The UNIQUE formula checks entires rows in the range for duplicates. Not a single column in the range. For example, the values in D2:F2 is matching in any other rows.

You can limit the UNIQUE formula to any single column. But the result will also from that column only.

Example:

=unique(E2:E11)

This formula will return the unique books. When you want to unique only a single column like E2:E11 in Unique but want the result from all the columns D2:F11, you can use SORTN.

Replace the above Unique formula with the following SORTN. To know more read this – SORTN Tie Modes in Google Sheets.

=sortn(D2:F11,9^9,2,E2:E11,false)
SORTN and UNIQUE approaches in finding duplicates

Filter Formula to Find Duplicates in Google Sheets

This is a different approach. With FILTER formula, you can check the appearance of duplicate words, text, or even an entire row for duplicates.

In the below formula, D2:F11 is the entire data range. The formula checks the presence of “Agatha Christie” under column D, i.e. the range D2:D11, and returns matching rows.

=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE")
Filter Formula to Find Duplicates

The following FILTER formula will check Column D, i.e., range D2:D11, for “Agatha Christie” and Column E, i.e., range E2:E11, for “A Pocket Full of Rye” and will return the matching rows.

=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE",E2:E11="A POCKET FULL OF RYE")
Another example of Filter to Find Duplicates

You can further narrow down the result as below for bringing more uniqueness.

=FILTER(D2:F11,D2:D11="AGATHA CHRISTIE",E2:E11="A POCKET FULL OF RYE",F2:F11=1953)

How to Find Duplicates in Google Sheets Using Dynamic Formula

CONCATENATE, ARRAYFORMULA and IF Logical Function – To Mark One Duplicate in Entire Rows

Here the approach to find duplicate words, text, numeric values or entire rows is different. See the below image.

How to Find Duplicates in Google Sheets Using Dynamic Formula

See column G where you can see duplicate rows corresponding to the first row (D2:F2) marked as “Yes”. How?

I’ve applied one formula in Cell G2 as below.

=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE(D2,E2,F2),"YES",""),""))

or

=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE("Agatha Christie","A Pocket Full of Rye",1953),"YES",""),""))

I will explain the formula part later. Now let us see what this formula does.

This dynamic formula checks Column D2, E2, and F2 and finds similar content in below rows.

That means this formula checks the repetition of rows with the content; author “Agatha Christie”, Book Name “A Pocket Full of Rye” and year 1953. If found, it returns “Yes” against that row.

When you want to check another author for duplicate, for example, the row D4:F4, modify the cell reference inside the Concatenate as below.

=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE(D4,E4,F4),"YES",""),""))

or

=ARRAYFORMULA(IF(LEN(D2:D),IF((D2:D&E2:E&F2:F)=CONCATENATE("Bram Stocker","Dracula",1897),"YES",""),""))
How to Create the above Dynamic Formula to Find Duplicates in Google Sheets?

The above formula is actually a modified form of the simple formula below. This formula you can use in cell G2 and copy down.

=IF((D2&E2&F2)=CONCATENATE($D$2,$E$2,$F$2),"YES","")

With this simple formula, I’ve added ARRAYFORMULA to expand the result to below rows. So I can avoid copying down the formula. Also changed D2&E2&F2 to D2:D&E2:E&F2:F.

Additionally, I’ve used the LEN Google Sheet function to limit the array expansion.

The formula CONCATENATE(D2,E2,F2) is the same as D2&E2&F2. Both will return “Agatha ChristieA Pocket Full of Rye1953”.

Of course, I can use D2&E2&F2 = D2&E2&F2 instead of D2&E2&F2 = CONCATENATE(D2,E2,F2). But it may conflict with the ARRAYFORMULA. That’s the reason I’ve followed two different methods to obtain the same result.

CONCATENATE, ARRAYFORMULA and SWITCH FunctionTo Mark One Duplicate in Entire Rows

You can use Google Sheets SWITCH function to find duplicates in Google Doc Spreadsheet.

This is just similar to the above CONCATENATE, ARRAYFORMULA and IF Logical Function method.

The difference here is the use of Sheets SWITCH function instead of IF Logical.

Here is that magical formula to find duplicates. It’s very simple to understand if you know the use of SWITCH formula.

=ARRAYFORMULA(SWITCH((D2:D11&E2:E11&F2:F11),"AGATHA CHRISTIEA POCKET FULL OF RYE1953","YES"," "))
Switch Formula to Find Duplicates

Countifs – The Killer Formula to Mark Multiple Duplicates in Entire Rows

Countifs is the simplest way and seems the most favorite function of all Google Sheets users to identify duplicates.

=ARRAYFORMULA(COUNTIFS(D2:D11&E2:E11&F2:F11,D2:D11&E2:E11&F2:F11,ROW(A2:A11),"<="&ROW(A2:A11)))

The above formula has one resemblance with the =unique(D2:F11) formula. What’s that?

Unique and running count resemblance in Sheets

Wherever the Countifs return the number 1 in column H, that is the unique rows. Other rows are duplicates.

As a side note, the above formula is actually a running count formula.

The =unique(D2:D11) formula output can be marked using the running count=ARRAYFORMULA(COUNTIFS(D2:D11,D2:D11,ROW(A2:A11),"<="&ROW(A2:A11))) formula.

Hope you enjoyed this ultimate finding/marking/removing duplicates and data clean-up tips.

Also, Check:

  1. Remove Duplicates in Google Sheets [The Complete Guide and Sample Sheet].
  2. Google Sheets: Find All Duplicates in a Column and Delete the Row.
  3. How to Find Multiple Occurrences of Rows in Google Sheets [Duplicates].
  4. Remove Duplicate Rows Based on Selected Columns in Google Sheets.
  5. Find and Eliminate Duplicates Using Query Formula in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

16 COMMENTS

  1. I used the countifs formula and it works great. The only issue I am having is in some of the rows a date is showing up rather than a number of duplicates.

    Can you tell me why this may be happening and how to change that?

    For example, A1-A10 shows a 1 or 2 but A11 shows 12/31/99. That same date is showing up on multiple rows.

    • Hi, Ashley,

      Blank cells have a date value of 12/30/1899. That means if you format the number 0 to date, you will get this date. Similarly, 1 is equal to 12/31/1899.

      To solve the issue, select that cells or entire column and go to the menu Format > Number > Number.

      Best,

  2. I need to highlight fields that have duplicates by keywords.

    For example, I have a list of companies that are provided by a data company and we call them to offer our software. But there is the occasional company on the list who is already a customer but we cannot always tell from the data that has been provided and we paste a list of our customers to the end of the list of potential new ones.

    This list might say ‘Company’ but our pasted list of company names might list the same company as ‘Company Limited’.

    The =countif(A:A,A1)>1 formatting doesn’t highlight this – it only works for exact duplicate values. Any ideas as to how I can highlight all duplicates, regardless of the exact value?

    • Hi, Louis,

      Formulas vary depending on your problem. You can use the filter based formula across sheets.

      Also, I have given links to different duplicate removal tutorials at the end. In that, the point # 1 covers different duplicate removal codes.

      • Thanks for your reply,

        I might be getting this wrong but from what I can tell the filter formula takes info from tab 1 and creates a separate tab 2,3,4 and so on and puts the filtered information into the different tabs right? I got this from the example of age groups.

        My problem is slightly different. My tab 1 is already full on entries and I am now filling up tab 2 with entries however I need to make sure that if I add something to tab 2 that is a duplicate of an entry in tab 1 then I need it to flag up.

        • Hi Louis,

          I have plenty of tutorials on this page related remove duplicates in Google Sheets. But I am unsure whether I have a specific tutorial addressing your issue. So if possible, create a demo sheet and share it with me. Please don’t share a cluttered sheet with full of formulas and several rows of data.

          Thanks.

  3. What if I want to grab selected columns from the duplicate data and then display on another sheet?

    Example: Column H has an e-mail address where I’m checking for duplicates but Columns A:B and X have the data that I want to display on another sheet.

    • Hi, Crush,

      We can easily do that using either Query or SORTN. Please make a sample sheet and share it with me. So that I can have a look at your problem and give you a proper solution.

      Include your expecting result in that sheet that you can manually enter.

    • Hi, X Cheung,

      There are many formulas in the example. I don’t know which one you are talking about.

      If your data is in A2: A, apply the below formula in B2. Wich marks the cells if the corresponding value in Column A has a duplicate.

      =ArrayFormula(if(len(A1:A),(if((countif(A2:A,A2:A))>1,"duplicate",)),))

      There are many methods and that depends on your dataset. I have given many links to duplicate removal at the end of the post. Maybe a link to your demo sheet can be useful for me to understand your problem and come up with a solution when I am free.

      Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here