Saturday, June 11, 2016

yogi_From 'All depots' Sheet Pull Data For Each Depot Into Each Depot's Own Respective Sheet

Google Spreadsheet   Post  #2071
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2016
question by: Jack Bayntun:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/xRjUDhvQTCg;context-place=mydiscussions

Sort spreadsheet rows into different sheets?

Hi all, I'm trying to sort a spreadsheet into different categories, and I would like it so that if a cell in a row contains one thing, it displays the row in another sheet, ie if the cell contains 'BH' then I would like it to be displayed in the sheet/page Bath (BH) which is visible at the bottom, or if it contains WS then to Wells (WS). I hope I've made that clear as it's quite hard to explain, I've attached a screenshot to show what I mean. I'm not sure if this is even possible but any help you can offer is much appreciated!

P.S. The screenshot is from the Sheets app on iPad, but I mostly use a Mac with Safari or Firefox, just to avoid any confusion!
---
Hi all, I'm trying to sort a spreadsheet into different categories, and I would like it so that if a cell in a row contains one thing, it displays the row in another sheet, ie if the cell contains 'BH' then I would like it to be displayed in the sheet/page Bath (BH) which is visible at the bottom, or if it contains WS then to Wells (WS). I hope I've made that clear as it's quite hard to explain, I've attached a screenshot to show what I mean. I'm not sure if this is even possible but any help you can offer is much appreciated!

P.S. The screenshot is from the Sheets app on iPad, but I mostly use a Mac with Safari or Firefox, just to avoid any confusion
-----------------------------------------------------------------------------------------------------------------------

Tuesday, June 7, 2016

yogi_Find Different Words And Their Respective Count In A LIST OF Phrases

Google Spreadsheet   Post  #2070
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2016
question by: Manny Sousera:
https://app.vcita.com/app/conversations/8fdiszatknkool66?flow=Email_Action

I have a column with whole sentences in each cell. What I need is to count the frequency of each word in the column, and to display the results in a new column (in descending order of frequency). An example is: (row 1) The apple fell from the tree. (row 2) The dog chased after the cat. (row 3) The cat ran up the tree. The formula would show (in a new column): The - 6 Cat - 2 Tree -2 Chased - 1 ... Etc. Preferably there would be a way to include a list of stopwords (so that 'the, or, if', etc, were not included in the count). I would provide the stopwords, perhaps in a separate column within the spreadsheet. I am stumped - I have looked up and down the net for a solution, without luck.

Monday, June 6, 2016

yogi_Compute Row By Row Sum of Entries In Columns C To End Of Sheet

Google Spreadsheet   Post  #2069
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-06-2016
question by: Ellen Lucas:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/EgHmLyx_-vo;context-place=forum/docs
How can I get individual rows to automatically find the sum?
I set up a reading log minutes data collection for the students in my class.  The format is simple, I have their names on column A and then I add their time read on column, B, C, etc. 

Ideally, I would like to set up google sheets so each week when I include their weekly minutes the sheet document automatically calculates the sums in each rows for each student. My goal is to be open the document and see one student's yearly sum immediately.  I'd also like to avoid each having to go to "insert - function - sum" thousands of time during a school year. 
 
Thank you!! 

yogi_Solution For Using Query Function To Sum Values In A Column Containing Both Numeric And Textual Data

Google Spreadsheet   Post  #2068
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-06-2016
question by: Lei_Mao:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/4YzUliGOaXk;context-place=forum/docs
Spread Sheet Query Error AVG_SUM_ONLY_NUMERIC
Hi All,

I'm trying to use query on spread sheet to group the sum value of a column, here is my query: =QUERY(Raw!1:1000,"select A,SUM(B) Group by (A)",-1)

however I got the alert: AVG_SUM_ONLY_NUMERIC and google doesn't return any value

Could you advise where am I wrong? do I need to reformat the data?


tab1: my query tab 2: raw data

Thanks!
-----------------------------------------------------------------------------------------------------------------------------------

Sunday, June 5, 2016

yogi_Using INDIRECT Function With Multi Sheet QUERY

Google Spreadsheet   Post  #2067
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-05-2016
question by: Elden Fenison:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/klwaiOAhKNU;context-place=forum/docs
Using the indirect function with multi-sheet query, syntax help
I have the following formula that works fine. It queries a handful of budget sheets looking for expenditures in a certain category.

=sort(query({Amazon!A2:F;Amex!A2:F;'Cash+'!A2:F;'Double Cash'!A2:F;'USB Checking'!A2:F},"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

I would like to use the indirect function to refer to these sheets instead of spelling them out. I have cells on this sheet that contain the names of the other sheets that I want to query.

I just can't seem to get the syntax right.

I've tried this... and it doesn't work.

=sort(query(indirect(E2&”!A2:F”);indirect(E3&”!A2:F”);indirect(E4&”!A2:F”);indirect(E5&”!A2:F”);indirect(E6&”!A2:F”);indirect(E7&”!A2:F”),"Select Col1,Col2,Col6 where Col5 ='"&D1&"'"))

The sheets names I want to query are in cells E2:E7.
------------------------------------------------------------------------------------------------------------------------------