yogi_Rearrange Team Data With Fields In Specified Order

             Google Spreadsheet   Post  #1886

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

post by  Chris Bromige:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/47j_HXpULGE
Updating 2nd sheet's data using "form response" data
Yogi,

Your suggestion regarding utilization of ArrayFormula to automatically fill rows works a charm for columns A and E of "Sorted", and the correct name of the winning and losing teams are appearing there.

However, applying the same ArrayFormula to columns B and D, where I'm hoping to have EACH row of data scanned for the winning team's score, and haviing that displayed in column B and having the losing team's score displayed in column D, resulted in sheets finding the highest/lowest score in ANY row and filling the column with that score.

Something that is complicating the entire project is my desire to allow for the possibility that unexpected teams may beat ranked teams.

so, given this data from form responses:

Column B                             C                  D               E                 F                            G                                   H                     I                   J                          K                             L

Team Name	Date of GAME	Your (Top 25ish) team's score?	Top 25 ish opponent?	Top 25ish opponent's score	Any note you wish to add about this game:	"newcomer" school name:	Date of GAME	newcomer team's score?	Top 25 ish opponent?	Top 25ish team's score?
Oak Bay	1/19/2015	59	SMU	56
newcomer						St. Patrick's	1/19/2015	75	Saint George's	72
Byng	1/17/2015	51	Kitsilano	54
Byng	1/17/2015	52	Kelowna	51

I am hoping for this output:

  

          Column A              B                     C                     D                  E

Winner	Pts.	Loser	Pts.	Game Date
Oak Bay	59	SMU	56	1/19/2015
St. Patrick's	75	Saint George's	72	1/19/2015
Kitsilano	54	Byng	51	1/17/2015
Byng	52	Kelowna	51	1/17/2015

So, with Yogi's help, I've got ArrayFormulas working to get me the correct winning and losing team names, and correct game date.  It is the points scored by the winner and the loser that are now giving me difficulty.  

The following formula gets me the correct points in B2, but I can't make it work as an ArrayFormula

   =if(Resp!B2="newcomer", Max(Resp!J2,Resp!L2), MAX(Resp!D2,Resp!F2))

This formula references the form response sheet ("Resp") and initially checks whether an unranked, unexpected team has appeared ("newcomer"), if it has, the formula then looks for the larger score in cells J2 and L2. If the game was one between two ranked teams, it looks for the highest score in D2 or F2. When I tried using the same arrayformula format as Yogi suggested for the team name columns, things went sideways:

=ArrayFormula(if(Resp!B2:B="newcomer", Max(Resp!J2:J,Resp!L2), Max(Resp!D2:D,Resp!F2:F))

Just resulted in the formula looking for the highest score, irrespective of the row it appeared in. All rows on my "sorted" sheet displayed the highest score any team achieved in the entire array:

Winner	Pts.	Loser	Pts.	Current Rank (winner)	Current Rank (loser)
Oak Bay	90	SMU	30
St. Patrick's	75	Saint George's	30
Kitsilano	90	Byng	30
Byng	90	Kelowna	30
Kitsilano	90	Byng	30

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

yogi_Count Number Of Items In aDataSheet That Are Older Than Specified Number Of Days Have Status As Specified And Are of Specific Color

             Google Spreadsheet   Post  #1885

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

post by  Munkey (David):
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/AM2YVafG4Hc
Using COUNTIFS & DATEDIF without a helper column, anyone had any success with this

Hello all,

Hope we are all well?

 

Just trying to get my head around a problem, wondering if anyone else had solved it first.

Aim:

I have three columns,

Timestamp, Status and Type

Timestamp is a date and type.

Status - is either outstanding or completed

 

Type - In my example it's colours, only 4, but in reality it's a text string, but there are only a few types.

 

Basically, I wanted to try to count OUTSTANDING cases that are 28 days or older, by type.

The data is in aDataSheet

and would like the results in aReportSheet somewhere.

So for example, I'm trying to count
1) Cases 28 days or over

AND

2) are currently outstanding
AND

3) That are type "Red"

I've tried using COUNTIFS with DATEDIF and could never ever get it working, whatever I tried.
Have managed to get the results using FILTER and COUNT ( no helper column needed)

The formula is:

=Count(Filter(aDataSheet!A:C,aDataSheet!C:C=ʺRedʺ,aDataSheet!B:B=ʺOutstandingʺ,aDataSheet!A:A,datedif(aDataSheet!A:A,now(),ʺDʺ)>=28))

Ideally I think a countifs with datedif might be a little less intensive on the spreadsheet.
Want to avoid helper columns as they will bloat the sheet with data.

have shared an example sheet here.

 

https://docs.google.com/spreadsheets/d/11tB-o7kR2x6HzZnSm1o4OdU6N7rpX1jGAoz7JXLTQtE/edit#gid=969429723

 

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

yogi_Compute From Table In Sheet1 Number Of Days By Name For Each Of The Attributes In DropDownList In Cell A1

             Google Spreadsheet   Post  #1884

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

post by  Russell Haner - NOAA Federal:
https://productforums.google.com/forum/#!mydiscussions/docs/IAAmSIla9sc
Names, Dates, and Cell Values

Aargh!

I have a spread sheet with column A being a list of names.

Row 1 is the dates for a year (365 columns).

Each name and date combination can have one of three values.

In a separate sheet, with 13 columns (column A is names, column B-L are months), I would like to show how many of a given value a given name has during a month.

For instance an employee can be "at work", "on call", or "on vacation" for any given day.

