Thursday, September 3, 2015

Google Sheets Query involving two groups of data

Question:

The easiest way to explain what I'm trying to do is to give an example of the data, so here's a simple example:

A                B          C            D
Name1        4          Name4     3
Name2        3          Name4     2
Name3        5          Name1     1.4
Name4        3          Name3     6
Name5        1          Name6     8
Name2        3          Name7     10
Name1        4          Name5     2
With that above data, I want to produce a pair of columns, the first that has a list of all the unique entries in A and C, the second having the average of the corresponding values in either B or D for the entry in A or C. The result should look something like:
Name1      3.133333
Name2      3
etc.
Columns A and C are guaranteed to be strings, B and D are guaranteed to be positive numbers. It is also guaranteed that A and C will never contain the same value.
Then I also want to do another query similar, except instead of taking the average of all the corresponding values, I want to count the number of times some specific value occurs with respect to the total. As an example:
A                B          C            D           E
Name1        4          Name4     3           Y
Name2        3          Name4     2           Y
Name3        5          Name1     1.4        N
Name4        3          Name3     6           Y
Name5        1          Name6     8           Y
Name2        3          Name7     10         N
Name1        4          Name5     2           Y
In this case, I want to count the percentage of times Y occurs in E over the total number of times each item in A and C occurs. This time, B and D are no longer important. For example, Name1 occurs 3 times, and has an E value of Y twice, so I want 0.66667. So the result should look like:
Name1    0.66667
Name2    0.5
Name3    0.5
Name4    1

If at any point any of this isn't clear, please ask so I can clarify. Thanks.

Solution:

Screenshot of Spreadsheet:



I have the following formula in cell G1:
=query({A:B;C:D},"Select Col1,avg(Col2) where Col1<>'' group by Col1 label Col1 'Name', avg(Col2) 'Average' format avg(Col2) '0.00'")

and the following formula in cell J1:
=query(arrayformula(iferror(query({{A:A,E:E,E:E};{C:C,E:E,E:E}},"select Col1,count(Col3) where Col1<>'' group by Col1 pivot Col2")*1,query({{A:A,E:E,E:E};{C:C,E:E,E:E}},"select Col1,count(Col3) where Col1<>'' group by Col1 pivot Col2"))),"select Col1,(Col3/(Col2+Col3)) label Col1 'Name', (Col3/(Col2+Col3)) 'Percentage' format (Col3/(Col2+Col3)) '0.00'")

Tuesday, September 1, 2015

Split list and append Data

Question:

I have a sheet called Master which has 3 columns: Name Home_Address, Work_Address. Note that a Name may have one or both associated addresses.

In a second sheet, Called Split. This sheet has 3 columns: Name, Address, Type I want to split each name and address to make 1 row for each Address, be it work or home, and add a last column, Type, with either Work or Home for values.  This will be based on which column the address comes from
How can I make this split list?

Solution:

Screenshot of Sheet "Master":

Screenshot of Sheet "Split":

I have the following formula in cell A2 of Sheet "Split":
={query(Master!A2:B,"select A,B,'Home' where B<>'' label 'Home' ''",0);query(Master!A2:C,"select A,C,'Work' where C<>'' label 'Work' ''",0)}


Thursday, July 23, 2015

Fetch data in MS Excel from Google Sheet

Question:


( by Chris Gob Shite )


I have a google form which is capturing data that is saved in a Google Sheet

I want to merge that data into an official form in MS Word

MS Word will only merge with an Excel sheet and not a Google Sheet
Currently just copy/pasting everytime there is a new entry
Just wondering
Any Sheets gurus know how to Create a dynamic link from an Excel Sheet to a data in a Google Sheet?



Solution:

Following are steps that will allow you to get the data from Google Sheets to MS Excel:

In your Google Sheets, click "File" > "Publish to the web...".
And then select the sheet "Form Responses 1" (from which you want to fetch the data), and then click "Publish".


When you click Publish, you will get the URL of your published Google Sheet, Copy it... We will use this link in MS Excel.


Open Excel 2007 (I am not sure whether this works in earlier versions). Click the "DATA" Tab on the top. There is a "Get External Data" section, click "from web"...



Paste that URL Link in the address bar of the dialog box that opened when you clicked get data from the web.
If you receive any error message like "So you want to continue running scripts on this page".. then Click "Yes".

Click the check box (in yellow color) that appears in the upper left of your website (in the Excel Dialog Box)... and click Import.

It may ask "Where do you want to put the data?", Input whatever option suits you.

And note: In MS Excel, if you need to refresh the data in every minute or every 30 minutes then you can set the time duration:


Click "Properties" in Data tab, and then Click on the check box "Refresh every" and set the time. Also click "Refresh data when opening the file", if you need it.

You are done...!!
Enjoy auto Importing of data from Google Sheets to Microsoft Excel.

NOTE:
When you close MS (after saving it) and open it again, you might notice "Security Warning"


Click on "Options", and then you can "Enable this content" and click "OK"..
But if you don't want to do it every time you open your Excel then you can click on "Open the Trust Centre".


Click on "External Content" and then choose "Enable all Data Connections" and "Enable automatic update for all Workbook Links".

Finally you are done.


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, April 25, 2014

Google Spreadsheet Solution With Advanced Query Formula

Question:

( by ANTONINO L )


Who can help me to solve this problem: 

I would like to sum ​​the number of packages in a column having a specific code for each month. 
How can I do?
Thanks

SHEET1SHEET 2
DATISUMMARY
CODDATAQ.TA'CODDESCRQ.TA' GENQ.TA' FEBQ.TA' MAR
6401/03/2014564PROD15
222713/02/201442227PROD2105
222711/03/201453339PROD3
345609/01/2014133456PROD413
222727/02/20146


Solution:


Screenshot of Sheet1:




Screenshot of Sheet2:






I have the following formula in Cell A1 of Sheet2:

=query(arrayformula(iferror(if({1,1,1,0},Sheet1!A:C,if(Sheet1!B:B="","","Q.TA' "&text(Sheet1!B:B,"MM MMM"))))),"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col4")


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