How to Group a Column Based on First Few Characters in Google Sheets

0
157
How to Group a Column Based on First Few Characters in Google Sheets

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 some times, you may want to group a data based on 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 to thorough functions as much as you can. Because we can use 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 group a column based on first few characters, requires 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.

sample data to group by first two letters in Google Sheets

In this data set, what I want to do is, 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,Col2, Sum (Col4) group by Col1, Col2 label Col1 ‘Item Code’, Col2 ‘Product’, Sum (Col4) ‘Total of Qty'”)

Formula Explanation

See the red coloured part of the above formula. It’s the key in this trick. What it does?

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. That’s the red coloured part in the Query formula. How?

Similar: How to Create a Virtual Helper Column in Google Sheets?

Here we want to group a column based on first few characters in Google Sheets. That column in our sample data is Column A, Range A2:A6. You can use LEFT function to extract 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.

left and array formula combined use in Google Sheets

Now we want the rest of the data range to be merged with this extracted column. Then only we get a complete new data set to use in Query. This we can easily do with Curly Braces.

virtual array in Google Sheets Query

I’ve used this combination of formula as a virtual data range in our master Query formula above (coloured in red). The rest of the Query part is similar to the use of normal Query function. That means I’ve selected Column 1 and Column 2, then summed Column 4, i.e. “Qty. Recd.” based on grouping Column 1 and Column 2.

Hope you could understand how to group a column based on first few characters in Google Sheets. Use with your own data set to explore the possibilities offered by this virtual data range in Query. See you again with another trendy Google Sheets tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here