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 think what you are saying is: if an error occurs, you want to manually reset his Current Rank, Current Wins and Current Losses to what they should be instead of making the computer recalculate all the Ws and Ls in order to reset his ranking to the proper rank.

No that is not what I am saying. What I am saying is

.. if an error occurs, you want to manually reset his Current Rank, Current Wins and Current Losses to what they should be by making the computer recalculate all the Ws and Ls in order to reset his ranking to the proper rank.

The code needs to accept whatever the numbers are in the Current Rank, Current Wins and Current Losses columns and build from there. Does that make sense?

No that does not make sense to me. If a value is changed near the start of the results (example cell J6 in your mini-sheet above is changed to W) then Current rank, Current Wins and current Losses would likely all be incorrect. So accepting those and building from there would also almost certainly produce incorrect results.

I have to say that this is getting very complex and I am not seeing an feasible solution emerging. Given that, and the large amount of time I have already put into trying to develop something, unless something emerges very soon it will likely mean that I will have to abandon further attempts.

 

[Daviboy30]

I agree this may be too complex a task. To get this right the computer would have to calculate each and all the Ws Ls entries every time a new entry is made -- basically rebuilding the numbers from the beginning every time a new entry is made. I think...

Anyway, I appreciate and thank you for all you have done.

 

[Peter_SSs]

To get this right the computer would have to calculate each and all the Ws Ls entries every time a new entry is made -

Yes, that is what I was getting at if an early entry is altered. In fact, if we did that every time a (single) cell in the row was changed, that would make it easier.
Try this code ..

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
 
  Const FirstDataRow As Long = 5
  Const FirstDataCol As Long = 7
 
  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
    Rows(r).Interior.Color = xlNone
    lc = Cells(FirstDataRow - 1, Cells(r, Columns.Count).End(xlToLeft).Column).Value + 1
    If lc < 2 Then lc = 2
    OldRank = Cells(r, FirstDataCol - 5).Value
    If OldRank < 2 Then OldRank = 2
    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 = 2 + 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

.. with this worksheet layout.

Daviboy30_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Player Name	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	1		2		3		4		5
4							1	2	3	4	5	6	7	8	9	10
5	Joe
6	Jon
7	Rich
P43

Note that I have not tested extensively so there will likely still be glitches. If you find errors then please try to document exactly what you had before you made the last entry and what and where that last entry was. Also, describe the error/wrong result.

 

[Daviboy30]

Thank you for continuing to plug away at this problem.
I like the color fill when the player rank changes, however several issues come with this version of the code:

1. The code won't accept any Current Rank except 2. If you manually enter any rank other than 2 at the beginning before you have entered any Ws and Ls, then you enter your first W or L, it will notify you that it is changing the rank back to 2.
2. You get a notification when you change rank from 2 to 3 and the cell changes color to green, but when you enter a W or L in the next cell after the change in rank, you get a notification that it is changing the rank back to a 2. The code won't accept a rank other than 2.
3. The cell color changed to orange or green when it reached the 7 out of 10 threshold, but the colors disappeared as soon as you entered a value in the next cell. And when the value is deleted in the next cell, the color came back
4. Current Wins and Losses reset to 0 after a rank change, but it also appears to stop counting of Ws and Ls after the rank change as well as you enter more values. (I need to test this a little more)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	Player Name	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	1	2	3	4	5
3
4							1	2	3	4	5	6	7	8	9	10
5
6	Joe	2	2	0		0	W	W	W	W	W	L	L	L	W	W	W	W	W	W	W	W	W
7
8	Jon	2	2	0		0	W	W	W	W	W	L	L	L	W	W	L
9
10	Rich	2	0	0		10	L	L	W	W	W	L	L	L	L	L
11
Sheet1

 

[Peter_SSs]

1. The code won't accept any Current Rank except 2. If you manually enter any rank other than 2 at the beginning before you have entered any Ws and Ls, then you enter your first W or L, it will notify you that it is changing the rank back to 2.

We seem to be going around in circles here.
Let us suppose that you have 50 entries in a row and that produces a current rank of 5. Now you discover that the very first entry was incorrect so you change it. That would mean that the whole previous set of rank calculations and the reset cell colours could be incorrect. My questions are:

