Learn how to find and eliminate duplicates using Query formula in Google Sheets. I’ve already shared a few awesome tips to find and eliminate duplicates in Google Doc Spreadsheets.

To eliminate duplicates the normal way is using the UNIQUE formula. But it has a shortfall. You can’t use the Unique based duplicate removal formula in all the circumstances.

See the below image. You can clearly understand that the UNIQUE function can’t do a certain type of duplicate elimination in Google Sheets.

Here comes the QUERY function, the killer of many other spreadsheet functions, handy. You can use the Query function for different types of data manipulation techniques.

With Query, you can eliminate duplicates in 2-3 columns while keeping other columns with numbers summed.

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

Let us see how the UNIQUE function treats duplicates.

Here on the left-hand side of the above image contains 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 and as well as in Column D summed.

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

Here the QUERY formula not only eliminates the duplicates but also keeps the values in the adjacent columns summed.

Note: If you don’t want the values to SUM, you can use SORTN. Go to this tutorial to learn more.

So you are not losing any corresponding values with duplicate elimination. Query formula can group and sum the rows containing duplicates.

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 “Yes” That Contain Duplicates in Google Sheets

If you find this interesting, below is the link.

You May Like: Find Duplicates in Google Sheets Using Dynamic Formula

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

Hope you are all ready 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.

Must Read: Learn Google Sheets Query.

As I’ve told you above, we can use the Query function to eliminate duplicates while retaining some columns.

The 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 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.

In the above screenshot, the first row has a duplicate in the 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.

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!

Related:

1. The ultimate duplicate removal tips in Google Spreadsheets.
2. How to Use Remove Duplicates Menu Command in Google Sheets.
3. Data Validation – How Not to Allow Duplicates in Google Sheets.