How to Apply QUERY for Unique Rows in Google Sheets (4 Ways)

We often use the QUERY function in Google Sheets for database-like searching in a dataset and filtering data according to a required format. We may wonder if we can return a set of unique rows from a dataset using the QUERY function. The answer is a resounding Yes! In this article, I’ll demonstrate 4 helpful ways to return unique rows using the QUERY function in Google Sheets.


A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the discussed examples to return unique rows using the QUERY function in Google Sheets.


4 Helpful Ways to Apply QUERY Function for Unique Rows in Google Sheets

First, let’s get familiar with our dataset. The dataset contains a list of names and professions of a few people. As you can see, several rows have been repeated here. Now, we’ll use the QUERY function to return only the unique rows. So, let’s get started.

google sheets query unique


1. Combining QUERY and UNIQUE Functions

We can combine the UNIQUE function with the QUERY function to return the unique rows. The UNIQUE function can return a set of unique rows in the order in which they appear in the provided range. We’ll use the QUERY function to return any specified range for the UNIQUE function.

Steps:

  • Firstly, select Cell E5.
  • Afterward, type in the following formula-
=UNIQUE(QUERY(B5:C20,"SELECT B,C"))
  • Finally, press Enter key to get the required result.

Combining QUERY and UNIQUE functions to Return Unique Rows in Google Sheets

Formula Breakdown

  • QUERY(B5:C20,”SELECT B,C”)

First, the QUERY function runs a Google Visualization API Query Language query across the range B5:C20. The expression SELECT starts a query. Since we want to provide the entire range B5:C20, we have simply selected columns B and C using the QUERY function. You may run any special query to provide a specified range.

  • UNIQUE(QUERY(B5:C20,”SELECT B,C”))

Later, the UNIQUE function returns only the unique rows from the data range returned by the QUERY function.

Read More: How to Get Unique Values Without Blanks in Google Sheets


2. Merging QUERY and ARRAYFORMULA Functions

If we require the frequency count of each row in the data range using the QUERY function, then we have to use the ARRAYFORMULA function along with it. However, all the elements in a row will be merged in a single cell in this method.

Steps:

  • To start, activate Cell E5 by double-clicking on it.
  • Then insert the following formula-
=QUERY(ARRAYFORMULA(B5:B20&"-"&C5:C20),"SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)''")
  • After that, get the required output by pressing the Enter key.

Merging QUERY and ARRAYFORMULA Functions to Return Unique Rows in Google Sheets

Formula Breakdown

  • ARRAYFORMULA(B5:B20&”-“&C5:C20)

Firstly, the ARRAYFORMULA function merges the subsequent cells of range B5:B20 and C5:C20 with a delimiter “”.

  • QUERY(ARRAYFORMULA(B5:B20&”-“&C5:C20),”SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)””)

Now, the QUERY function runs a Google Visualization API Query Language query across the range returned by the ARRAYFORMULA function. The expression SELECT starts a query. Here the Col1 statement indicates the first column of the provided range. Since the range returned by the ARRAYFORMULA function has only one column, the query performed by the QUERY function will count the frequency of each row in the range and later return it for each row as a group.

Read More: How to Count Unique Values in Multiple Columns in Google Sheets


3. Uniting ARRAYFORMULA, SPLIT, and QUERY Functions

Now, if you don’t require the frequency count of each row in the data range, you can use the SPLIT function along with another ARRAYFORMULA and QUERY function to return the list of unique rows.

Steps:

  • Select Cell E5 first and then activate it by using function key F2.
  • Afterward, insert the following formula-
=ARRAYFORMULA(SPLIT(QUERY(QUERY(ARRAYFORMULA(B5:B20&"-"&C5:C20),"SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)''"),"Select Col1 where Col2<>0"),"-"))
  • Now, press Enter key to get the required output.

Formula Breakdown

  • ARRAYFORMULA(B5:B20&”-“&C5:C20)

First, the ARRAYFORMULA function merges the subsequent cells of range B5:B20 and C5:C20 with a delimiter “”.

  • QUERY(ARRAYFORMULA(B5:B20&”-“&C5:C20),”SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)””)

Afterward, this QUERY function runs a Google Visualization API Query Language query across the range returned by the ARRAYFORMULA function. The expression SELECT starts a query. Here the Col1 statement indicates the first column of the provided range. Since the range returned by the ARRAYFORMULA function has only one column, the query performed by the QUERY function will count the frequency of each row in the range and store it in a new column (Col2).

  • QUERY(QUERY(ARRAYFORMULA(B5:B20&”-“&C5:C20),”SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)””),”Select Col1 where Col2<>0″)

Now, this QUERY function will run another query to select and return the rows from Col1 for which the subsequent count is not 0 in Col2.

  • SPLIT(QUERY(QUERY(ARRAYFORMULA(B5:B20&”-“&C5:C20),”SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)””),”Select Col1 where Col2<>0″),”-“)

At this time, the SPLIT function divides the merged text around the delimiter “”.

  • ARRAYFORMULA(SPLIT(QUERY(QUERY(ARRAYFORMULA(B5:B20&”-“&C5:C20),”SELECT Col1, Count (Col1) Group by Col1 label Count(Col1)””),”Select Col1 where Col2<>0″),”-“))

Finally, this ARRAYFORMULA function helps the non-array function SPLIT to deal with an array.


4. Joining SORT, UNIQUE, and FLATTEN Functions

Sometimes, we require finding unique cells from multiple columns in Google Sheets. In such scenarios, we can combine the UNIQUE and FLATTEN functions to return the unique cells in a separate column. Here, we’ll return the unique project member names from the following dataset and sort them using the SORT function.

Steps:

  • Firstly, select Cell F5 and then insert the following formula-
=SORT(UNIQUE(FLATTEN(B6:D10)))
  • Finally, get the required output by pressing the Enter key.

Joining SORT, UNIQUE and FLATTEN Function to Query Unique Cells from Multiple Columns in Google Sheets

Formula Breakdown

  • FLATTEN(B6:D10)

First, the FLATTEN function returns all values from the entire range to a single column.

  • UNIQUE(FLATTEN(B6:D10)

Afterward, the UNIQUE function returns all the unique rows from the flattened range.

  • SORT(UNIQUE(FLATTEN(B6:D10)))

Finally, the SORT function returns a sorted list of unique names.


Alternative to Apply QUERY Function for Unique Rows in Google Sheets

Although the QUERY function can return unique rows from the entire range, the amount of work required for it is very tedious in some cases. So when you are searching for a simpler way then you can use the UNIQUE function.

Steps:

  • First, activate Cell E5 by double-clicking on it.
  • Now, insert the following formula-
=UNIQUE(B5:C20)
  • Consequently, press the Enter key to get the required output.


Things to Be Considered

  • All the elements of a row need to be merged into a single cell before we apply the QUERY function to count the frequency of each row in a data range.
  • The UNIQUE function requires a flattened range if we want it to return unique cells from multiple columns.

Conclusion

This concludes our article to learn how to return unique rows using the QUERY function in Google Sheets. I hope the demonstrated examples were ideal for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.


Related Articles

Seemanto Saha

Seemanto Saha

Hello, I am Seemanto Saha, and working as a Technical Writer and Content Creator at OfficeWheel.com. I completed my B.Sc. in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. Being an enthusiast for solving analytical problems, I aim to leverage my research and analytical skills to create better content for everyone.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo