yogi_From 'Master Sheet' Pull Select Columns For Specials By Day Of Week And Lunch

Google Spreadsheet   Post  #2088

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-30-2016

question by: Josh Crofts:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/5nfKVHg21pA;context-place=forum/docs

Need to pull data from one sheet into another but also filter it

 Hi,
I am new to the world of spread sheets and am trying to setup some sheets to auto fill off the main sheet.

What I have done is created a master sheet with different categories (venue, address, phone number, day of special etc.)

I would like to be able to pull out the data for each given day onto a new sheet.
This would have Venue, suburb & specials for the given day on each particular sheet.

Some venues do not contain specials for every day of the week so when they are pulled into the monday sheet I have many venues listed with  blank spcials.

I would just like to pull across the relevant venues that have data located in the monday specials column on the master sheet so that when I am on the Monday sheet only venues with specials on Monday are listed.

Here is the required link,

https://docs.google.com/spreadsheets/d/1UY-x739uS6QSyHpOdWRCyYLr-VbTDgRRhGbOqYEi4hY/edit?usp=sharing

Hopefully that makes sense

Thanks,

yogi_Populate Required Number of Classes Given Number and Names Of Students And Max Number Of Students Per Class

Google Spreadsheet   Post  #2087

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-27-2016

question by: Andre LaFlamme:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/XGGHbo0tmHM;context-place=mydiscussions
calculating number of classes - please help

I have individual students enrolling for classes with a minimum of 2 and maximum of 8.  As such, 2 or more to 8 is 1 class.  8 is 1 class.  9 is 2 classes of less than 8.  37 students is 4 classes of 8 and 1 class of less than 8.  And so on.  So I will have a cell for individual students, a cell of classes of 8 and a cell for classes of 2 of more and less than 8.  Can someone suggest and efficient way/arrangement of cells and formulas to for me to calculate this?

yogi_Pull Unique Names From B8:B Where Color Specified in J8 Matches That In Range C8:H

Google Spreadsheet   Post  #2086

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-26-2016

question by: Ekim Otucu:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/ROQA-rvzxZI;context-place=forum/docs
Return name from column based on match in range of data?
I have a range of data, with the corresponding names to the left in a column. I want to have an example of data, and pull the corresponding names from the left out no matter where in the data the match to my example was found. Here is an example:

https://docs.google.com/spreadsheets/d/1bObxnrHa8GYqWiyAe25FObAGsxBhidl7bBf1Tuq9Azc/edit?usp=sharing

Thanks!

Ekim

yogi_Create Pivot Table For Selected Clients From Raw Data

Google Spreadsheet   Post  #2085

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-20-2016

question by: Wynne Walker:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!msg/docs/aAI6LrJ0Q88/NBmAIbqAAgAJ;context-place=topic/docs/8ogMiVvgF2I
Help with query and pivot

Well, I tried for over hour and gave up on this. I am new to query formulas and pivots.

I have a tab with the columns: Client, Date, Actual Hours. This is imported from a bunch of raw data from our time tracking software.

I have a 2nd tab with the columns: Client, Target Hours. The data in these 2 columns is manually entered.

I want a 3rd tab to have the columns: Client from tab 2, Target Hours from tab 2, Actual Hours by Month (multiple columns for each month from tab 1), Total of all months.  See below and attachment. 

Client          Target Hours           Jan    Feb    March   Apr  .....   Total

John Doe           2                         .5      1         3          2              30

Jane Doe         etc ......

On the attachment its using a pivot table, which is formatting it EXACTLY how I want BUT its not filtering out the clients we've deleted by manually entering them on tab 2. So imagine we have 100 clients but we only care about tracking our time on 50 of them. Thats what the 2nd tab is for, its a manually selected list of clients we care about and we want the 3rd tab, time by month, to only show the 50.

I tried the following query which works for one row, John Doe (A2) but I dont know how to make it continue down the rows by having A2 increment to B2, C2, etc. 

=query('RAW Data Time'!A:AA,"select AA, sum(K) where AA ='"&A2&"' group by AA pivot A")

Sorry if this is super confusing!!

Thanks!

Screenshot 2016-11-20 11.04.29.png

175 KB

Categories:ChromeAsk a "

yogi_modify entries in range A2:A matching those in D1:H1 as specified in the question by the poster

Google Spreadsheet   Post  #2084

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-20-2016

question by: BillTeach:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8ogMiVvgF2I;context-place=mydiscussions
Regex formula to replace multiple first words with multiple substitutes

How do I copy from one sheet to another if cell string begins with an apostrophe.

I want to substitute only at the beginning of a string (followed by a space).  For example:

m-> 'm

s -> 's

d -> 'd

ll-> 'll

re -> 're

've -> /ve

or in the complete string:

m going home -> 'm going home

Something like =substitute(A1, "m", char(39)&"m") works for one substitute, but I think regexreplace will be better to replace multiple substrings and only at the beginning of the cell string.

yogi_Compute Stats For Hourly Visitor Count For WeekDays And WeekEnds

Google Spreadsheet   Post  #2083

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-12-2016

question by: Nudus:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/bSSx7ZhOsXU;context-place=forum/docs

Trying to pull out metrics off a visitor log.

 Hey guys!

I'm trying to figure out a way to pull average visits on an hourly base - So I can tell how many people visited on average per hour for different hours.

I have the log files that our swipe card system creates , I attempted to create a Pivot Table but I cant seem to get them to display unique only.

Ideally I'd like to be able to get statistics for weekdays / weekends separately.

File link:
https://docs.google.com/spreadsheets/d/1wzJejhfh-yL_y-rvHC_lgD8y_HR-wCZkDfwLN0d7wUw/edit?usp=sharing

Would really appreciate any help, I'm at a loss here :<

---------------------------------------------------------------------------------------

yogi_Compute Row By Row And Column By Column Average Of Entries Submitted via Google Form

Google Spreadsheet   Post  #2082

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-08-2016

question by: BMosty:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/PCkUjhZIZPU;context-place=forum/docs
How do I find the average of row sums when pulling data via Google Forms?

I am collecting data using Google Forms and retrieving responses in sheets.  Because each form response adds a row, I am having trouble with my formula.  

I need to find the total numerical score of each row and then find the average of these row totals.  I have established a separate sheet to run the formulas, but the combination of pulling data from a separate sheet, totaling across a row, and then averaging several totals is beyond my limited technical abilities.  

For example:https://docs.google.com/spreadsheets/d/1n2qhLu0vBQ4x-JuvUm5OAaamEitS6G4ADa36-Q2T9Uc/edit?usp=sharing

I have a sheet named "MP1 Responses."  I would like to find the total score of each row depending on the number of Google Form responses. Then, I need to find the average of row 2's sum, row 3's sum, row 4's sum, etc.  Then, I would like this average score to appear in B2 of my sheet named "Totals."

Based on my reading, it seems that I might need to use an array, but I am not sure how.  Any help would be appreciated.

-BMosty

yogi_Create Column Chart To Show Monthly Values For Various Attributes And Their Respective Totals

Google Spreadsheet   Post  #2081

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-02-2016

question by: Bronwyn Wakeman:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/EdHJCftDglY;context-place=forum/docs

Converting to chart

How do I convert this to a chart?

Injury
July		August		September		October
with	without	with	without	with	without	with	without
7	19	6	9	6	12	7	19