Have you ever used the Group By clause in Google Sheets Query and noticed that it automatically sorts the output? While this is ideal in most cases, there are times when you may want to avoid this auto-sorting feature. Unfortunately, Google Sheets does not have a built-in clause to prevent auto-sorting in the Query Group By clause. But don’t worry, I have a clever workaround for you!
In this tutorial, I will address two important points related to Query:
How to Use the Query Group By Clause in Google Sheets
Before diving into the workaround, it’s important to understand how to properly use the Group By clause in the Query function. The Group By clause is used to aggregate values across rows and create a single row for each distinct combination of values (groups) in the Group By clause. Let’s take a look at a couple of examples:
-
Example 1: Single Column Grouping
=Query(A1:C7,"Select A, Sum(C) group by A")
In this example, the Query function groups the data in column A and calculates the sum of values in column C for each group. -
Example 2: Two Column Grouping
=Query(A1:C7,"Select A,B, Sum(C) group by A,B")
This example demonstrates grouping based on two columns, A and B, and calculating the sum of values in column C for each group.
Now that you have a basic understanding of how the Group By clause works, let’s move on to the main topic of avoiding auto sorting in the Query Group By clause.
How to Avoid Auto Sorting When Using Query Group By Clause
In order to demonstrate this workaround, let’s use a real-life example. We will import data from a table containing details of FIFA World Cup finals. To import the data, we will use the IMPORTHTML function. Here is the formula to import the table:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_FIFA_World_Cup_finals","table",3)
Once we have the data imported, we can proceed with the workaround. The main idea is to add a sequential number column to the grouping column and then use the SORT and SORTN functions to achieve the desired result.
Here are the steps involved in the workaround:
Step 1: Add a Sequential Number Column with the Grouping Column
To add sequential numbers to the data in the grouping column, you can use the following formula:
=ArrayFormula({B2:B22,row(B2:B22)})
This formula creates an array with the grouping column and the corresponding row numbers.
Step 2: Sorting of Grouping Column and Sequential Number Column
Sort the array created in the previous step in ascending order based on the grouping column and the sequential number column using the SORT function. Here is the formula:
=sort({B2:B22,row(B2:B22)},1,1,2,1)
Step 3: SORTN to Create a Single Row for Each Distinct Combination of Values
To create a single row for each distinct combination of values, we will use the SORTN function. This function removes duplicates and sorts the data in ascending order. Here is the formula:
=sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1)
Step 4: Extract the Sequential Number Column
Use the INDEX function to extract only the sequential number column from the SORTN output. Here is the formula:
={"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}
Step 5: Prepare the Data to Avoid Auto Sorting When Using Query Group By Clause
Combine the original data with the sequential number column using curly brackets. Here is the formula:
={query(B1:B22,"Select B, count(B) where B is not null group by B",1),{"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}}
Step 6: Final Step
In the final step, use the prepared data as input for another Query function. Use the Order By clause to sort the third column in ascending order and extract only the first and second columns. Here is the formula:
=Query({query(B1:B22,"Select B, count(B) where B is not null group by B",1),{"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}},"Select Col1,Col2 order by Col3 Asc")
That’s it! By following these steps, you can avoid auto sorting when using the Query Group By clause in Google Sheets. I hope this workaround helps you achieve the desired results without any hassle.
For more Google Sheets tips and tricks, visit Crawlan.com. Enjoy exploring the world of Google Sheets!
