Baseball Series Wins, Losses, and Ties

[Stephen_IV]

I want to thank James006 for helping me with my last post. In this post I would like to calculate Wins, Losses, and Ties for a series please see below. Thanks in advance!

A series refers to two or more consecutive games played between the same two teams

Book1 A B C D E F G H I J K L 1 Date Opp W/L Team W L T 2 4/8/2023 BOS W BOS 1 1 3 4/9/2023 BOS W TOR 3 1 4 4/10/2023 BOS L BAL 3 1 5 4/11/2023 TOR L DET 2 6 4/12/2023 TOR W CLE 2 7 4/13/2023 TOR L KCR 1 8 4/14/2023 TOR W TEX 1 9 4/15/2023 BAL L CHW 1 1 10 4/16/2023 BAL W TBR 2 1 11 4/17/2023 BAL L LAA 1 12 4/19/2023 DET W MIN 1 13 4/20/2023 DET W CHC 1 14 4/21/2023 DET L HOU 1 15 4/22/2023 CLE W OAK 1 16 4/23/2023 CLE W PIT 1 17 4/24/2023 CLE W 18 4/26/2023 BAL W 19 4/27/2023 BAL W 20 4/28/2023 BAL W 21 4/29/2023 KCR W 22 4/30/2023 KCR W 23 5/1/2023 KCR W 24 5/2/2023 TOR W 25 5/3/2023 TOR W 26 5/4/2023 TOR L 27 5/8/2023 TEX W 28 5/8/2023 TEX L 29 5/9/2023 TEX W 30 5/10/2023 TOR W 31 5/11/2023 TOR W 32 5/12/2023 CHW W 33 5/13/2023 CHW W 34 5/14/2023 CHW L 35 5/15/2023 CHW W 36 5/16/2023 BAL W 37 5/17/2023 BAL W 38 5/18/2023 BAL W 39 5/19/2023 BAL L 40 5/21/2023 CHW W 41 5/22/2023 CHW L 42 5/22/2023 CHW L 43 5/23/2023 BAL L 44 5/24/2023 BAL W 45 5/25/2023 BAL W 46 5/26/2023 TBR W 47 5/27/2023 TBR W 48 5/28/2023 TBR L 49 5/29/2023 TBR L 50 5/31/2023 LAA W 51 6/2/2023 LAA W 52 6/2/2023 LAA W 53 6/3/2023 DET W 54 6/4/2023 DET W 55 6/5/2023 DET W 56 6/7/2023 MIN W 57 6/8/2023 MIN L 58 6/9/2023 MIN W 59 6/10/2023 CHC W 60 6/11/2023 CHC W 61 6/12/2023 CHC W 62 6/14/2023 TBR W 63 6/15/2023 TBR W 64 6/16/2023 TBR W 65 6/17/2023 TOR W 66 6/18/2023 TOR W 67 6/19/2023 TOR L 68 6/20/2023 TBR W 69 6/21/2023 TBR L 70 6/22/2023 TBR W 71 6/23/2023 HOU W 72 6/24/2023 HOU L 73 6/25/2023 HOU L 74 6/26/2023 HOU W 75 6/27/2023 OAK W 76 6/28/2023 OAK W 77 6/29/2023 OAK W 78 6/30/2023 HOU L 79 7/2/2023 CLE W 80 7/2/2023 CLE W 81 7/3/2023 CLE L 82 7/5/2023 PIT L 83 7/6/2023 PIT W 84 7/7/2023 BOS W 85 7/8/2023 BOS W 86 7/9/2023 BOS L 87 7/10/2023 BOS L Sheet1 Cell Formulas Range Formula H2:H16 H2 =UNIQUE(B2:B87) Dynamic array formulas.

 

[OaklandJim]

Is data in the second table (to the right) the actual counts of W, L, T? If not maybe provide real results so we know what to see if a solution works.

I am trying to understand the objective.

Scenarios

1. There are two BOSs in a row and both are wins (W W). Does that count as one or two wins in the table?

2. There are three BOSs in a row and each is a win (W W W). Does that count as one or three wins in the table?

3. There are three BOSs in a row and wind/losses are W L W. Does that count as none, one or two?

 

[Stephen_IV]

This should help.

