yogi_Pull First Instance Of A Record From 'Form responses 1' Sheet If The Record Was Submitted Within The Specified Number Of Days of The Refrenced Date

Google Spreadsheet   Post  #2030

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

post by: Ferdi T :

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/sVDIjY9l2bo;context-place=mydiscussions
RE: I need help writing a non complicated formula/query

Hi there,

I'm really bad with google sheet and almost as bad with searching forums so I apologise if my problem already found an answer.

I have a very simple google spreadsheet (just 6 columns, where the first one "A" is the time stamp); the file is populated via google forms.

I was wondering if you could help me to write a formula (query/array formula or anything that is more appropriate) that allowed me to constantly extract from one sheet ('Form responses 1') to a new sheet, all the rows generated in a given period of time (i.e. all the rows generated in the last 30 or 40 days).

This second sheet would be publicly shared on the web and should only show the entries of the above mentioned period of time (i.e. the last 30 or 40 days).

It would also be super if the formula (or a different formula) eliminated any duplicate row (column B contains a 16 digit alphanumeric code that could be used as the reference term for duplicates).

Thank you very much for your help.

Ferdi

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

yogi_Create A Quilt Or Tile-View Of Selected Results

Google Spreadsheet   Post  #2029

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

post by: danielrucci :

https://productforums.google.com/forum/#!topic/docs/N4HaSXQ1CuI;context-place=forum/docs
Create a "Quilt" or Tile-view of selected results

Team,

I have a long list of clients and certain aspects about them, which I'd like to pull into another tab and reformat into a "Quilt"-like view, if they meet certain criteria.

So if any customer in the spreadsheet is selected to be in the quilt (in order) then it gets pulled into a possibly endless quilt in another page. I'm also adding counts to the quilt page.

editable version here

https://docs.google.com/spreadsheets/d/12bqbc3ZB-ZKKKa8I2FUUI8GQIoEn_vGHfsa7X_c1UH4/edit?usp=sharing

thanks for your help

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

yogi_Conditionally Format Rows In Which Column A Houses 'closed'

Google Spreadsheet   Post  #2028

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

post by: Marc Simmons126 :

https://productforums.google.com/forum/#!topic/docs/2HjB3a4jeFQ;context-place=mydiscussions

Conditional formatting of row (not column)

 

I have a google sheets spreadsheet that includes a column where the the cell's optional content is "open" or "closed".  I would like to make the row have a fill color of grey automatically when the value in this cell is "closed". Is there any way to do this?  Thanks for any help

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

yogi_Split Row By Row Set Of Courses Keyed In As A String With Comma Delimiter And Then Lookup Corrsponding Course Numbers From 'Course List'

Google Spreadsheet   Post  #2027

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

post by: Michelle Roach:

https://productforums.google.com/forum/#!topic/docs/PnjVGtJvSEI;context-place=forum/docs

Remove a leading space that appeared after a split function

I have cells that will contain multiple parts for example French 1, French 2, French 3. I split it up but now would like to find a way for the space to be removed that is in front of French 2 and French 3.  I need the space gone because I'm also trying to do a vlookup in the following columns, using an array in another sheet. Here is a test spreadsheet:  https://docs.google.com/spreadsheets/d/174bTY332ZnlaOViWGVCD0q8kXTXeewVvZx-ycrxJxvM/edit?usp=sharing

Any help is appreciated!
-------------------------------------------------------------------------------- 

yogi_Restrict Entry 'Completed' Into Rows Of Column A Until Hours Have Been Entered In Column B Or D

Google Spreadsheet   Post  #2026

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

post by: Tom:

https://productforums.google.com/forum/#!topic/docs/p2pqQ_KEJzU;context-place=forum/docs
Possible to restrict adding value if adjacent cell is empty?
Hi Again!

I'm at risk of running out of goodwill here after asking so many questions but this might be an interesting challenge to some of you geniuses!

In the sample sheet here - is there anyway to prevent setting the status to "Completed" until the hours cell next to it has been completed? The practical purpose for me is to remind (make) staff enter their hours working on a task prior to marking a job as complete.

Thanks as always :)

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

