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 removed the extra column after column A, I cleared all the data, then restarted Excel. I then entered a"W" in the first column under "Jon" (G4) and got the error message. Attached is a screen capture of the VBA code.

Thanks for the extra information, screen shot etc. It has confused me a little though.

• Jon is on row 6 so if entering a "W" for him that should be G6?
• I can reproduce the error you reported if I re-enter the 1 in cell G4 but the error is on a different line of code than the one you reported. This error for me is caused because the layout has changed again. previously the first row of player data was row 4 but now it is row 5.
• On one occasion I was able to reproduce the error on the line you reported but that was when I deleted a particular cell value. The current code is not actually designed to handle deletions, only additions or changes and only to the right of the 'Last Reset'**
• Your last mini-sheet shows merged cells in rows 7 & 8. Merged cells do not generally work well with vba and they will certainly cause my code to error. Hopefully you do not actually need merged cells?

** Does every player start this sheet with no current rank (or I guess that is the min rank of 2)? I am asking because this whole process (vba or formulas) may be simpler if we ignored that 'Last Reset' and just calculated all the players changes right from the start each time and published their current rank and current wins/losses. That way we could probably handle deletions, changes anywhere in the row, multiple entries at once etc. I would envisage still showing the "Last Reset" so that when looking at the worksheet you knew where that was but for the next calculation it would all be calculated from scratch again. That would mean for example that if some values were deleted (eg because they were accidentally entered in the wrong row) then the "Last Reset" could in fact be moved backwards.

Post back if you would like to investigate the above option but in the interim you could try this version. As well as trying to avert the error, this version should allow you more easily to alter the code if the layout changes by just altering the 'Const' lines near the start of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As Variant
  Dim W As Long, L As Long, K As Long, i As Long, j As Long, r As Long, wk As Long, lc
  Dim OldRank As Long, NewRank As Long, UpDown As Long, LastReset As Long
 
  Const FirstDataRow As Long = 5
  Const FirstDataCol As Long = 8    '<- Col H
  Const CurrentRankCol As Long = 3  '<- Col C
 
  If Target.CountLarge = 1 And Target.Column >= FirstDataCol And Target.Row >= FirstDataRow Then
    r = Target.Row
    LastReset = Cells(r, CurrentRankCol + 4).Value
    wk = Target.Column - FirstDataCol + 1
    If wk > LastReset Then
    OldRank = Cells(r, CurrentRankCol).Value
    lc = Cells(r, Columns.Count).End(xlToLeft).Column
    If lc < Target.Column Then lc = Target.Column
    a = Split(" " & Join(Application.Index(Range(Cells(r, LastReset + FirstDataCol), Cells(r, 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
                LastReset = LastReset + i + j
                i = i + j
              ElseIf L > 6 Then
                UpDown = UpDown - 1
                LastReset = LastReset + i + j
                i = i + j
              End If
              Exit Do
            End If
          End If
          j = j + 1
        Loop Until i + j = UBound(a) + 1 Or K = 10
        If i + j = UBound(a) + 1 Then
          i = UBound(a)
        Else
          W = 0
          L = 0
          K = 0
          j = 0
        End If
      Next i
      NewRank = Cells(r, CurrentRankCol).Value + UpDown
      If NewRank < 2 Then NewRank = 2
      If NewRank > 7 Then NewRank = 7
      Application.EnableEvents = False
      Cells(r, CurrentRankCol + 1).Resize(, 4).Value = Array(W, L, NewRank, LastReset)
      If NewRank <> OldRank Then MsgBox "New Rank for " & Range("A" & r) & " will now be moved to Current Rank and New Rank cell cleared"
      Cells(r, CurrentRankCol).Value = NewRank
      Cells(r, CurrentRankCol + 3).ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[Daviboy30]

I'm sorry that I have created so much confusion by moving columns and rows. I didn't realize that VBA coding was so inflexible.

• The way our rules work, the lowest rank possible is 2, the highest rank possible is 7. You will never be lower than a 2 no matter how many times you lose, and you will never be higher than a 7 no matter how many times you win. Someday this may change, but for now this what it is.
• We have different rules for men and women if they have never played before or don't have any ranking: an entry-level woman always starts at 2 and men always start at 4.
• Sometimes the commissioner has stepped in and moved a player up (or down) because he realizes the player is better than (or worse) his or her ranking. This rarely happens, but when it does, the commissioner assigns a more appropriate ranking to the player.

The ranking system is important because we don't want to end up with uncompetitive matches, and in fact players are not allowed to play against another player with a ranking that is 2 or more above or below.

Back to the worksheet: I have two rows for some players because on occasion a player will play two matches in one day. The two rows allow me to enter a W or L for each match. I don't have to merge the cells in columns B, C, D, E and F for a particular player, but I do need to have two slots available for that day to enter the data. I had previously in other draft worksheets had two slots available and arranged in the same row, instead of vertically in the same column, but I was finding that the worksheet was becoming too long horizontally. Anyway, I'm open to other ideas on how to enter W and Ls if a player plays twice. A player will never play more than two matches on any particular day.

I changed the formula ranges so that they all now start in column F. I replaced the VBA code with your latest code and I am now getting a different error in the code. See the attached screen capture. The error occurs whenever I enter a W or L in any column after G. The error does not appear when I enter data in column F.

IFTestBook4-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	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV
1							2021
2							July																			August																															September																														October																															November																														December
3	Player	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr
4	First Name						13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	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	31	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	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	31	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	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	31
5	Joe	6	2	0			W	W
6	Jon	2	2	0			W				W
7	Rich	2	0	10			L																					L									L															L													L																																	L																					L
8							L	L						L
Sheet1

Cell Formulas
Range	Formula
C5:C6	C5	=LEN(RIGHT(CONCAT(G5:AVY5),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G5:AVY5),10),"W",""))
D5	D5	=LEN(RIGHT(CONCAT(G5:AVY5),10))-C5
D6	D6	=LEN(RIGHT(CONCAT(G6:AVZ6),10))-C6
C7	C7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G7:AVY8),10),"W",""))
D7	D7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-C7

 

[Peter_SSs]

Your data, code and descriptions are all different.
- One seems to be saying that the first data column (ie the first W or L) is column F,
- one is showing that the first data column is column G
- and one is showing the first data column is H

The error does not appear when I enter data in column F.

According to your mini-sheet, column F is 'Last Reset' so you should not be entering anything there as the code records the last reset. You certainly should not be putting a "W" or "L" in that column if your mini-sheet is correct.

Your code still says

yet your mini-sheet shows that the first data column is G. So if your mini-sheet is correct then that line should say

Const FirstDataCol As Long = 7

The issue of multiple rows rows for a single player is going to make things much harder. It would mean that a player's rank could change in as little as 5 weeks (if 2 matches per week).
Also, how would we record the "Last Reset" for those players? Image that a player play 2 matches in a day and after the first match of the day they reach a new rank. In that case the second match of the day becomes part of their "current" wins or losses. Another player might play 2 matches on the same day and their rank might change after the second of the two matches. In that case the second match would not count as current wins/losses. So, having a "Last Rest" saying week 11 and there being two matches in week 11 would not be sufficient to determine whether to count the second match in the "current" or not.

I'm not really seeing a good way to deal with that yet. In any case, unless we can resolve the process for people with a single row there is not much use worrying about the 2-row scenario at this stage.

 

[Daviboy30]

I'm sorry, my description was wrong: the error occurs when I enter a W or L in column H -- the second column (or any other column after column G). There is no error when I enter Ws and Ls in column G.
Concerning player's rank changing on the same day between the two matches: It was decided that a player would play his second match at his previous rank level, even though technically he had moved up or down in rank. But going forward, the second game would still count towards any future rank changes.

 

[Peter_SSs]

my description was wrong:

.. but also your code does not match your worksheet. If the mini-sheet given in post 22 is the layout that you are actually using then these two highlighted values are both incorrect

Your First Data column is not column 8 in that layout.
The Current Rank column is not column 3 in that layout.

You need to match all those 'Const' lines to what you actually have in the worksheet that you are using.

 

[Daviboy30]

I see there is a difference in your three lines of code and the code that my worksheet is using. I changed the 'Const' code and I'm still getting an error message. Here is my complete code and another minisheet (I've highlighted where the code error is occurring):

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

Const FirstDataRow As Long = 5
Const FirstDataCol As Long = 8 '<-
Const CurrentRankCol As Long = 3 '

