Sunday, January 25, 2015

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 NameDate of GAMEYour (Top 25ish) team's score?Top 25 ish opponent?Top 25ish opponent's scoreAny note you wish to add about this game:"newcomer" school name:Date of GAMEnewcomer team's score?Top 25 ish opponent?Top 25ish team's score?
Oak Bay1/19/201559SMU56
newcomerSt. Patrick's1/19/201575Saint George's72
Byng1/17/201551Kitsilano54
Byng1/17/201552Kelowna51


I am hoping for this output:
  
          Column A              B                     C                     D                  E
WinnerPts.LoserPts.Game Date
Oak Bay59SMU561/19/2015
St. Patrick's75Saint George's721/19/2015
Kitsilano54Byng511/17/2015
Byng52Kelowna511/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:

WinnerPts.LoserPts.Current Rank (winner)Current Rank (loser)
Oak Bay90SMU30
St. Patrick's75Saint George's30
Kitsilano90Byng30
Byng90Kelowna30
Kitsilano90Byng30
--------------------------------------------------------------