Google Spreadsheet How to stack different columns using a formula or query.

Question:

( by Gilles-Japon )

Using Mac 10.8.5, Chrome or Safari

I have a form that collects information for an entire family. We ask each participating member of the family the same information (rental of ski, size of shoe,...). Some of the members have to and some don't have to fill the information (they do not want the service). My problem is that the information of all the members of the same family is displayed in one line. With several families registering, I need to be able to cumulate the information about a specific service for the whole group (all the families).

I would like to be able to create a sheet pulling the information from the form, and placing the appropriate answers of the other member of the family underneath the first response, keeping the blanks if any, to match the names.

The sample worksheet is here with the expected results:

I tried many formulas, but really cannot find one that works. Anybody would have an idea how to stack results ?

Thank you

Gilles

Solution:

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.

Have a look at the following screenshot of Sheet "Feuille 1":

Have a look at the following screenshot of Sheet "Feuille 2":

In the above Sheet "Feuille 2" I have the following formula in Cell A1:

=vmerge( filter( 'Feuille 1'!A:H ; not( ('Feuille 1'!A:A="")*('Feuille 1'!B:B="")*('Feuille 1'!C:C="")*('Feuille 1'!D:D="")*('Feuille 1'!E:E="")*('Feuille 1'!E:E="")*('Feuille 1'!F:F="")*('Feuille 1'!H:H="") ) ) ; filter( 'Feuille 1'!I:P ; not( ('Feuille 1'!I:I="")*('Feuille 1'!J:J="")*('Feuille 1'!K:K="")*('Feuille 1'!L:L="")*('Feuille 1'!M:M="")*('Feuille 1'!N:N="")*('Feuille 1'!O:O="")*('Feuille 1'!P:P="") ) ) ; filter( 'Feuille 1'!Q:X ; not( ('Feuille 1'!Q:Q="")*('Feuille 1'!R:R="")*('Feuille 1'!S:S="")*('Feuille 1'!T:T="")*('Feuille 1'!U:U="")*('Feuille 1'!V:V="")*('Feuille 1'!W:W="")*('Feuille 1'!X:X="") ) ) ; filter( 'Feuille 1'!Y:AF ; not( ('Feuille 1'!Y:Y="")*('Feuille 1'!Z:Z="")*('Feuille 1'!AA:AA="")*('Feuille 1'!AB:AB="")*('Feuille 1'!AC:AC="")*('Feuille 1'!AD:AD="")*('Feuille 1'!AE:AE="")*('Feuille 1'!AF:AF="") ) ) )

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

Converting Text responses to numerical values

Question:

( by Jake Probst )

Hello,

I am doing some monitoring with a student using this form:

The results end up like this:

What I would like to do now, is create a separate sheet to graph each column over time. In order to do this, I was thinking I would use a conversion sheet that uses IF THAN statements to convert the results to numerical values. 

This is one of the things I have tried in order to accomplish this:

=IF(OR('Form Responses'!B2="1 - Less than 1/2 of the homework was completed."),'Form Responses'!B2=""1", IF(OR('Form Responses'!B2="3 - 3/4 or more of the homework was completed."),'Form Responses'!B2=""2", IF(OR('Form Responses'!B2="3 - 3/4 or more of the homework was completed."),'Form Responses'!B2=""3", IF(OR('Form Responses'!B2="4 - All of the homework was completed."),'Form Responses'!B2=""4"))

Unfortunately, I am getting a parse error. Any ideas?

Also, If you see a cleaner way to accomplish this, I am certainly open to it.

Thank you!

Solution:

Have a look at the following screenshot of "Form Reponses":

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

In the above "Sheet2" I have the following formula in Cell A1:

=Arrayformula('Form Responses'!A:A)

and  the following formula in Cell B1:

=Arrayformula(if(row(A:A)=1;"Homework Completion [Points Earned]";iferror(match('Form Responses'!B:B;{"1 - Less than 1/2 of the homework was completed.";"2 - At least 1/2 of the homework was completed.";"3 - 3/4 or more of the homework was completed.";"4 - All of the homework was completed."};0))))

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

