Grouping a column is very easy than you think in Google Sheets. All you need to know is the basic use of Query Function. But sometimes, you may want to group a data based on the first few letters present in a column. So in this Google Sheets tutorial, we can learn the trick to group a column based on first few characters in Google Sheets.
While using any spreadsheet applications, the best way to get ideas flashed into your mind is learning functions as much as you can. Because we can use a combination or nesting of functions to achieve the results that we want.
Why I’m telling this is, for the above kind of grouping, that means to group a column based on the first few characters, requires a combination of functions. Here they are LEFT and QUERY.
Steps to Group a Column Based on First Few Characters in Google Sheets
As you know, a sample data is essential to start with a spreadsheet tutorial. So here it’s.
In this data set, what I want to do is to group the data based on the first two letters in Column A. We can learn it step by step.
Formula:
=query({ArrayFormula(left(A2:A6,2)),B2:F6},"Select Col1, Sum (Col4) group by Col1 label Col1 'Item Code', Sum (Col4) 'Total of Qty'")
Formula Explanation
Our original data is in the range A2: F6. But instead of that range as data in Query, I’ve used the below formula in Query as Data:
{ArrayFormula(left(A2:A6,2)),B2:F6}
See this Query Syntax:
Syntax:Â QUERY(data, query, [headers])
The above ArrayFormula is the key in this trick. What does it do?
From the above sample data set, you can clearly understand that our data range is spread across A2: F6. Instead of using this data range in the Query formula, I’ve used a virtual range created using Left, Arrayformula, and Curly Braces.
Similar:Â How to Create a Virtual Helper Column in Google Sheets?
Here we want to group a column based on the first few characters in Google Sheets. That column in our sample data is Column A, Range A2: A6.
You can use the LEFT function to extract the first two characters in a cell. But here we’ve to deal with an array or column range. So you should use the Left Function together with an ArrayFormula function as below.
Now we want the rest of the data range to be aligned with this extracted column. Then only we get a completely new data set to use in Query.
This we can easily do with Curly Braces.
I’ve used this combination of formula as a virtual data range in our master Query formula above.
The rest of the Query part is similar to the use of normal Query function. That means I’ve selected Column 1 then summed Column 4, i.e. “Qty. Recd.” based on grouping Column 1.
Hope you could understand how to group a column based on the first few characters in Google Sheets.
Apply the above tips with your own dataset to explore the possibilities offered by this virtual data range in Query. See you back again with another trendy Google Sheets tutorial.