Book1 A B C D 1 Date Opp W/L 2 4/8/2023 BOS W W 3 4/9/2023 BOS W 4 4/10/2023 BOS L 5 4/11/2023 TOR L T 6 4/12/2023 TOR W 7 4/13/2023 TOR L 8 4/14/2023 TOR W 9 4/15/2023 BAL L L 10 4/16/2023 BAL W 11 4/17/2023 BAL L 12 4/19/2023 DET W W 13 4/20/2023 DET W 14 4/21/2023 DET L 15 4/22/2023 CLE W W 16 4/23/2023 CLE W 17 4/24/2023 CLE W 18 4/26/2023 BAL W W 19 4/27/2023 BAL W 20 4/28/2023 BAL W 21 4/29/2023 KCR W W 22 4/30/2023 KCR W 23 5/1/2023 KCR W 24 5/2/2023 TOR W W 25 5/3/2023 TOR W 26 5/4/2023 TOR L 27 5/8/2023 TEX W W 28 5/8/2023 TEX L 29 5/9/2023 TEX W 30 5/10/2023 TOR W W 31 5/11/2023 TOR W 32 5/12/2023 CHW W W 33 5/13/2023 CHW W 34 5/14/2023 CHW L 35 5/15/2023 CHW W 36 5/16/2023 BAL W W 37 5/17/2023 BAL W 38 5/18/2023 BAL W 39 5/19/2023 BAL L 40 5/21/2023 CHW W L 41 5/22/2023 CHW L 42 5/22/2023 CHW L 43 5/23/2023 BAL L W 44 5/24/2023 BAL W 45 5/25/2023 BAL W 46 5/26/2023 TBR W T 47 5/27/2023 TBR W 48 5/28/2023 TBR L 49 5/29/2023 TBR L 50 5/31/2023 LAA W W 51 6/2/2023 LAA W 52 6/2/2023 LAA W 53 6/3/2023 DET W W 54 6/4/2023 DET W 55 6/5/2023 DET W 56 6/7/2023 MIN W W 57 6/8/2023 MIN L 58 6/9/2023 MIN W 59 6/10/2023 CHC W W 60 6/11/2023 CHC W 61 6/12/2023 CHC W 62 6/14/2023 TBR W W 63 6/15/2023 TBR W 64 6/16/2023 TBR W 65 6/17/2023 TOR W W 66 6/18/2023 TOR W 67 6/19/2023 TOR L 68 6/20/2023 TBR W W 69 6/21/2023 TBR L 70 6/22/2023 TBR W 71 6/23/2023 HOU W T 72 6/24/2023 HOU L 73 6/25/2023 HOU L 74 6/26/2023 HOU W 75 6/27/2023 OAK W W 76 6/28/2023 OAK W 77 6/29/2023 OAK W 78 6/30/2023 HOU L 79 7/2/2023 CLE W W 80 7/2/2023 CLE W 81 7/3/2023 CLE L 82 7/5/2023 PIT L T 83 7/6/2023 PIT W 84 7/7/2023 BOS W T 85 7/8/2023 BOS W 86 7/9/2023 BOS L 87 7/10/2023 BOS L Sheet1

 

[OaklandJim]

As far as I can tell the attached workbook does what you want. Note that it is macro enabled as I found it necessary to use a "user defined function" (UDF) to do what you want.

The workbook is HERE.

Here is the function. Often other listers improve my code, sometimes a lot.

Function GetCount(psTeam As String, prAnchorCellTeamsGames As Range, psWLT As String) As Long

'   This is the "header" cell for the column containg teams for each game.
'   As of 4/16/23 this is cell B1.
    Dim rAnchorCellTeamsGames As Range
    
'   Range containg team names in the games data.
    Dim rRangeTeams As Range
    
'   Used for looping
    Dim rCell As Range
    
'   Count of consecutive games found (i.e., for a series)
    Dim iConsecutivesFound As Long
    
'   For a given series these two variables contain a count of wins and losses.
    Dim iWinsInASeries As Long
    Dim iLossesInASeries As Long

'   For all series (for the specified team) these three variables contain
'   a count of wins, losses and ties.
    Dim iWinsAllSeries As Long
    Dim iLossesAllSeries As Long
    Dim iTiesAllSeries As Long
    
'   Number of games in the games data to process.
    Dim iGameRows As Long
    
'   The current team in the games data being processed.
    Dim sTeamCurrent As String
    
'   The next team in the games data to be processed.
    Dim sTeamNext As String
    
    sTeamNext = ""
    
    sTeamCurrent = ""
    
    iWinsInASeries = 0
    
    iLossesInASeries = 0
    
    iWinsAllSeries = 0
    
    iLossesAllSeries = 0
    
    iTiesAllSeries = 0
    
