Winning a Loosing Streak

[wladoD]

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?

 

[wideboydixon]

If you have a column to record Win/Lose then you can use something like this:

Book1
	A	B
1	Result	Streak
2	Win	Win 1
3	Win	Win 2
4	Lose	Lose 1
5	Lose	Lose 2
6	Lose	Lose 3
7	Win	Win 1
8	Win	Win 2
9	Win	Win 3
10	Lose	Lose 1
11	Win	Win 1
12	Win	Win 2
13	Lose	Lose 1
14	Lose	Lose 2
15	Lose	Lose 3
16	Lose	Lose 4
Sheet1

Cell Formulas
Range	Formula
B2	{=$A2&" "&(ROW($B2)-MAX(IF($A$1:$A1<>$A2,ROW($A$1:$A1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[wladoD]

 

[wladoD]

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]

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

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	ROUND	TEAM 1	PLAYER 1		H	A	R		PLAYER 2	TEAM 2	DATE
2	3	1. Round	Edmonton Oilers	Tosino23	WRT	2	1	RT	LRT	wladoD	Edmonton Oilers	22-Jul-17		Tosino23	-2
3	44	Quaterfinal	Anaheim Ducks	alphafxdb	LRT	4	6	RT	WRT	dawo043	St. Louis Blues	23-Jul-17		wladoD	1
4	45	Quaterfinal	Edmonton Oilers	Tosino23	LRT	0	2	RT	WRT	wladoD	Chicago Blackhawks	24-Jul-17		alphafxdb	-3
5	46	Quaterfinal	St. Louis Blues	dawo043	WRT	3	2	RT	LRT	alphafxdb	Anaheim Ducks	25-Jul-17		dawo043	-3
6	47	Quaterfinal	Chicago Blackhawks	wladoD	WRT	1	0	RT	LRT	Tosino23	Edmonton Oilers	26-Jul-17		Biculinho	-1
7	48	Quaterfinal	Anaheim Ducks	alphafxdb	LPP	1	2	PP	WPP	dawo043	St. Louis Blues	27-Jul-17		FlatOut3232	3
8	49	Semifinal	Washington Capitals	Biculinho	LRT	1	5	RT	WRT	wladoD	Chicago Blackhawks	28-Jul-17
9	50	Semifinal	Nashville Predators	FlatOut3232	WRT	6	2	RT	LRT	dawo043	St. Louis Blues	29-Jul-17
10	51	Semifinal	Chicago Blackhawks	wladoD	WRT	5	2	RT	LRT	Biculinho	Washington Capitals	30-Jul-17
11	52	Semifinal	St. Louis Blues	dawo043	LRT	1	5	RT	WRT	FlatOut3232	Nashville Predators	31-Jul-17
12	53	Semifinal	Washington Capitals	Biculinho	WRT	1	0	RT	LRT	wladoD	Chicago Blackhawks	01-Aug-17
13	54	Semifinal	Nashville Predators	FlatOut3232	WRT	4	3	RT	LRT	dawo043	St. Louis Blues	02-Aug-17
14	55	Semifinal	Chicago Blackhawks	wladoD	WRT	5	1	RT	LRT	Biculinho	Washington Capitals	03-Aug-17
Sheet1

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

 

[wladoD]

Can you please advice where to add VBA code and how will it work?
Where will I see the result?

 

[wladoD]

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Team1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[wideboydixon]

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