Google Spreadsheet formula to Concatenating columns from query results

Question:

( by christopher.r.haley )

I sell items on eBay and log them in google spreadsheet.  I'd like to see which weekday is the best day for me in terms of when I make a sale.  In order to do this I have a column where I log the date a sale was made.  For a short example, assume this is my data (2nd column in the formulate below is necessary because you can't aggregate and non-aggregate on the same column in the SELECT query:

06-18-12

06-19-12

06-19-12

06-21-12

I then have a query to determine which day I sell the most on:

=ArrayFormula(Query((A:B),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 IS NOT NULL GROUP BY dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"))

This gives me the following:

DoW | Occurances

2 | 1

3 | 2

5 | 1

What I want to see is something like this:

DoW (Occurances)

2 (1)

3 (2)

5 (1)

Essentiall, I'd like to concatenate the columns from the query's results. Better yet I'd like the DoW to be the 3 letter representation for the weekday (i.e. 2 = "Mon", 3 = "Tue", 5 = "Thu"). I know I can do all of this with multiple rows/columns and either hide them or use another sheet. However, I would LOVE to do it all in one call, if possible.

Solution:


If you are having the dates in Column A:

6/18/12

6/19/12

6/19/12

6/21/12

Then put the following formula in some other column, like in Column B:

=arrayformula(concat(concat(concat(query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col1");" (");query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Thu (1)

Tue (2)

And if you want the output in Ascending order of days, that is Monday then Tuesday then Wednesday and so on... then try the following formula:

=arrayformula(concat(concat(concat(TEXT(query(ArrayFormula(query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col1"));"select Col1");"DDD");" (");query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Tue (2)

Thu (1)

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 or confidential projects:

http://igoogledrive.blogspot.in/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