Google Spreadsheet Advanced Query to get Unique Left Values

Question:

( by cossieWill )

Hello all

I am trying to create a query to select the first 9 characters of a column and them group the results by these first 9 characters.

I have tried the following but without success :(

SELECT U GROUP BY U",9

SELECT U GROUP BY LEFT(U,9)

Here is a sample of the data

Data:

09-001001-01-00

09-001007-01-00

09-001012-02-25

09-001006-00-10

09-010007-01-00

09-001006-00-20

09-001006-00-10

Expected Results

09-001006

09-010007
09-001012
09-001001


Solution:

Have a look at the following screenshot:

As you can see in the above screenshot, I have the following formula in Cell C1:

=query(arrayformula(if({1,0};left(A:A;9);1));"select Col1, count(Col2) where Col1 <> '' group by Col1 label Col1 '',count(Col2) ''")

Replace the Column A:A according to your requirment.

But you can also get your desired results by the following formula:

=unique(arrayformula(left(A:A;9)))

OR

=query(unique(arrayformula(left(A:A;9)));"select Col1 where Col1 <> '' ")


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive