Counting Wins And Losses

[Daviboy30]

I'm running a scoresheet for my league and I need to track each player's wins and losses record throughout the season, including the previous season, so that I can assign a proper player ranking. If a player wins or loses 7 out of 10 matches his ranking goes up or down, depending on how he (or she) does. Players often do not play every week, but I still need to track their wins and losses through multiple seasons. More often than not player ranking don't change because they don't meet the 7 out of ten threshold, but when they do, I want the spreadsheet to automatically post the new ranking.

In the attached Mini-sheet example Joe's ranking remained the same because over a period of 13 weeks he played 10 matches, but did not meet the 7 out of 10 wins or losses threshold. Meanwhile Jack's ranking went from 6 up to 7 because over a period of 15 weeks he played 10 matches and won 7 of them. In Mary's example, her ranking went down because over a period of 14 weeks she played 10 matches and lost 7 times. If a player rank goes up then the counting of wins and losses starts fresh.

The problem I'm having is how to automatically track a player's last 10 matches whether he plays the matches in 10 weeks, 10 months or even 10 years.

IFTestBook2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	Player	Current Rank	Current Wins	Current Losses	New Rank	1/1	1/8	1/15	1/22	1/29	2/5	2/12	2/19	2/26	3/5	3/12	3/19	3/26	4/2	4/9	4/16	4/23	4/30
4	Joe	4	6	5	4	W	W	W	L		L	W	W	L		W	W	W	L		W		L
5	Jack	6	7	3	7	W			W	L		W	W	L			W	W		L		W	W
6	Mary	3	3	7	2		L	W		L	W	L	L	L	W	W			L	L			L
Sheet1

Cell Formulas
Range	Formula
C4	C4	=COUNTIF(K4:W4,"W")
D4:D5	D4	=COUNTIF(H4:W4,"L")
E4:E6	E4	=IF(C4>6, B4+1, IF(D4>6, B4-1, IF(B4=2, 2, IF(B4=3, 3, IF(B4=4, 4, IF(B4=5, 5, IF(B4=6, 6, IF(B4=7, 7))))))))
C5	C5	=COUNTIF(I5:W5,"W")
C6	C6	=COUNTIF(J6:W6,"W")
D6	D6	=COUNTIF(J6:W6,"L")

 

[Peter_SSs]

I took the minisheet and the code from #49.

That is not what your mini-sheet in post #50 shows. If you look at #49, PN, CR, CW etc are in row 3 while you have them in row 1.

 

[Daviboy30]

You're right. I don't know how to use these XL2LL minisheets. What your sheets on #49 display are rows 3, 4 and 5 only and that's all it copied. I will try again.

 

[Daviboy30]

I recreated the spreadsheet with the proper number of rows and columns and rebuilt Joe's W and Ls just as you had written in #49 step by step and I got your same result! Success!

I have a couple of comments:

• The computer is more logical than I am because when you changed cell 2 to an L, I would have thought that Joe's rank at cell 27 would be 6, but I see now logically he is actually a 4. All because of the one change in cell 2. It doesn't seem fair, but there it is...
• I am struggling with how to keep the spreadsheet no longer than a year at a time. I was thinking, there has to be some overlap from one year to the next because players' rankings build on from previous seasons. So I'm thinking that the sheet could hold 14 months of the calendar year -- one month before and one month after -- to allow for the collection of the data. (?) The code uses row 4 to count the W and Ls, I assume. I would not want the numbering in row 4 to continue from the the previous sheet. I would like to restart the numbering with every new calendar spreadsheet. Am I making sense?

 

[Peter_SSs]

I don't know how to use these XL2LL minisheets. What your sheets on #49 display are rows 3, 4 and 5 only and that's all it copied.

Yes, when you click the copy icon at the top left of the mini-sheet, it just copies the range that is actually in the mini-sheet.
So if the top left cell in the mini-sheet is A3, then select A3 in your blank sheet before you Paste if you want the data to go into the same cells.

I am struggling with how to keep the spreadsheet no longer than a year at a time. I was thinking, there has to be some overlap from one year to the next because players' rankings build on from previous seasons. So I'm thinking that the sheet could hold 14 months of the calendar year -- one month before and one month after -- to allow for the collection of the data. (?) The code uses row 4 to count the W and Ls, I assume. I would not want the numbering in row 4 to continue from the the previous sheet. I would like to restart the numbering with every new calendar spreadsheet.

Try this changed layout and changed code.
Suppose that Joe had finished last year with a rank of 5 and 3 current wins and 2 current losses.
Then set up the new year sheet for Joe like this.

Daviboy30_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
3	PN	COW	COL	CR	CW	CL	NR	LR
4									1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
5	Joe	3	2	5
P54

