How to Find Duplicates in Google Sheets Using Dynamic Formula

0
126
Find Duplicates in Google Sheets Using Formula

You can use formula to find duplicates in Google Sheets. There are three different approaches in finding duplicate words, text or rows in Google Sheets using Formula. I will explain you all the three different approaches with examples below.

The Three Approaches to Find Duplicates in Google Sheets Using Formula

  1. Using UNIQUE Function.
  2. With the Help of FILTER Function.
  3. Using CONCATENATE and IF Logical (Dynamic Formula) in Combined Form or using CONCATENATE and SWITCH formula in Combined Form.

Before going to the examples, I will explain you how can we use these three functions to find duplicate words, text or rows in Google Sheets. In the above three methods, each one of them produce different outputs.

1. UNIQUE Formula

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

2. Filter Formula

The second option is using the FILTER function. This approach to find duplicate is entirely different and just opposite to the above UNIQUE formula method. Here you can apply filter to a range by providing certain conditions. It will return the content matching your specified condition. This method is little more flexible than the above.

3. Dynamic Formula

a) By Using CONCATENATE, ARRAYFORMULA and IF Logical Function

This is probably new to you. With this combination of formula, you can easily check words or text in a cell or rows and mark rows with duplicate content. With this formula, you can check or verify any rows for duplicate.

b) By Using CONCATENATE, ARRAYFORMULA and SWITCH Function

This is just same as above CONCATENATE, ARRAYFORMULA and IF Logical Function approach.

Examples to Find Duplicates in Google Sheets Using Formula

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

sample data for finding duplicate with formula

1. Find Duplicates in Google Sheets Using UNIQUE Formula

Find duplicate words, text, or rows in spreadsheets using UNIQUE formula. This is a straightforward approach. As told above, it’s not finding duplicates, instead it’s removing duplicate and returns unique content.

In any cell other than the above data range, apply the UNIQUE formula as below. Make sure that there are sufficient rows 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. I just applied the formula in Cell D15.

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 rows with duplicate.

Note: The UNIQUE formula checks entire row for duplicate. Not a single cell or word. Also you can limit UNIQUE formula to any singly column. But the result will also from that column only. Here scores the below filter function. You can find a more detailed tutorial on duplicate removal using UNIQUE formula HERE.

2. Filter Formula to Find Duplicates in Google Sheets

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

In the below formula, D2:F11 is the entire data range. The formula checks the presence of “Agatha Christie” under column D, i.e., “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 returns 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 filter as below to bring more uniqueness.

=filter(D2:F11,D2:D11=”Agatha Christie”,E2:E11=”A Pocket Full of Rye”,F2:F11=1953)

I have an advanced tutorial on Google Sheets Filter Formula HERE.

3. Finally How to Find Duplicates in Google Sheets Using Dynamic Formula

a) CONCATENATE, ARRAYFORMULA and IF Logical Function

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

How to Find Duplicates in Google Sheets Using Dynamic Formula

See column G above, where you can see duplicate rows 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”,“”),“”))

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, simply copy and paste the above formula to that row as below and remove the first formula. Removing the first formula is important. Otherwise it can return error.

Dynamic Duplicate Removal Formula in Google Sheets

How to Create the above Dynamic Formula to Find Duplicates in Google Sheets

The following is the formula which we have used for the above dynamic duplicate identification method.

=ArrayFormula(if(len(D2:D),if((D2:D&E2:E&F2:F)=CONCATENATE(D2,E2,F2),”Yes”,””),””))

It’s actually a modified form of the simple formula below.

=if((D2&E2&F2)=CONCATENATE(D2,E2,F2),”Yes”,””)

With this simple formula, I’ve added ARRAYFORMULA to expand the result to below rows. Also I’ve used the LEN Google Sheet function to limit the array expansion. If you are not familiar with using ARRAYFORMULA or LEN, please go to our related tutorials. Check our Google Sheets Function Guide to pick the formula you want to learn. So I am only explaining the above simple formula here.

This part of the formula “D2&E2&F2“, add the content of the cells together. So the result will be like this “Agatha ChristieA Pocket Full of Rye1953”.

The formula “CONCATENATE(D2,E2,F2)” is same as “D2&E2&F2”. This also returns “Agatha ChristieA Pocket Full of Rye1953”. Then why I used two different methods to join text, text and number?

Note: You can find how to join cell content using CONCATENATE or “&” HERE.

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

The Logic: What I am doing here is, I am checking the combined value of D2,E2 and F2 with combined valued of D2,E2 and F2 in the same row and rows down. That means I am checking if “Agatha ChristieA Pocket Full of Rye1953 = Agatha ChristieA Pocket Full of Rye1953” in same row and below rows. If this matches in any rows, it returns “Yes”. In the first row where the formula is, it’s of course a match!

b) CONCATENATE, ARRAYFORMULA and SWITCH Function

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, I am going to use Google 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”,” “))

The logic and basic fundamentals in developing this formula are same as the above dynamic formula with IF. Only the difference is in the usage of SWITCH function over IF. So learn SWITCH function first to understand this formula.

Switch Formula to Find Duplicates

Hope you enjoyed this ultimate duplicate removal and data clean up tips.

LEAVE A REPLY

Please enter your comment!
Please enter your name here