Tuesday, September 3, 2013

How do I transpose part of a spreadsheet with grouped columns

Question:

( by Tom Shevock )


Here is the sheet I have as a result of a Google Form...(Columns)

A, B, C, D, E, ..... AF, AG, AH, AI           all in one spreadsheet row.  An unknown number of rows will be created.

Here is what I need for each row from above...

A, B, C, D, E, F
A, B, C, G, H, I
A, B, C, J, K, L
...
A, B, C, AG, AH, AI
and then repeat for the next form row.

Seems straight forward but haven't found a way to do it.

Any help would be appreciated.
Tom

Solution:

Have a look at the following screenshot of "Sheet1":




Have a look at the following screenshot of "Sheet2":




You will need to install script VMerge from Script Gallery.
Go to menu "Tools" > "Script gallery" and then search for "vmerge" and then install it.

After you have installed the script, put the following formula in Cell A1 of "Sheet2":

=query(vmerge(query('Sheet1'!A:AJ;"select A,B,C,D,E,F");query('Sheet1'!A:AJ;"select A,B,C,G,H,I");query('Sheet1'!A:AJ;"select A,B,C,J,K,L");query('Sheet1'!A:AJ;"select A,B,C,M,N,O");query('Sheet1'!A:AJ;"select A,B,C,P,Q,R");query('Sheet1'!A:AJ;"select A,B,C,S,T,U");query('Sheet1'!A:AJ;"select A,B,C,V,W,X");query('Sheet1'!A:AJ;"select A,B,C,Y,Z,AA");query('Sheet1'!A:AJ;"select A,B,C,AB,AC,AD");query('Sheet1'!A:AJ;"select A,B,C,AE,AF,AG");query('Sheet1'!A:AJ;"select A,B,C,AH,AI,AJ"));"select * where Col1<>'' ")



And if you want to get the results in the following order:

then try the following formula:
=query(vmerge(query('Sheet1'!A:AJ;"select A,B,C,D,E,F");query('Sheet1'!A:AJ;"select A,B,C,G,H,I");query('Sheet1'!A:AJ;"select A,B,C,J,K,L");query('Sheet1'!A:AJ;"select A,B,C,M,N,O");query('Sheet1'!A:AJ;"select A,B,C,P,Q,R");query('Sheet1'!A:AJ;"select A,B,C,S,T,U");query('Sheet1'!A:AJ;"select A,B,C,V,W,X");query('Sheet1'!A:AJ;"select A,B,C,Y,Z,AA");query('Sheet1'!A:AJ;"select A,B,C,AB,AC,AD");query('Sheet1'!A:AJ;"select A,B,C,AE,AF,AG");query('Sheet1'!A:AJ;"select A,B,C,AH,AI,AJ"));"select * where Col1<>'' order by 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:
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,

1 comment:

  1. Thanks,
    Here is a link to the spreadsheet with the form data in the first three lines and the expected result starting at line 14.

    https://docs.google.com/spreadsheet/ccc?key=0Ak1t-W4jJzB0dHltMW9iWHFHeXBLV3BrZDkwTmpWOGc&usp=sharing

    Where there is no "Photograph_Title", no row should be created.

    ReplyDelete