If Target.CountLarge = 1 And Target.Column >= FirstDataCol And Target.Row >= FirstDataRow Then
r = Target.Row
LastReset = Cells(r, CurrentRankCol + 4).Value
wk = Target.Column - FirstDataCol + 1
If wk > LastReset Then
OldRank = Cells(r, CurrentRankCol).Value
lc = Cells(r, Columns.Count).End(xlToLeft).Column
If lc < Target.Column Then lc = Target.Column
a = Split(" " & Join(Application.Index(Range(Cells(r, LastReset + FirstDataCol), Cells(r, 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
LastReset = LastReset + i + j
i = i + j
ElseIf L > 6 Then
UpDown = UpDown - 1
LastReset = LastReset + i + j
i = i + j
End If
Exit Do
End If
End If
j = j + 1
Loop Until i + j = UBound(a) + 1 Or K = 10
If i + j = UBound(a) + 1 Then
i = UBound(a)
Else
W = 0
L = 0
K = 0
j = 0
End If
Next i
NewRank = Cells(r, CurrentRankCol).Value + UpDown
If NewRank < 2 Then NewRank = 2
If NewRank > 7 Then NewRank = 7
Application.EnableEvents = False
Cells(r, CurrentRankCol + 1).Resize(, 4).Value = Array(W, L, NewRank, LastReset)
If NewRank <> OldRank Then MsgBox "New Rank for " & Range("A" & r) & " will now be moved to Current Rank and New Rank cell cleared"
Cells(r, CurrentRankCol).Value = NewRank
Cells(r, CurrentRankCol + 3).ClearContents
Application.EnableEvents = True
End If
End If
End Sub

IFTestBook5-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	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV
1							2021
2							July																			August																															September																														October																															November																														December
3	Player	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr
4	First Name						13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	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	31	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	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	31	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	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	31
5	Joe	6	2	0			W	W
6	Jon	2	3	0			W	W			W
7	Rich	2	0	9			L																					L									L															L													L																																	L																					L
8							L							L
Sheet1

Cell Formulas
Range	Formula
C5:C6	C5	=LEN(RIGHT(CONCAT(G5:AVY5),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G5:AVY5),10),"W",""))
D5	D5	=LEN(RIGHT(CONCAT(G5:AVY5),10))-C5
D6	D6	=LEN(RIGHT(CONCAT(G6:AVZ6),10))-C6
C7	C7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G7:AVY8),10),"W",""))
D7	D7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-C7

 

[Daviboy30]