yogi_Compute Instance Of Most Occurring Weekday For Tasks In E20:E For Categories Specified In B1:E1

Google Spreadsheet   Post  #2025

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

post by: Nuclear Buckeye:

https://productforums.google.com/forum/#!mydiscussions/docs/F8fhYOeU2aM
Find most productive day of the week - HELP!!!
...
from post 0f 10:21 AM Jan-18-2016

Thanks for the help so far.... I'm lost (as H***) as to how to use array formula and queries... you guys are too good.

I'm sharing a dummy sheet (sorry, didn't think of making a dummy sheet)

I have the categories up in Row 1. I actually want the most frequently reoccurring day, for each category

https://docs.google.com/spreadsheets/d/1Vx9ugj4c4ho5wK1PPJhzfD8vxNc-WNi0yiVDeBYKeXw/edit?usp=sharing 

https://docs.google.com/spreadsheets/d/1Vx9ugj4c4ho5wK1PPJhzfD8vxNc-WNi0yiVDeBYKeXw/edit#gid=0

Cells B5:F5 are editable. I cleared a lot of it out to make it less busy.

Also, if possible, to have Cell F5 also since it'll be the best overall day of the week.

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

yogi_Split Entries In Sheet 'yC' With Collection Column F Delimited By Specified Character Into DataBase Layout

Google Spreadsheet   Post  #2024

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

post by: Training Development Changi Airport:

https://productforums.google.com/forum/#!topic/docs/NL7QojMMoYs;context-place=forum/docs
How to generate new rows from multiple comma-separated values in a single column

Hi, I do not have any programming background in JavaScript but have been attempting to do the Apps Script to write codes based on what I read online.

