Tuesday, May 23, 2017

yogi_Facilitate Filtering Data In FilterView1 based On Variables Specified In Cells A2 to A6 In Sheet2

Google Spreadsheet   Post  #2167
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-23-2017
question by schnikket79:
filter table using multiple keywords that are all located in one column
Here is a sheet I made to help illustrate my problem (it's a reduction of a much larger table).


My large table lists details about various musical works and one of the columns lists the instruments (keywords) that are needed to perform those works. 
(In the example sheet, keyword column is column I).

I don't want to change the layout of this table at all (it is used by my other sheets that rely on that particular sort, with all the rows unfiltered).

I want to be able to:
- go into this sheet's Filter View (e.g. Filter1 in my example sheet)
- choose my instruments (i.e. keywords), e.g. type them into the orange cells on Sheet2
- get a filtered table where my Sheet2 choices retrieve matching rows. 

Example:
I have there right now Sheet2!A2 == "Piano" and Sheet2!A3 == "Voice"
I want all rows where column I cell contains those two keywords to be shown.
This would include entries such as "Piano, Voice", "Piano, Voice,", or "Piano, Voice, Clarinet in Bb,". 
*It would not include entries such as "Piano Solo", for example, because my Sheet2 choice of Piano and Voice requires that both of those keywords be present within one cell.

*To limit my filter to only the rows with Piano, Voice and nothing else, I was thinking of using some kind of "terminator". Say, in Sheet2!A4, I'd put "XXXX".
EDIT: I can imagine "terminator" being a problem. I have a way of working around this issue. So, this doesn't need to be addressed.

I am guessing the above is possible using some clever Filter by Condition custom formula (including some regex stuff), but everything I have tried has failed me so far.)

I would love any help!


yogi_Row By Row Average Of Numbers In Columns

Google Spreadsheet   Post  #2166
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-23-2017
question by zr2ee:
averaging form submission data and optimizing formula's
Ultimately this is probably going to be a long thread as i'm fairly new to advanced formula's and have multiple issues i need to work through however for the sake of simplicity i'm going to try to focus on one thing at a time.

I have 3 forms submitting raw data to a workbook, i have several additional worksheets that i'm using to merge and do math on the raw data (mainly averaging), What is the best option for averaging raw form data from form submissions?

  • Currently i've tried the =iferror(AVERAGE('Step 1 (form)'!J2:N2),)
  • and =ArrayFormula(if(ISBLANK('Step 1 (form)'!E2:E)," ",'Step 1 (form)'!E2:E+'Step 1 (form)'!F2:F+'Step 1 (form)'!G2:G+'Step 1 (form)'!H2:H+'Step 1 (form)'!I2:I)/5)

i like the array formula but i'd like to have it ignore anything that is blank or zero but so far all the suggestions i've seen haven't worked for my formula.

Here is a Sandbox version of the workbook, feel free to comment and edit: Sandbox workbook

moving forward i will be looking to align and batch the raw data together hopefully automatically

Friday, May 19, 2017

yogi_Look For Student Name In Several Columns And Mark YES If The Student Has Taken The Quiz

Google Spreadsheet   Post  #2165
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-19-2017
question by KathleenWatson4s:
Looking to search several columns with array formula
I am hoping I can find someone to help!

I have a google form quiz and it asks what students are in which classrooms as different questions. They end up in spreadsheet in different columns. I am trying to figure out a way to use the search function to pick up their names in all the possible columns their name could appear in

Here is a mock spreadsheet mirroring what I am trying to do!

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

Thursday, May 18, 2017

yogi_For Specified Words Pull Unique Values For FIELD1 And FIELD2 And Present As Comma Separated Values

Google Spreadsheet   Post  #2164
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-18-2017
question by ai tis:
Query Combining duplicate matches while excluding copies
I've been using the following function:

    =query(Sheet1!A2:D," select A, B, C where A matches '"&JOIN("|", A2:A)&"' and D matches 'yes'")

This function returns 3 rows with the same string in column A along with some other rows which have different strings in column A.  For the case where column A has the same string for various rows, I want to combine the columns selected (a, b, c) into new columns a, b, and c.
In other words, I want every row that starts with a match will be added a comma separated list in which each column occupies one cell with no duplicates as shown in sheet3.

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

Sheet 2 has the function that I used and the result.

Wednesday, May 17, 2017

