Find and Eliminate Duplicates Using Query Formula in Google Sheets

Find and Eliminate Duplicates Using Query

Learn how to find and eliminate duplicates using Query formula in Google Sheets. I’ve already shared few awesome tips to find and eliminate duplicates in Google Doc Spreadsheets. The main topic was using the UNIQUE formula. But it has its deficiencies. You can’t use this formula in all circumstances. See the above featured image. You can clearly understand that UNIQUE function can’t do that type of duplicate elimination in Google Sheets. Here comes QUERY formula, the killer of many spreadsheet functions, handy. You can eliminate duplicates in 2-3 columns while keeping other columns with values intact.

Insights into Spreadsheet Duplicate Removal Using UNIQUE, QUERY, or by Using a Dynamic Formula

Let us see how the UNIQUE function treats duplicates.

Duplicate Removing Using UNIQUE a comparison with QUERY

Here on the left hand side of the above image resides the source data. From this it’s clear that one item is repeating multiple times. I’ve marked that above.

On the right hand side of the screenshot, you can see the unique values that after removing the duplicates. Here the problem is we can’t get the values in Column C as well as Column D.

See how QUERY handles this. We can find and eliminate duplicates using Query in the following way.

How query formula eliminates duplicates - example

Here QUERY formula not only eliminates the duplicates, but also keeps the values in other adjacent columns summed. So you are not losing any corresponding values with duplicate elimination. Query formula can sum the corresponding values in the rows where duplicates appear.

Before going to our tutorial, find and eliminate duplicates using Query, one more small tip. You can find and mark duplicates in corresponding rows using a dynamic formula as below. You can pick any one row and match other rows for duplicates.

Mark Rows with Duplicates in Google Sheets

trick to mark rows with duplicates in google sheets

If you seem interesting, below is the link.

Find Duplicates in Google Sheets Using Dynamic Formula

How to Find and Eliminate Duplicates Using Query Formula in Google Sheets

Hope you are already using Google Sheets Query Function. If not, please follow the below link to master it. Also a simple search on this site can bring more awesome QUERY tips and tricks.

Learn Google Sheets Query

As I’ve told you above, we can use Query function to eliminate duplicates while retaining some columns. The multiple group by clause in Query language is enabling us to do this. Here is the formula based on our sample range above.

=query(A2:D9,”Select A,B, sum(C), sum(D) group by A,B label (A) ‘Item Description’,(B) ‘Area’, SUM(C) ‘Qty’, SUM(D)’Amount'”)

This Query formula checks unique values in Column A and Column B. That means it removes duplicates from Column A and Column B.

How you can you use this Query formula for your use?

A2:D9 – data range. Change this range as per your data set.

Select A,B – We are checking unique values in these two columns.

What is unique rows? tips

In the above screenshot, the first row has a duplicate in second row. The third one is not a duplicate as the second column value is different.

You can add more columns to the formula like Select A,B,C. But you should make the same changes in the group by part of the formula like group by A,B,C and also label part. Hope you got it.

Here is a twist! You can use a combination of UNIQUE, SORT and QUERY to get the same above query formula result. Here is that formula for your experiment.

={{“Item Description”,”Area”;sort(unique(A2:B9))},query(A2:D9,”Select Sum(C),Sum(D) group by A,B label Sum(C)’Qty.’, Sum(D) ‘Amount'”)}

Thanks for the stay!


Please enter your comment!
Please enter your name here