I have reached a roadblock on one of the posts by Martin Hawksey on "Data Shaping in Google Spreadsheet – Generating New Rows from Multiple Values in a Single Column" (https://mashe.hawksey.info/category/mooc/page/4/).

Basically I need to do exactly what he did in his article, and I have my spreadsheet here https://docs.google.com/spreadsheets/d/1gqB4qZOvBXDBITZaxDfe8pUmx3EObLiUcIzmZ1U1xLg/edit?usp=sharing 

In the tab Form Responses 2, I have a Google form linked to it whereby people can enter multiple names by separating them with commas. I need to generate new rows from each name, and have the other columns and their contents duplicated into the corresponding rows. However when I try to use the below code, I am still unable to obtain the new rows with each name.

Is anyone kind enough to assist?

function splitColumnAndRepeatRows(anArray, splitColumnIndex) {
  var output = [];
  for (var i = 0; i < anArray.length; i++) { // for each row
    var splitArray = anArray[i][splitColumnIndex].toString().split("\n"); // split values in specified column
    for (var j = 0; j < splitArray.length; j++) { // for each split cell value
      if (splitArray[j] == "" && j >= 1)
        continue;
      var row = anArray[i].slice(0); // take a copy of source row
      row[splitColumnIndex] = splitArray[j];
      output.push(row); // push new row to output
    }
  }
  return output;
}

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

yogi_From Data In Table A16:E Compute The Most Productive Day (the day with fewest 'inc') In Column E

Google Spreadsheet   Post  #2023

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

post by: Nuclear Buckeye:

https://productforums.google.com/forum/#!topic/docs/F8fhYOeU2aM;context-place=forum/docs

Find most productive day of the week - HELP!!!

I have a list of dates in A17:A in format eg, Sat. 16-Jan. 

I also have the categories for the type of work are in cells E17:E. One category is labeled "inc", short for incomplete.

How can I find the most common day of the week, eg. Wed or Tues, that I complete the work?

Desired Result format: Saturday, e.g.

(PS - cannot share spreadsheet since it has client info on it)

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

yogi_Compute In Columns C And D Row By Row Running Total Of Instances Of 'de' And 'en' In Column B

Google Spreadsheet   Post  #2022

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

post by: AndreEG:

https://productforums.google.com/forum/#!topic/docs/xspGQBruyQM;context-place=forum/docs

Need some help with arrayformulas

 I am currently working on my pocket stats, and I would like to have some kind of counter. See the screenshot:

I would like to do the same with arrayformulas, but currentky 'm getting the error "circle reference".

Thanks for your help

Andre

yogi_Pull Name Of Grade Winner Scoring Maximum Number Of Total Points By Specified Grade

Google Spreadsheet   Post  #2021

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

post by: Abby Samotis:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-SN-HUxkz8U;context-place=forum/docs
Pulling max from several data points in a sheet.
I have a sheet that records points per grades 7-12 by student name. I would like to pull the name of the student with the most points in a grade and report it in a separate cells. 

Therefore I want to pull the name cell, based on two cells of data: most points, and grade. My end goal is to report the student of each grade 7-12  by name. 

Advice?

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

yogi_Conditionally Format Current Month Column

Google Spreadsheet   Post  #2020

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

post by: RyanU:

https://productforums.google.com/forum/#!topic/docs/r_zL4z_jpvw;context-place=mydiscussions
Conditional Formatting for Months

Basically I'm looking to highlight the current month and it's column (subset of cells within that column).

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

yogi_Use Custom Number Format To Display Results That Amount To 0 (or $0.00) As Blanks

Google Spreadsheet   Post  #2019

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

post by: Lampasas Trucking:

https://productforums.google.com/forum/#!topic/docs/NAp-GMNpIgs;context-place=forum/docs
Getting rid of the "$0.00" until I input an amount
Hello,

I'm attempting to make invoices for my business, which is cement and crane work. There are times when I do not know the amount of hours the crane will be running, therefore I cannot input the amount due. The guys go out and do all the heavy labor, and prior to them leaving, I must have an invoice for them to go off of. When I do not enter a number of hours, it simply stays at $0.00, which is correct, but then when the customer signs the invoice at the end of the job, lets say it was a 6 hour crane job, the new total of $1065.00 has to be scribbled (handwritten) over the old $0.00. It just looks unprofessional and is annoying. I don't want to delete the formula, because I'm aware that would rid me of the "$0.00". Any help would be appreciated!! Thank you!

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

yogi_Look In DataCase Table And Delineate If Error(s) Are Found Any Where in The Table

Google Spreadsheet   Post  #2018

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

post by: Mark Spencer:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/P46JU_s1m84;context-place=mydiscussions
Searching a range for a specific value
I want to test a range for the presence of a specific value, is this possible.

For example, I have a data set containing calculated and non-calculated fields. Some of the calculated fields test for error conditions in data elements in their row and return "ERROR" if met.

I want a separate sheet to test (TRUE/FALSE) for the presence of any errors anywhere in the given range.

Thanks

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

yogi_Rearrange A Table Of Entries Into DataBase Layout

Google Spreadsheet   Post  #2017

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

post by: kfeuerhelm:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-_tEugECvM0;context-place=forum/docs
Transpose/Formatting using functions.

I have attached a sample/worksheet:

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

If you have any ideas let me know.

Thanks!

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

yogi_Sum Up Hours Worked From Weekly Sheets by Customer And Project

Google Spreadsheet   Post  #2016

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

post by: tybo611:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/JBj4h7AQuhk;context-place=mydiscussions
SUMPRODUCT/SUMIFS acorss multiple sheets

I'm trying to use the SUMPRODUCT along with SUMIFS to gather data for expended hours across multiple weekly timesheets.  However the formula is only looking at the first sheet, it will not continue through to each subsequent sheet. 

The weekly time sheets are filled out, then the Data tab would pull all that information to track the amount of hours used across the weeks to quickly display any time remaining.

If it could be updated to automatically fill in the Customer and project that would be ideal but that is not a problem currently.

Any help would be much appreciated.  I've attempted this with query as well but couldn't get the syntax correct.

Sample sheet below
https://docs.google.com/spreadsheets/d/11NDNZPrOPANcCl9601cJ0IVRnMSG47hkrBJS_RDRuLI/edit?usp=sharing

All information has been stripped down to non specific, so use of cells/ranges in formula is desired so that it can be quickly updated.

I can get this to work in excel, but for some reason it does not carry over to google sheets. From what I've read, I'm guessing it could have something to do with the array calculations but I'm not sure. 
---------------------------------------------------------------------------------------------

yogi_Convert An Array Of Names Into UserNames (lowercase without any special characters)

Google Spreadsheet   Post  #2015

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

post by: Robert Andrews:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/TD8PeY05ufo;context-place=mydiscussions
Convert name to username?

How can I take a cell containing a full name and turn it in to a username - ie. no symbols or spaces, only text, preferably lower case?

Remember, names come in all shapes and sizes, with punctuation marks, middle names, spaces and all sorts.

Thanks.

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

yogi_In Table A:K Count Number Of Instances Of Each Entry In Column K

Google Spreadsheet   Post  #2014

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

post by: MSG Crane:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/umbbrIQPzVU;context-place=forum/docs
Using multiple "count() where" arguments in a Google Query
Team,

Hoping for a little help...  I am building a charts dashboard and one of the charts is a line chart depiction of the total number of records that meet the following criteria in column "K":  "Start Usage", "Midpoint", and "End Usage".

In the end, I would like the query result to show the following so I can graph each submission type with different lines on the chart:

|    A   |                     B                  |                 C               |                 D                   |

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

|Date | Count of Start Usage | Count of Midpoint | Count of End Usage |

I feel like the way to do this is three iterations of count() where K = "???" on column D.  I can get it to work with one iteration of "count() where" but I cannot figure out how to get it to work three times in the same query... 

This works:

select A, count(D) where K = 'Start Usage' group by A

Query as it stands right now with coloring for clarity (need help):

select A, count(D) where K = 'Start Usage' label 'Count of Start Usage', count(D) where K = 'Midpoint' label 'Count of Midpoint', count(D) where K = 'End Usage' label 'Count of End Usage' group by A

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

yogi_Transpose Cross Tabbed data Into DataBase Style Layout

Google Spreadsheet   Post  #2013

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

post by: aaron urban:

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

Index and Match?

https://docs.google.com/spreadsheets/d/1aZKphjUWSy5KcLT8YlRx62e-SUzUCwR7MzOJDwot3DM/edit?usp=sharing

I am trying to re arrange the information in a table and am stumped as to which functions to call.  I am looking for a specific output.

Any help would be greatly appreciated.

Thanks in advance!!!
-------------------------------------------------------------------------- 

yogi_Compute Row By Row Count Of Number Of Students Registered In Classes Listed In Columns F:K

Google Spreadsheet   Post  #2012

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

post by: shcptech:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/osHoc8Nbm2o;context-place=mydiscussions
Count number of non-blank cells with an array?
I saw this asked in another discussion, but the formula did not work for me.

https://docs.google.com/a/shcp.edu/spreadsheets/d/1N6xy4DkhHbP9qndwpSXefc4wjIfASNk1fpwmTgAEJGw/edit?usp=sharing

I would like the first column to count the number of non blank cells in columns F-K and put the result in column A.data in the cells will constantly be updating, and in fact, at some point the contents will be numbers, and I would want to add the values in cells F-K and put the sum in Column A.

Thanks for your help!

Tim

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

yogi_Compute Month By Month Amount Spent And Running Balance Remaining From Monthly Budget Amount

Google Spreadsheet   Post  #2011

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

post by: David Leichter:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/JEbGnQTIGjg;context-place=mydiscussions
Show Purchases Totaled for Each Month SUMIFS

I want to track spending, and make sure I stay under my organization's monthly budget.

Sheet Example

As you can see in the example below, Column A contains the dates a purchase was made, Column C contains the total amount. Over in Column G, I have the months of the year and in column H is the total spend for each month.

In Column H I have a formula that works, but I wonder if there is a more elegant solution:

=SUMIFS(C$2:C, A$2:A, "<2/1/2016", A$2:A, ">12/31/2015")

This causes all of the purchases with a January 2016 Date to be summed next to January. I then cut and paste this formula down the column manually changing the date range in the formula each time.

I tried all the following, and either got an error or $0, as my answer:

=SUMIFS(C$2:C, A$2:A, "MONTH=1")

=SUMIFS(C$2:C, A$2:A, "<2/1/2016 AND >12/31/2015")

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