1. In that circumstance, how can you possibly use the current rank of 5 (which was arrived at using all 50 entries including the original incorrect first value) to now calculate what the actual current rank should be?
2. Given that the calculation of the 50 entries cannot use the current rank of 5 as a starting point when re-calculating because the first value changed, what rank should the code use as a starting point for the recalculation for the 50 entries?

You also seemed to be agreeing with my logic when you said:

To get this right the computer would have to calculate each and all the Ws Ls entries every time a new entry is made -- basically rebuilding the numbers from the beginning every time a new entry is made.

2. You get a notification when you change rank from 2 to 3 and the cell changes color to green, but when you enter a W or L in the next cell after the change in rank, you get a notification that it is changing the rank back to a 2. The code won't accept a rank other than 2.

I think that you may find that is because you did not extend the row 4 numbers beyond column P. The code uses those numbers so they should extend at least as far as your data will go.

3. The cell color changed to orange or green when it reached the 7 out of 10 threshold, but the colors disappeared as soon as you entered a value in the next cell. And when the value is deleted in the next cell, the color came back

Same reason as the last point

4. Current Wins and Losses reset to 0 after a rank change, but it also appears to stop counting of Ws and Ls after the rank change as well as you enter more values.

Could be the same reason again. See if extending the row 4 numbers fixes that.

 

[Daviboy30]

I can't explain this, but the code is performing differently (and properly) than it did yesterday. Yesterday the rank would revert to 2 no matter how many times a player ranked up. Today the code is accepting manually entered ranks 3 thru 7, even before any Ws or Ls are entered, and then the rank goes up or down after it reaches the proper threshold of 7 out 10. This is exactly what I am looking for.

It is not, however, filling the cell with a color after the threshold is reached like it was yesterday. I liked that.

As I said, I can't explain yesterday from today...

To answer your questions:

In that circumstance, how can you possibly use the current rank of 5 (which was arrived at using all 50 entries including the original incorrect first value) to now calculate what the actual current rank should be?

I can offer only two solutions:

1. Make the computer calculate from the beginning every time a new entry is made, erased or changed. I however believe this could become very cumbersome and time consuming as more and more entries are made.
2. Allow the computer to accept the Current Rank, Wins and Losses numbers as is, even if the numbers have been manually entered. That way if there is a mistake, then it can be corrected manually. I prefer solution no. 2.

This iteration of the code is very usable and I am going to try it out in a more real-world test.

 

[Peter_SSs]

It is not, however, filling the cell with a color after the threshold is reached like it was yesterday.

Well, I have not changed the code in your workbook, so I have no idea about that.
Hopefully since then you have perhaps closed Excel and re-opened and it is working again now.

Can you confirm that you have extended the row 4 numbers right across from column G at least as far as you are entering data?

Can you confirm that you have not altered the layout by adding or deleting rows or columns?

 

[Daviboy30]

The answers to your questions: I had not extended the row 4 numbers beyond 10 and I had not altered the layout by adding or deleting rows or columns.

So this morning I decided to start from scratch again.

• Using your layout and code from #43, I extended the row 4 numbers out to 23 and proceeded to fill in data.
• I did not give Joe a rank, manually or otherwise. When I filled out Joe with Ws and Ls it immediately assigned Joe a rank of 2 and he moved up to a 3 when it reached the threshold of 7 out of 10, then again he moved back down to a 3 when it reached the threshold in losses.
• I manually assigned Jon and Rich Current Ranks of 4 and 5 respectively, before I had entered any Ws and Ls. When I entered an L for Jon in cell G8 it immediately notified me that it was changing his rank from a 4 to a 2.
• I then went to Rich, who I had manually given a rank of 5, and entered a W in cell G10. Again the computer immediately notified me that it was changing his rank from a 5 to a 2. I continued to fill out Wins and Losses for Rich, finishing with a rank of 4. (See Rich-5>2.png)
• The code is not letting me manually enter ranks.
• I did not try altering Current Wins or Losses numbers to see if that affected things.
• If I had the ability to manually alter Current Rank, Wins and Losses numbers and have the computer calculate based on those altered Current numbers, when new Ws and Ls are entered, I think that would be the best solution.

