Google Spreadsheet Advanced Pivot Table Group By Query

Question:

( by Aaron Isaacs )

After one of you wizards put up a single line capable of performing this bit of shifting and sorting, I'll look at it and think to myself - I want his brain, lol.  

Looking to filter (Sheet1/UniqueID) 123's data into (Sheet2) "123'sTotals".  As shown in the Shared Spreadsheet below, I want to use the Legend Column Header 'EMOTIONS' on Sheet1 as Row Headers in Sheet2.  Then, sum the total number of 10's (Happy's), 9's (Funny's), 8's...1's, by EMOTION into blocks of time (every 5 minutes) with reference to the Timestamp on Sheet1.  

Any help is appreciated!  Thank you.

Aaron

Solution:

Now, instead of having formulas in each and every cell, you can optimize the performance of your spreadsheet by using ArrayFormulas and by reducing these excessive formulas.

Have a look at the following screenshots of "Sheet2" my spreadsheet:

I have the following formula in Cell A1:

=query( arrayformula( iferror( if ( 'Data'!A:A=""; ""; if ( {{1},{0},{0},{0}}; if ( minute('Data'!B:B)<5; "0 to 5"; if ( minute('Data'!B:B)<10; "5 to 10"; if ( minute('Data'!B:B)<15; "10 to 15"; if ( minute('Data'!B:B)<20; "15 to 20"; if ( minute('Data'!B:B)<25; "20 to 25"; if ( minute('Data'!B:B)<30; "25 to 30"; "above 30" ) ) ) ) ) ); if ( {{0},{1},{0},{0}}; 'Data'!A:A; if ( {{0},{0},{1},{0}}; choose('Data'!C:C;"Scary";"Confusing";"Dumb";"Bored";"Exciting";"Anger";"Sad";"Tense";"Funny";"Happy"); 'Data'!D:D ) ) ) )));"select Col3,count(Col2) where Col2='BEE' and Col4=123 group by Col3 pivot Col1 order by Col3 desc")

the above formula will give you the pivot table, as you can see the result in the above screenshot.

You can replace: BEE and 123 as per your requirement.

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