Saturday, October 26, 2013

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

Friday, October 18, 2013

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

Monday, October 14, 2013

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

Friday, October 4, 2013

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

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
SKUProduct IDTitleProduct Grouping ID
FS016297Nike Bears Home Shirt 2012 20146297
FS026298Nike Bears Home Shirt 2012 20146297
FS036299Nike Bears Home Shirt 2012 20146297
FS046300Nike Bears Away Shirt 2013 20146298
FS056301Nike Bears Away Shirt 2013 20146298
FS066302Nike Bears Away Shirt 2013 20146298
FS076303Nike Bears Away Shirt 2013 20146298
FS086304Nike Bears Away Shirt 2013 20146298
FS096305Nike Bears Away Shirt 2013 20146298
FS106306Nike Bears Away Shirt 2013 2014 Junior6299
FS116307Nike Bears Away Shirt 2013 2014 Junior6299
FS126308Nike Bears Away Shirt 2013 2014 Junior6299


REFERRED DATA ARRANGEMENT

Product Grouping IDTitleSKU
6297Nike Bears Home Shirt 2012 2014FS01,FS02,FS03
6298Nike Bears Away Shirt 2013 2014FS04,FS05,FS06,FS07,FS08,FS09
6299Nike Bears Away Shirt 2013 2014 JuniorFS10,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:
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,