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

Google Spreadsheet Area Calculations in Feet and Inches

Question:

( by Paul Enestvedt )

How do I enter feet and inches in a spreadsheet so that I can multiple values together and arrive at a sq. ft. (L x W) value?

Solution:

Have a look at the following screenshot:

In the above sheet, I have the following formula in Cell G3:

=round( ( (A3*12)+B3 ) * ( (D3*12)+E3 ) / 144 ; 2 )

and then you can drag the above formula to Cell G4,G5... and so on... as far as needed...

And you can also use the following array formula in Cell G3 to auto populate entire column G:
=arrayformula( if( A3:A="" ; "" ; round( ( (A3:A*12)+B3:B ) * ( (D3:D*12)+E3:E ) / 144 ; 2 ) ) )

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 How do you subtract/add values from a cell much like depositing and withdrawing from a bank account? (Not just + and - operators)

Question:

( by Tes Lescat )

Say I have a cell (let's say A1) with value 1000

From another cell (B1), I want users to input X so that A1 will become 1000+X. From that same cell B1, if a user were to input -Y, A1 does not go back to 1000 but rather adds or subtracts all the values that have ever been entered into A1. Thus, A1 would be 1000+X-Y

For example:

A1 = 500

user enters B1 = 4

A1 = 504

user enters B1 = -20

A1 = 484 (because 504-20, not 500-20)

user enters B1 = 100

A1 = 584

etc. etc.

Solution:

Have a look at the following animated screenshot:

Have a look at the following code:

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

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var a1 = s.getRange("A1");
  var b1 = s.getRange("B1");
  var activeCell = s.getActiveCell().getA1Notation();
  if( s.getName()=="Sheet Name" && activeCell=="B1" )
    a1.setValue(a1.getValue()+b1.getValue());
};

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

put the above code in your script editor and then whenever you edit on any sheet, it will automatically update the current total in Cell "A1" of the sheet name "Sheet Name".

You can change the "Sheet Name, "A1" and "B1" as per your requirement.


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:

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 Concatenate 2 rows if they have the same value in one column

Question:

( by Paul Enestvedt )

Hi, I'm trying to merge the text in several rows based on a common ID# in a different field.  For example, I have:

Id           date             service

12345     1/21/2012     testing

23456     4/21/2010     registration

12345     2/15/2012     conference

12345     2/15/2013     practice test

44567     9/14/2009     coaching

12345     8/15/2012     employed

44567     10/13/2010   conference

etc.

I would like:

12345     testing, conference, practice test, employed

23456     registration

44567     coaching, conference

I found a post that demonstrated a similar query for summing, but I want to concatenate.

Anyone ideas?

Thanks,

Paul

Solution:

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

And following screenshot of "Sheet2":

In the above sheet "Sheet2", I have the following formula in Cell A2:

=unique('Sheet1'!A2:A)

the following formula in Cell B2:

=join(", ";transpose(iferror(filter('Sheet1'!C:C;'Sheet1'!A:A=A2))))

and then you can drag the above formula to Cell B3, B4... and so on... as far as needed...

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