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,

Saturday, September 28, 2013

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

Tuesday, September 24, 2013

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