I'd like to identify how many days that a given employee was "on call" in July.

Of course, the actual spreadsheet is 300+ names and a full year, so it's a bit ungainly.

Ideally I'd like to be able to select a name from a drop down list and have the monthly totals auto fill.

thanks,

Russell

---

The gist of it is that I want to summarize a year's worth of personnel status "ready, willing, or able", by month.

the excel spreadsheet that I've attached is downloaded from Google, so should be the same.  I tried to share the actual google sheet, but that didn't seem to work.

When I tried to use countif functions to look at the name in column A, the dates in row 2, and the cell values , I got an error that the countifs functions were not the same size. 

I think it would work with countif functions if I had a single column with 365 row entries for each name, the next three columns would identify the date for each person, each day, and the next column would have the daily status, but that would mean that the table would be 365 rows for each employee...not a problem if there's only a few, but with 300+, it gets too unwieldy. 

Thanks,

Russell

Attachments (1)

Names, Dates, and Cell Values.xlsx

9 KB   View   Download

yogi_Conditionally Format Cell J6 Background Red If only J5 is checked (=x) Yellow if only K5 is checked Green if only L5 is checked

             Google Spreadsheet   Post  #1883

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

post by  Chaos234:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/__lrhlfqKZE
Why is Google Sheets so tupied to parse formula correctly?

Actually I'm very angry about GS!

All what I want is a simple color fomarted cell over a formula!

Conditonal formating is not useable because there is one missin field! It has to have a condition field, a field from how the data in the condition is to catch/to check and a field which zell is affected by the result of the condition and that isn't possible yet!

So I tried the following:

=IF(J5="x",ROWS(G6:G8)=TEXT(F5, "#ff0000"),"") but all I get is a Circular dependency detected and that can't also be possible!

What I want to do?

I want to change the text color red in the affected zell, if J5 is checked (value = x). But if K5 is checked, then the text color should be yellow and if L5 is checked, it should be green.

For what is the Sheet?

This sheet contains a development tree with some conditions. If you want to use rockets, you may have to develop fuel first so the sheet must also check, if the prerequired development is

developed.

Is there a macro or a GS-Code sniped for that to sol this?

Also it will be fine if you can only check one from three from J5 to L5 and not more.

Hope that I can get some help to solve this problem.

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

yogi_Conditionallty Format Rows If Same Date (column B) And Name (column D) Occur More Than Two Times

             Google Spreadsheet   Post  #1882

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

post by  TN Dispatch:
https://productforums.google.com/forum/#!mydiscussions/docs/O-qBgP_njDU
how to color a row of cells if a value occurs more then twice

I am looking for a script or formula that will color coat a row if a value occurs more then once on the same date. I have attatched the sheet for reference. If you notice column b has dates c has company names and d has names i would like if adam with tn transport has more then 2 entrys on the 6th then those rows are colored orange with blue text. 

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

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

yogi_Pull From Sheet Named 'MASTER' Values For Non-Blank Columns Of A Specified Name In Column A

             Google Spreadsheet   Post  #1881

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

post by  vaughanF1:
https://productforums.google.com/forum/#!searchin/docs/automoatically$20hide$20columns/docs/dhi_LpaPFvk/O1nC_XREM0AJ
Hide Columns while using a query and import function

I've searched many forums and have found a couple of options of hiding columns that have no values, but I can't seem to put the formula together with the other formula's I am using.

My existing formula is =QUERY(MASTER!A:V, "select A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V where A contains 'MARCEL' order by A", 2)

These are the problems I need help with:

1. Not all of these columns will have values where A contains 'Marcel', so I need some help with using the best way to hide those columns with no values. I was trying the filter option, but happy with any.

2. Is there anyway to import the formatting of the cells? When I import now it's only the values that come over. It would be great to have the orange fill colour import into my other sheets as well.

Here is a link to the sheet I have been working on https://docs.google.com/spreadsheets/d/1gxu2OVUjgeDT6xJkpIBloAkpghAT-cbKfWkmNIhQ-GQ/edit?usp=sharing

Thank you for any help you could give.

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

yogi_Work With Displaying Hours Minutes And Seconds With 60 Minutes Shown As 60 Minutes Rather Than An Hour

             Google Spreadsheet   Post  #1880

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

post by  Bon H:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9kfBfWLGmPc
How can I stop the spreadsheet from rounding my times up?
I need to enter 89:60:00 into a cell, but it immediately changes to 90:00:00. Have tried everything I think of to do with formatting cells, but nothing works. Works fine on desktop spreadsheet program... Please Help!
------------------------------------------------------------------------------------------------------------------------------------

yogi-Conditionally Format First Instance of A Date In Range A2 to A

             Google Spreadsheet   Post  #1879

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

post by  Noelle Devoe:
https://productforums.google.com/forum/#!mydiscussions/docs/RvNcqVCc1Ug
Highlight First Instance Of Date In Column Using Conditional Formatting?

I would like to highlight the first instance of a date in a column using conditional formatting, but I can for the life of me find the answer to this question.

Basically, in column A of my Google sheet, I have a list of dates and some repeat many times. I would like Sheets to highlight the first instance of every date. So it would be something like this (bold represents a highlighted cell):

1/1/2015

1/2/2015

1/2/2015

1/2/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/5/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

1/6/2015

If anyone could help me figure this out, it would be greatly appreciated. And if it can't be done with conditional formatting, any direction as to how to achieve this would be much appreciated!

Thanks!

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