yogi_Array Formula of SUMIFS with partial cell text match not expanding

Google Spreadsheet   Post  #2163
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-17-2017
question by Clayton Clixo:

Array Formula of SUMIFS with partial cell text match not expanding

I'm having trouble with an arrayformula that won't expand. It's in cells B4 and C4 on the summary tab. I know the base sumifs formula works and if I copy down the array formula to lower cells it works, but it won't expand by itself. Any help is much appreciated!

Maybe it's not running because:
 - using sumif that is looking up a partial text string within a cell?
 - referencing a cell value that's populated by another array formula (column A on summary tab)

Sample spreadsheet



Thursday, May 11, 2017

yogi_Pull Row By Row GroupNo For Which Entry in That Row Is The Largest Entry In That Row

Google Spreadsheet   Post  #2161
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-08-2017
question by Hồ Quang Hướng:
https://productforums.google.com/forum/#!topic/docs/UFZB8NZBMAE;context-place=forum/docs
SUMIFS providing correct values only when I delete&paste the same value
I have used an index/match/max filter to identify column headers of cells with a value greater than zero in a given range. This works fine when there is only one such cell in a range. But what if there are multiple cells with a value greater than zero in a range? Is there a formula I can use to identify all column headers for all cells with values greater than zero in a given range?

Monday, May 8, 2017

yogi_Multi Criteria Summing From Filtered Data

Google Spreadsheet   Post  #2160
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-08-2017
question by maddy:
https://productforums.google.com/forum/#!topic/docs/GSYwdTbjcqw;context-place=mydiscussions
Identify column headers for cells that meet criteria in a range
OK.. cant  figure out why.

for the first condition if I delete and paste the same value i.e., 2 in D1, it returns correct value.

Shouldnt it by default take 2 the first  time itself.



**you can take a copy of the tab to test

Sunday, May 7, 2017

yogi_Pull Row By Row GroupNos For Which Entries Are Greater Than 0

Google Spreadsheet   Post  #2159
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-07-2017
question by CAB05:
https://productforums.google.com/forum/#!topic/docs/UFZB8NZBMAE;context-place=forum/docs
SUMIFS providing correct values only when I delete&paste the same value
I have used an index/match/max filter to identify column headers of cells with a value greater than zero in a given range. This works fine when there is only one such cell in a range. But what if there are multiple cells with a value greater than zero in a range? Is there a formula I can use to identify all column headers for all cells with values greater than zero in a given range?

yogi_Flag With 'S' Row By Row If Time In Column B Plus Duration In Column C Is In Range Of Clock Time In Row 1

Google Spreadsheet   Post  #2158
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-07-2017
question by MatKa!#:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/GXVpoAYH2Ro;context-place=mydiscussions

Timesheet creation

 Hello

Im trying to create a timesheet and i need to mark the hours needed for each task with S

So i have the following columns : Name, Task, Time Start and Duration... What i need is, if Time Start is 10:00 and the duration is 3 to feel in with S the hours 10:00 - 13:00

Here is my example sheet for explanation


Any help will be appreciated 

Thanks.

yogi_Compute Weekly Stats From Hourly And Daily Data

Google Spreadsheet   Post  #2157
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-07-2017
question by KM1388:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/MA6PvQQSGLU;context-place=mydiscussions
Need help on sum and count formula please.
Hi all,

I need some help on sum and count formula for a simple calculation.

=Query(Hours!A:G,"Select A,B, MAX(C),SUM(E),SUM(F), 3*(COUNT(D)+COUNT(F))+sum(G) where A is not null group by A,B",1)

on the formula i use 3*(COUNT(D)+COUNT(F))+sum(G) the result came out null value, if the column G value is null value. you could compare the output on column F and column H with the share link below:

can anyone help please!!!



Kenneth

Monday, May 1, 2017

yogi_For Each Set Of Customer IDs Select Unique Product Names And Pull Associated Data

Google Spreadsheet   Post  #2156
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-01-2017
question by Jill Dub:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/I8EM7j-I8nE;context-place=mydiscussions
Generate Random List with Multiple Columns Required
I need to randomly generate a list, can be repeating, from a set number of entries - however I need to make sure that the other columns in the entry row also make it in which are data-sensitive. I need to generate the list from entries in Column B, but their corresponding rows in Columns A, C and D also need to be there.