# Winning a Loosing Streak

##### New Member
Hi Guys,

I do have a question about winning and loosing streak in my hockey league.

I have data where player can play home or away.
What would be the most efficient way to count winning and loosing streak?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### wideboydixon

##### Well-known Member
If you have a column to record Win/Lose then you can use something like this:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

WBD

##### New Member
My data is bit more complicated.

Player can play away and home matches.

Outcomes for results are WRT - WOT - WPP these are all Wins
LRT , LOT, LPP these are all Loses

#### wideboydixon

##### Well-known Member

With the level of complication, I think you'll need VBA and not a formula:

Code:
``````Public Sub FindStreaks()

Dim lastPlayer As Long
Dim thisPlayer As Long
Dim lastRow As Long
Dim thisRow As Long
Dim lastResult As String
Dim thisResult As String
Dim currentStreak As Long

' Find the last row of data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Find the last row of player names
lastPlayer = Cells(Rows.Count, "N").End(xlUp).Row

' Process each player
For thisPlayer = 2 To lastPlayer
' Blank out the last result and streak
lastResult = ""
currentStreak = 0

' Process the data rows from bottom to top
For thisRow = lastRow To 2 Step -1
' Determine whether the current player won, lost or didn't play
thisResult = ""
If Cells(thisRow, "D").Value = Cells(thisPlayer, "N").Value Then
If Left\$(Cells(thisRow, "E").Value, 1) = "W" Then
thisResult = "W"
Else
thisResult = "L"
End If
ElseIf Cells(thisRow, "J").Value = Cells(thisPlayer, "N").Value Then
If Left\$(Cells(thisRow, "I").Value, 1) = "W" Then
thisResult = "W"
Else
thisResult = "L"
End If
End If

' If this player played then either accumulate or terminate the streak
If thisResult <> "" Then
If lastResult = "" Then
lastResult = thisResult
currentStreak = 1
Else
If thisResult = lastResult Then
currentStreak = currentStreak + 1
Else
Exit For
End If
End If
End If
Next thisRow

' Choose one of the lines below
Cells(thisPlayer, "O").Value = CStr(currentStreak) & " " & lastResult
Cells(thisPlayer, "O").Value = currentStreak * IIf(lastResult = "L", -1, 1)
Next thisPlayer

End Sub``````

WBD

##### New Member
Where will I see the result?

##### New Member
 Team1 WRT LRT LRT LRT WRT WRT LRT LRT LRT LRT

<colgroup><col span="2"></colgroup><tbody>
</tbody>

I am able to dynamically extract the last matches played. would it be possible to use regular formula?

#### wideboydixon

##### Well-known Member
For the VBA, press [Alt]+[F11] to bring up the editor, select Insert->Module and paste the code above. Close the editor and go back to the sheet. Press [Alt]+[F8] to select and run the macro.

WBD

Replies
3
Views
120
Replies
23
Views
2K
Replies
23
Views
263
Replies
3
Views
72
Replies
3
Views
102