IFTestBook9-Peter.xlsx
	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	AC
1
2	Player Name	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	1	2	3	4	5
3
4							1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
5
6	Joe	2	0	0		20	W	W	L	W	W	W	L	L	W	W	L	L	L	L	L	W	W	L	L	L
7
8	Jon	2	0	1		0	L
9
10	Rich	4	0	0		20	W	W	W	W	W	W	L	L	L	W	W	W	W	W	W	L	L	W	W	L
Sheet1

 

[Peter_SSs]

I had been struggling with how to deal with current rank if one of the early values in a row was changed. This is a possible way.
Take the example below (headings just abbreviated to save space) where I started with B5:F5 all blank and just entered all the Ws and Ls one at a time.

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	AC	AD	AE	AF	AG	AH	AI	AJ
3	PN	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	21	22	23	24	25	26	27	28	29	30
5	Joe	4	0	7		20	W	W	W	W	W	L	L	L	W	W	W	W	W	W	W	W	W	L	L	L	L	L	L	L	L	L	L
P49

Now I decide to manually change the current rank (CR) to 6

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	AC	AD	AE	AF	AG	AH	AI	AJ
3	PN	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	21	22	23	24	25	26	27	28	29	30
5	Joe	6	0	7		20	W	W	W	W	W	L	L	L	W	W	W	W	W	W	W	W	W	L	L	L	L	L	L	L	L	L	L
P49

I now realise that cell 2 for Joe is incorrect and change it from 'W' to 'L'.
What my new code below attempts to do is before anything else, look across that row and say, "OK, there are two green cells so the 'base rank' for this player must have been 2 less than whatever is showing in the CR column. So Joe's 'base rank' is 4. Having changed cell 2 to an 'L', Joe's first change of rank moves from cell 10 to cell 11 and cell 20 is no longer an increase in rank but in fact cell 24 becomes a reduction in rank. That is, now Joe has one increase and one decrease in rank. Given that his 'base rank' was 4, his actual new rank will also be 4. Also, since his last reset is now in cell 24 his current count of wins/losses is 0/3. Is all that logic correct?

Here is my sheet, using the code below, after making that one change in cell 2.

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	AC	AD	AE	AF	AG	AH	AI	AJ
3	PN	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	21	22	23	24	25	26	27	28	29	30
5	Joe	4	0	3		24	W	L	W	W	W	L	L	L	W	W	W	W	W	W	W	W	W	L	L	L	L	L	L	L	L	L	L
P49

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 = 7
 
  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
    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

 

[Daviboy30]

I'm not the same results as you

• I took the minisheet and the code from #49.
• I added rows for Jon and Rich
• I manually assigned Jon a Current Rank of 5 and left Rich's rank at blank
• I proceeded to add a W and L for Jon. It started counting Wins and Losses for two cells, but then it stopped counting after that. No matter how many Ws or Ls were added, the CW and CL numbers didn't change after the second cell
• I got the same result with Rich. When I entered the first W in cell G7 it assigned Rich a rank of 2, a CW number of 1 and CL number of 0. I entered a L in cell H7 and it gave Rich a CL of 1. The numbers didn't change after that, no matter how many Ws or Ls were added. Below is minisheet of my results

IFTestBook10-Peter.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	AC	AD	AE	AF	AG	AH	AI	AJ
1	PN	CR	CW	CL	NR	LR
2							1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
3	Joe	4	0	3		24	W	L	W	W	W	L	L	L	W	W	W	W	W	W	W	W	W	L	L	L	L	L	L	L	L	L	L
4
5	Jon	5	2	0		0	W	W	W	W	L	W	W	W	W	W	W	W	W	W	W
6
7	Rich	2	1	1		0	W	L	W	W		L	W
Sheet1

• Just to be thorough, I created a brand new worksheet with the same cell, column and row structure, saved it as a macro enabled file, then restarted Excel. I basically got the same results as the previous file.