Google Spreadsheet Copy data from one sheet to another if certain conditions are met removing duplicates

Question:

( by tic toc )

I would like to be able to copy data from Sheet1 to Sheet2 displaying only those rows with the highest values from Sheet1.

The following is an example of what I would like to do.  Sheet3 is the result that I would like to achieve.

Sheet1:

Sheet3:

If "Name" and "Value 1" are the same, I only want to copy the row with the highest number in column "Value 2" to Sheet2.  After copying the values to Sheet2 I would like them to display in descending order.

Thanks for the help.

Solution:

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

In the above sheet I have the following formula in Cell A1:

=query(Sheet1!A:C;"select A,B,max(C) group by A,B order by max(C) desc label max(C) 'Value 2'";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 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

Google Spreadsheet Compare between software version numbers

Question:

( by Spon4ik )

I need to compare between several version numbers with unusual decemas for integer values like 4.0.4 & 4.3.0

and also to be able to filter and display only the greatest or lowest version.

MIN/MAX doesn't see it as numbers, and doesn't work for me.

any suggestions?

I need help with a filtering and comparison between numbers as 3.2.5>2.4.3>2.2.6 etc

I need to display data in column A:A(model name) and B:B(firmware ver) while B:B = MAX (per model name in A:A)

I hope I wrote it's right.

thanks in advance!

Solution:

Have a look at the following screenshot of Sheet "rom comparison":

Have a look at the following screenshot of Sheet "New Sheet":

In the above sheet I have the following formula in Cell A1:

=query('rom comparison'!A2:F;"select A,max(F) where A<>'' group by A label max(F) 'Latest Version'")

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

Google Spreadsheet Grouping Fields

Question:

( by Ben )

Ok, I have a major head scratcher! My data is currently like the table below and I need it to be sorted by the Product Grouping ID. I have hundreds of rows so need a way to do this programmatically. Not even sure if this is too difficult for a spreadsheet. 

Would be very grateful for any help or pointers.

Thanks

CURRENT DATA ARRANGEMENT

CURRENT DATA ARRANGEMENT
SKU	Product ID	Title	Product Grouping ID
FS01	6297	Nike Bears Home Shirt 2012 2014	6297
FS02	6298	Nike Bears Home Shirt 2012 2014	6297
FS03	6299	Nike Bears Home Shirt 2012 2014	6297
FS04	6300	Nike Bears Away Shirt 2013 2014	6298
FS05	6301	Nike Bears Away Shirt 2013 2014	6298
FS06	6302	Nike Bears Away Shirt 2013 2014	6298
FS07	6303	Nike Bears Away Shirt 2013 2014	6298
FS08	6304	Nike Bears Away Shirt 2013 2014	6298
FS09	6305	Nike Bears Away Shirt 2013 2014	6298
FS10	6306	Nike Bears Away Shirt 2013 2014 Junior	6299
FS11	6307	Nike Bears Away Shirt 2013 2014 Junior	6299
FS12	6308	Nike Bears Away Shirt 2013 2014 Junior	6299

REFERRED DATA ARRANGEMENT

Product Grouping ID	Title	SKU
6297	Nike Bears Home Shirt 2012 2014	FS01,FS02,FS03
6298	Nike Bears Away Shirt 2013 2014	FS04,FS05,FS06,FS07,FS08,FS09
6299	Nike Bears Away Shirt 2013 2014 Junior	FS10,FS11,FS12

Solution:

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

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

In the above sheet I have the following formula in Cell A1:

=unique(Query(Sheet1!C2:D;"select D,C";1))

and the following formula in Cell C2:

=join(",";transpose(filter(Sheet1!A$2:A;Sheet1!C$2:C=B2;Sheet1!D$2:D=A2)))

and then you can drag the above formula to the cells below as far as needed, and by doing so you will get the following formula in Cell C3:

=join(",";transpose(filter(Sheet1!A$2:A;Sheet1!C$2:C=B3;Sheet1!D$2:D=A3)))

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