'   Count rows containg teams in the games data.
    iGameRows = prAnchorCellTeamsGames.Offset(100000).End(xlUp).Row

'   Set range of cells containing teams in the games data.
    Set rRangeTeams = prAnchorCellTeamsGames.Offset(1).Resize(iGameRows - 1)

'   Process all cells in the Teams cells range.
    For Each rCell In rRangeTeams
    
        sTeamCurrent = rCell.Value
    
        sTeamNext = rCell.Offset(1).Value
    
'       Look for cells containing consecutive games with the same team -- a series.
        If sTeamCurrent = psTeam _
         Then
            iConsecutivesFound = iConsecutivesFound + 1
        Else
            
            iConsecutivesFound = 0
            iWinsInASeries = 0
            iLossesInASeries = 0
        
        End If
        
'       If consecutive cells include the same team -- a series -- then
'       increment iWinsInASeries or iLossesInASeries.
        If iConsecutivesFound <> 0 _
         Then
            
            If rCell.Offset(0, 1).Value = "W" _
             Then
                iWinsInASeries = iWinsInASeries + 1
            Else
                iLossesInASeries = iLossesInASeries + 1
            End If

        End If

'       If we have encountered the end of a series then consecutive games found > 0
'       And the next team to process is not the same as the current team.
        If iConsecutivesFound <> 0 And sTeamNext <> sTeamCurrent _
         Then
            If iWinsInASeries = iLossesInASeries _
             Then
                iTiesAllSeries = iTiesAllSeries + 1
            
            ElseIf iWinsInASeries > iLossesInASeries _
             Then
                iWinsAllSeries = iWinsAllSeries + 1
            Else
                iLossesAllSeries = iLossesAllSeries + 1
            End If
            
        End If

    Next rCell
    
'   Return the "type" of value specified by parameter psWLT:
'   1. WINS All Series, or 2. LOSSES All Series, or 3. TIES All Series
    
    If psWLT = "W" _
     Then
        GetCount = iWinsAllSeries
    ElseIf psWLT = "L" _
     Then
        GetCount = iLossesAllSeries
    Else
        GetCount = iTiesAllSeries
    End If

End Function

 

[Stephen_IV]

OaklandJim that does the job! I appreciate it. Thanks for spending your time on this post. I greatly appreciate your help!

 

[OaklandJim]

A comment in the code is incorrect.

This line of code

'       If we have encountered the end of a series then consecutive games found > 0

Should be this

'       If we have encountered the end of a series then consecutive games found <> 0

 

[planetpj]

I know that you have a VBA solution but I have been working on a Dynamic Array for this:

=VSTACK({"Home","W","L","T"},LET(ra,B2:B87,rb,C2:C87,a,TEXTSPLIT(TEXTBEFORE(UNIQUE(ra&"|"&LET(b,ra,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(s,c,IF(c=1,1,IF(INDEX(b,c-1)<>INDEX(b,c),s+1,s)))))),"|"),"-","|"),
u,UNIQUE(TEXTSPLIT(TEXTBEFORE(UNIQUE(ra&"|"&LET(b,B2:B87,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(s,c,IF(c=1,1,IF(INDEX(b,c-1)<>INDEX(b,c),s+1,s)))))),"|"),"-","|")),
h,LET(u,SCAN(0,SEQUENCE(ROWS(ra)),LAMBDA(s,c,IF(c=1,1,IF(INDEX(ra,c-1)<>INDEX(ra,c),s+1,s)))),v,UNIQUE(ra&"|"&LET(b,ra,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(s,c,IF(c=1,1,IF(INDEX(b,c-1)<>INDEX(b,c),s+1,s)))))),s,BYROW(v,LAMBDA(x,SUMPRODUCT(--(ra&"|"&u=x)*(rb="W")/SUMPRODUCT(--(ra&"|"&u=x)*(rb<>""))))),IF(s>0.5,"W",IF(s<0.5,"L","T"))),
m,MAP(u,LAMBDA(x,SUM(IF(ISERROR(FILTER(a,(x=a)*(h="W"))),0,1)))),
n,MAP(u,LAMBDA(x,SUM(IF(ISERROR(FILTER(a,(x=a)*(h="L"))),0,1)))),
o,MAP(u,LAMBDA(x,SUM(IF(ISERROR(FILTER(a,(x=a)*(h="T"))),0,1)))),
HSTACK(u,m,n,o)))

 

[Stephen_IV]

Thank you so much. I appreciate all of the help and guidance. Did not know that was even possible. I greatly appreciate it.