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:

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