Saturday, July 20, 2013

Google Spreadsheet Manipulating and displaying data using Query and VMerge

Question:

( by tkatnash )



I have a created a form and stored the responses in a spreadsheet:




I need to create:

1. A separate sheet for each Session/Grade (C) so that I can publish them.

2. A vertical stacked list of all UNIQUE item/qty/location (F-H, I-K, etc).

3. Include the Teacher Last Name (E)/Class Name (B) for each item.

I have achieved a convoluted way to get to #2 (I'm sure there is a better way) but have absolutely no idea how to do #3. I have too much time in this already and of course, they want it yesterday. Argh!

Yogi are you out there?

Thank you anyone for some much needed and appreciated help!
~ T
Solution:

Have a look at the following screenshot:





I have the following formula in Cell A2:

=query(VMerge(query('Responses'!A4:AZ;"select C,E,B,F,G,H";0);query('Responses'!A4:AZ;"select C,E,B,I,J,K";0);query('Responses'!A4:AZ;"select C,E,B,L,M,N";0);query('Responses'!A4:AZ;"select C,E,B,O,P,Q";0);query('Responses'!A4:AZ;"select C,E,B,R,S,T";0);query('Responses'!A4:AZ;"select C,E,B,U,V,W";0);query('Responses'!A4:AZ;"select C,E,B,X,Y,Z";0);query('Responses'!A4:AZ;"select C,E,B,AA,AB,AC";0);query('Responses'!A4:AZ;"select C,E,B,AD,AE,AF";0);query('Responses'!A4:AZ;"select C,E,B,AG,AH,AI";0);query('Responses'!A4:AZ;"select C,E,B,AJ,AK,AL";0);query('Responses'!A4:AZ;"select C,E,B,AM,AN,AO";0);query('Responses'!A4:AZ;"select C,E,B,AP,AQ,AR";0);query('Responses'!A4:AZ;"select C,E,B,AS,AT,AU";0);query('Responses'!A4:AZ;"select C,E,B,AV,AW,AX";0));"select * where Col1 contains 'MW6' ";0)

the above formula will give you all the columns mentioned by you in which there is "MW6" in "Session/Grade"


And if you want all the rows (that is not only "MW6"), then create a new sheet and try the following formula in it:

=VMerge(query('Responses'!A4:AZ;"select C,E,B,F,G,H";0);query('Responses'!A4:AZ;"select C,E,B,I,J,K";0);query('Responses'!A4:AZ;"select C,E,B,L,M,N";0);query('Responses'!A4:AZ;"select C,E,B,O,P,Q";0);query('Responses'!A4:AZ;"select C,E,B,R,S,T";0);query('Responses'!A4:AZ;"select C,E,B,U,V,W";0);query('Responses'!A4:AZ;"select C,E,B,X,Y,Z";0);query('Responses'!A4:AZ;"select C,E,B,AA,AB,AC";0);query('Responses'!A4:AZ;"select C,E,B,AD,AE,AF";0);query('Responses'!A4:AZ;"select C,E,B,AG,AH,AI";0);query('Responses'!A4:AZ;"select C,E,B,AJ,AK,AL";0);query('Responses'!A4:AZ;"select C,E,B,AM,AN,AO";0);query('Responses'!A4:AZ;"select C,E,B,AP,AQ,AR";0);query('Responses'!A4:AZ;"select C,E,B,AS,AT,AU";0);query('Responses'!A4:AZ;"select C,E,B,AV,AW,AX";0))


the above formulas looks horrible, but give the exact desired results...

and also the above formula also has a custom function VMerge() that you can install from Script Gallery.

Or if you wish then you can insert that VMerge code in your script editor from below.

Have a look at the following code:

///////////////////////////////////////



function VMerge() {
  var maxw=l=0;
  var minw=Number.MAX_VALUE;
  var al=arguments.length ;
  for( i=0 ; i<al ; i++){
    if( arguments[i].constructor == Array )l =arguments[i][0].length ;
    else if (arguments[i].length!=0) l = 1 ;
    maxw=l>maxw?l:maxw;
    minw=l<minw?l:minw;
  }
  if( maxw==minw) {
    var s = new Array();
    for( i=0 ; i<al ; i++){
      if( arguments[i].constructor == Array ) s = s.concat( arguments[i].slice() )
      else if (arguments[i].length!=0) s = s.concat( [[arguments[i]]] )  
    }
  if ( s.length == 0 ) return null ; else return s;
  }
  else return "#N/A: All data ranges must be of equal width!"      
}


///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.



And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

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:
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,

3 comments:

  1. Hi Kishan, Thanks so much. I plugged it in but it is giving me an error that I am not familiar with: Query completed with an empty output. Did I do something incorrectly? ~ T

    ReplyDelete
    Replies
    1. Hi Tiffany,
      Did you inserted script VMerge??
      You can go to Script Gallery and search for VMerge and install it.
      Or I have mentioned the code of it in the above blog post, copy that code and insert in your Script Editor...
      After that the above formula will work..

      Delete
  2. Hi Kishan, That worked perfectly after I installed the script. Yay! The entire school thanks you! ~ T

    ReplyDelete