Ignore my previous post.
I changed the values to 7 and 2 in the 'Const' lines to indicate the proper columns, and now, when I input a W or L in the first column (column G), I'm getting an error in a different part of the code. Previously it was the second column (column H) that caused the error. Below is my current code with the problem code highlighted:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a As Variant
  Dim W As Long, L As Long, K As Long, i As Long, j As Long, r As Long, wk As Long, lc
  Dim OldRank As Long, NewRank As Long, UpDown As Long, LastReset As Long
 
  Const FirstDataRow As Long = 5
  Const FirstDataCol As Long = 7    '<-
  Const CurrentRankCol As Long = 2  '
 
  If Target.CountLarge = 1 And Target.Column >= FirstDataCol And Target.Row >= FirstDataRow Then
    r = Target.Row
    LastReset = Cells(r, CurrentRankCol + 4).Value
    wk = Target.Column - FirstDataCol + 1
    If wk > LastReset Then
    OldRank = Cells(r, CurrentRankCol).Value
    lc = Cells(r, Columns.Count).End(xlToLeft).Column
    If lc < Target.Column Then lc = Target.Column
    a = Split(" " & Join(Application.Index(Range(Cells(r, LastReset + FirstDataCol), Cells(r, 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
                LastReset = LastReset + i + j
                i = i + j
              ElseIf L > 6 Then
                UpDown = UpDown - 1
                LastReset = LastReset + i + j
                i = i + j
              End If
              Exit Do
            End If
          End If
          j = j + 1
        Loop Until i + j = UBound(a) + 1 Or K = 10
        If i + j = UBound(a) + 1 Then
          i = UBound(a)
        Else
          W = 0
          L = 0
          K = 0
          j = 0
        End If
      Next i
      NewRank = Cells(r, CurrentRankCol).Value + UpDown
      If NewRank < 2 Then NewRank = 2
      If NewRank > 7 Then NewRank = 7
      Application.EnableEvents = False
      Cells(r, CurrentRankCol + 1).Resize(, 4).Value = Array(W, L, NewRank, LastReset)
      If NewRank <> OldRank Then MsgBox "New Rank for " & Range("A" & r) & " will now be moved to Current Rank and New Rank cell cleared"
      Cells(r, CurrentRankCol).Value = NewRank
      Cells(r, CurrentRankCol + 3).ClearContents
      Application.EnableEvents = True
    End If
  End If
End Sub

IFTestBook5-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	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV
1							2021
2							July																			August																															September																														October																															November																														December
3	Player	Current Rank	Current Wins	Current Losses	New Rank	Last Reset	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr
4	First Name						13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	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	31	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	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	31	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	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	31
5	Joe	6	3	0		0	W	W	W
6	Jon	2	2	1		0	W	W	L
7	Rich	2	0	1			L
8
Sheet1

Cell Formulas
Range	Formula
C7	C7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G7:AVY8),10),"W",""))
D7	D7	=LEN(RIGHT(CONCAT(G7:AVZ8),10))-C7

 

[Daviboy30]

I don't know what is going on!!!
There were formulas in cells C5, C6, D5 and D6. And now there aren't. I did not erase the formulas! Is the VBA code erasing the fornulas?

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. My signature block below has more details. Use the "rich" tags if you what to highlight a particular line like you did. I have inserted the tags for you this time in your last post.

With the mini-sheet layout and vba code in post #27 I have not been able to reproduce an error in the line you have highlighted. The only error I have been able to create is by entering a value for player Rich but that error occurs in a different code line and I did say earlier

.. unless we can resolve the process for people with a single row there is not much use worrying about the 2-row scenario at this stage.

I don't know what is going on!!!
There were formulas in cells C5, C6, D5 and D6. And now there aren't. I did not erase the formulas! Is the VBA code erasing the fornulas?

Yes the vba is putting values in those cells. As I said in post #11:

1. I think the calculations required are too complex for standard formulas. (Happy to be proved wrong though)
2. Therefore I am proposing a vba suggestion.

Unless I am not understanding what you are trying to achieve, those formulas you have shown in columns C & D are not doing what you want. They will produce a count of wins and losses in the last 10 matches** but they will not produce a correct count of wins and losses since the last reset.

Take this example (I have hidden a couple of columns) Joe had a rank change at week 10 after 7 or more wins so doesn't the "Current Wins" and "Current Losses" start again at week 11? That would mean Current Wins = 1 and Current losses = 0 wouldn't it? Yet those formulas produce 8 and 2 as shown.

Daviboy30_1.xlsm
	A		C	D		F	G	H	I	J	K	L	M	N	O	P	Q	R
4	Player		Current Wins	Current Losses		Last Reset	1	2	3	4	5	6	7	8	9	10	11	12
5	Joe		8	2		10	W	W	W	W	W	L	W	L	W	W	W
WL (4)

Cell Formulas
Range	Formula
C5	C5	=LEN(RIGHT(CONCAT(G5:AVY6),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G5:AVY6),10),"W",""))
D5	D5	=LEN(RIGHT(CONCAT(G5:AVZ6),10))-C5

** Also, the formulas do not even produce the correct result for the last 10 matches if you have two rows per player. here is an example of the formula as you modified it. It gives 10 current wins and zero current losses and yet we can see that in tha last 10 matches the correct count would be 8 and 2.

Daviboy30_1.xlsm
	A		C	D		F	G	H	I	J	K	L	M	N
4	Player		Current Wins	Current Losses		Last Reset	1	2	3	4	5	6	7	8
7	Rich		10	0			W	W	L	L	W	W	W
8							W	W	W	W	W	W	W
WL (4)

Cell Formulas
Range	Formula
C7	C7	=LEN(RIGHT(CONCAT(G7:AVY8),10))-LEN(SUBSTITUTE(RIGHT(CONCAT(G7:AVY8),10),"W",""))
D7	D7	=LEN(RIGHT(CONCAT(G7:AVZ8),10))-C7

.. back to the error you reported in this line of code

a = Split(" " & Join(Application.Index(Range(Cells(r, LastReset + FirstDataCol), Cells(r, lc)).Value, 1, 0)))

If you can get the error again, can you click Debug and then hover your cursor in that yellow line one at a time over the four variables that I have highlighted red and tell me what values show in the pop-up. Also show the mini-sheet again and also tell me what value you put in what exact cell when the error occurred?

 

[Daviboy30]

This a lot to digest.
There will always be scenarios where a player may have to play twice in one day. So there has to be two slots (cells) to contain that information. That is a given. My current solution is to have the two cells arranged vertically, but I have done score sheets in the past where they are arranged horizontally in the same row. A horizontal solution means that for each player, every playing date will have two slots available to be filled, if needed.

See the attached image of an old score sheet, where John Doe played twice in Week 3, winning the first match and losing the second match, on the same date. There are two slots available each week to be filled if necessary. The W and L information for each player is contained in one row.

Would something like this work for you?