COW stands for 'Carry Over Wins' and COL is 'Carry over Losses'.
If testing this layout and code ends up working, you could hide columns B and C after you have entered everybody's carried over wins/losses/ranks if you wanted to.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As Variant
  Dim K As Long, i As Long, j As Long, r As Long, lc As Long, Wclr As Long, Lclr As Long
  Dim W As Long, L As Long, OldRank As Long, NewRank As Long, UpDown As Long, LastReset As Long, BaseRank As Long

  Const FirstDataRow As Long = 5
  Const FirstDataCol As Long = 9

  If Target.CountLarge = 1 And Target.Column >= FirstDataCol And Target.Row >= FirstDataRow Then
    Wclr = RGB(112, 173, 71)
    Lclr = RGB(237, 125, 49)
    r = Target.Row
    lc = Cells(FirstDataRow - 1, Cells(r, Columns.Count).End(xlToLeft).Column).Value + 1
    If lc < 2 Then lc = 2
    W = Cells(r, FirstDataCol - 7).Value
    L = Cells(r, FirstDataCol - 6).Value
    K = W + L
    OldRank = Cells(r, FirstDataCol - 5).Value
    If OldRank < 2 Then OldRank = 2
    BaseRank = OldRank
    For i = FirstDataCol To lc + FirstDataCol - 1
      Select Case Cells(r, i).Interior.Color
        Case Wclr: BaseRank = BaseRank - 1
        Case Lclr: BaseRank = BaseRank + 1
      End Select
    Next i
    Rows(r).Interior.Color = xlNone
    a = Split(" " & Join(Application.Index(Cells(r, FirstDataCol).Resize(, lc).Value, 1, 0)))
    For i = 1 To UBound(a)
      Do
        If Len(a(i + j)) > 0 Then
          If UCase(a(i + j)) = "W" Then
            W = W + 1
          Else
            L = L + 1
          End If
          K = K + 1
          If K >= 10 Then
            If W > 6 Then
              UpDown = UpDown + 1
              Cells(r, FirstDataCol).Offset(, i + j - 1).Interior.Color = Wclr
              LastReset = i + j
              i = i + j
              W = 0
              L = 0
              K = 0
              j = 0
            ElseIf L > 6 Then
              UpDown = UpDown - 1
              Cells(r, FirstDataCol).Offset(, i + j - 1).Interior.Color = Lclr
              LastReset = i + j
              i = i + j
              W = 0
              L = 0
              K = 0
              j = 0
            End If
            Exit Do
          End If
        End If
        j = j + 1
      Loop Until i + j = UBound(a) + 1 Or K = 10
      If i + j - 1 >= UBound(a) Then i = UBound(a)
    Next i
    NewRank = BaseRank + UpDown
    If NewRank < 2 Then NewRank = 2
    If NewRank > 7 Then NewRank = 7
    Application.EnableEvents = False
    Cells(r, FirstDataCol - 4).Resize(, 4).Value = Array(W, L, NewRank, LastReset)
    If NewRank <> OldRank Then MsgBox "Rank for " & Range("A" & r) & " will change from " & OldRank & " to " & NewRank
    Cells(r, FirstDataCol - 5).Value = NewRank
    Cells(r, FirstDataCol - 2).ClearContents
    Application.EnableEvents = True
  End If
End Sub

So the code is supposed to use those carried over values when calculating the new results. For example, after a W is entered you see that CW is 4, being the carried over 3 wins plus the new win just entered. CL is 2, being just the 2 carried over losses from last year.

Daviboy30_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	PN	COW	COL	CR	CW	CL	NR	LR
4									1	2	3
5	Joe	3	2	5	4	2		0	W
P54

After I have entered some more values, Joe's rank increased from the carried over 5 to 6 due to the fact that he has 4 wins this year plus 3 from last year and the 10-game condition is met by the 5 games this year plus the carried over 5 games (3 wins, 2 losses) from last year. Since that last reset at cell 6, his current wins and losses are now both 1.

Daviboy30_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3	PN	COW	COL	CR	CW	CL	NR	LR
4									1	2	3	4	5	6	7	8	9	10	11
5	Joe	3	2	6	1	1		6	W	W	W	W		L	W		L
P54

 

[Daviboy30]

It worked perfectly! Thank you! This will save me tons of time trying to keep track of player rankings.

PlayerRankingSystem_211201.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
2	Player Name	Carry Over Wins	Carry Over Losses	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	Sa		Su		Mo		Tu		We		Th		Fr		Sa		Su
3									1/1		1/2		1/3		1/4		1/5		1/6		1/7		1/8		1/9
4									1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
5
6	Joe	3	2	5	0	0		18	W	W		L	W	W	L	L		W	W	L		L	L	L	L	L
Sheet1

 

[Peter_SSs]

You're welcome. Glad we got there in the end. I hope it continues to work as expected. ?