How would I do this?

[kmprice710]

I have a long list of college basketball scores.

I want to find the longest consecutive road losing streak of one team vs. any one other team. For example, let's say Oregon State lost 15 consecutive times as the road team vs. UCLA. How would I find this? How would I find the longest consecutive home winning streak by any team against any one other team.

season	date	road	road_score	home	home_score
2017-18	12/6/2017	Belmont	58​	Texas State	62​
1972-73	1/4/1973	Belmont	49​	Texas	44​
1971-72	1/19/1972	Belmont	70​	Texas Tech	76​
2017-18	11/13/2017	Indiana State	83​	Texas State	69​
1995-96	11/25/1995	Indiana State	82​	Texas State	78​
1972-73	2/26/1973	Indiana State	92​	Indiana	85​
1971-72	1/24/1972	Indiana State	95​	Wichita State	72​
1970-71	3/1/1971	Indiana State	72​	Texas State	57​
1971-72	12/7/1971	Boise State	78​	North Texas	73​
2014-15	12/20/2014	Boise State	77​	North Texas	33​
2014-15	11/29/2014	UCLA	76​	North Texas	56​
2017-18	11/24/2017	Furman	80​	Texas State	85​
2018-19	3/2/2019	Central State	55​	Texas State	67​
2017-18	1/20/2018	Central State	63​	North Texas	80​
2016-17	2/9/2017	Central State	84​	Texas State	73​
2015-16	1/2/2016	Central State	61​	Texas State	62​
2016-17	12/3/2016	West Virginia	82​	North Texas	85​
2015-16	11/24/2015	Colorado	108​	Texas State	100​
2018-19	11/15/2018	Denver	61​	Texas State	67​
2017-18	3/12/2018	Drake	80​	Texas State	73​
2015-16	12/22/2015	Drake	87​	North Texas	70​
2013-14	11/9/2013	Duquesne	94​	Texas State	75​
2014-15	12/28/2014	Grand Canyon	83​	Texas State	81​
1971-72	12/17/1971	Hardin-Simmons	69​	UCLA	71​
2014-15	12/6/2014	Houston	71​	Texas State	59​
2017-18	1/10/2018	Houston Baptist	74​	Texas State	82​
2016-17	3/4/2017	Houston Baptist	81​	Texas State	72​
2015-16	2/18/2016	Houston Baptist	73​	Oregon State	58​
2014-15	2/2/2015	Houston Baptist	77​	Texas State	82​
2018-19	2/16/2019	Incarnate Word	48​	Texas State	63​
2017-18	3/3/2018	Incarnate Word	59​	Texas State	67​
2016-17	1/26/2017	Incarnate Word	82​	Texas State	83​

 

[sijpie]

The easiest will be to do this through a macro.
In the macro you would first make a unique list of all the 'road' teams and a unique list of all the receiving teams (they would probably be the same, but in case someone wants to use this principle on something else)
Then you'd set up an array with road teams in the rows and receiving teams as the columns.
Next, after sorting on date, you read the total input list into an array for fast processing
Then for each road team you count the number of consecutive losses. Once you have a win, you store the number in the first array. Continue to the end of the list. If there is a larger count it replaces the stored number.
You do this for each of the receiving teams.
Then you loop to the next road team.

Finally you check what the highest number is in the first array.

Sub GetLongestLoss()
    Dim colRoad As Collection, colRec As Collection
    Dim vIn As Variant, vCountMatrix As Variant
    Dim lRi As Long, lRcm As Long, UB As Long
    Dim iCRo As Integer, iCRe As Integer, iLoss As Integer
    Dim rIn As Range
    Dim sRoadT As String, sRecT As String
    
    Set rIn = ActiveSheet.Range("A1").CurrentRegion
    
    rIn.Sort rIn.Cells(1, 2), xlAscending, Header:=xlYes
    vIn = rIn.Value
    UB = UBound(vIn, 1) 'number of rows
    
    'get unique road teams. Do this by using a collection with the team name as key, only unique keys are allowed
    Set colRoad = New Collection
    Set colRec = New Collection
    On Error Resume Next    ' to avoid stop when trying to add duplicate key
    For lRi = 2 To UB 'skip header
        colRoad.Add vIn(lRi, 3), vIn(lRi, 3)  'road teams in 3rd column
    Next lRi
    'do the same for the receiving teams
    For lRi = 2 To UB 'skip header
        colRec.Add vIn(lRi, 5), vIn(lRi, 5)  'receiving teams in 5th column
    Next lRi
    On Error GoTo 0     'reset error handling behaviour to normal
    
    ' size the output(counting) array
    ReDim vCountMatrix(1 To colRoad.Count + 1, 1 To colRec.Count + 1)
    'Now loop through the road teams
    For iCRo = 1 To colRoad.Count
        sRoadT = colRoad.Item(iCRo)
        'loop for each receeiving team
        For iCRe = 1 To colRec.Count
            sRecT = colRec.Item(iCRe)
            iLoss = 0
            'go through the scoring list
            For lRi = 2 To UB
                If vIn(lRi, 3) = sRoadT And vIn(lRi, 5) = sRecT Then
                    If CInt(vIn(lRi, 4)) < CInt(vIn(lRi, 6)) Then 'Loss
                        iLoss = iLoss + 1
                    Else    'Win, end of losing streak
                        If iLoss > vCountMatrix(iCRo + 1, iCRe + 1) Then vCountMatrix(iCRo + 1, iCRe + 1) = iLoss
                        iLoss = 0 'end of streak, reset to 0
                    End If
                End If
            Next lRi
            'end of scoring list, check loss count
            If iLoss > vCountMatrix(iCRo + 1, iCRe + 1) Then
                vCountMatrix(iCRo + 1, iCRe + 1) = iLoss
            ElseIf IsEmpty(vCountMatrix(iCRo + 1, iCRe + 1)) Then
                vCountMatrix(iCRo + 1, iCRe + 1) = 0
            End If
        Next iCRe
    Next iCRo
        
    'fill matrix headings
    For iCRo = 1 To colRoad.Count
    vCountMatrix(iCRo + 1, 1) = colRoad.Item(iCRo)
    Next iCRo
    For iCRe = 1 To colRec.Count
    vCountMatrix(1, iCRe + 1) = colRec.Item(iCRe)
    Next iCRe
    
    ' >>>>>> set your output range below. I have made it to Cell I1, but modify to taste
    Range("I1").Resize(UBound(vCountMatrix, 1), UBound(vCountMatrix, 2)).Value = vCountMatrix
    
    'clean up
    Set colRoad = Nothing
    Set colRec = Nothing
    Set rIn = Nothing
End Sub