Find names & results in a list and tally them up

[Bender1964]

Hi, first timer on this site. I run a local darts comp here in Australia, and 99% of what we do uses an Excel spreadsheet. We score on Excel, and also keep a running tally of the current seasons wins and losses by players to give us a ranked table. We have many seasons of data available in the form of an all seasons combined results table which lists the 2 players per games names side by side in columns and the points they scored in that game in the next 2 columns. What I would like to achieve is to sort through the table of results to produce a running total of wins and losses for each player against other particular players. This would allow us to know who has the best results against certain players etc.

I have attached a very small screenshot of our historical table. As per the attached table, the players names are in columns A & B, and the score for player 1 is in column C, and the score for player 2 is in column D. As an example, I would like to search through that table to find out how many times Dave Gibson has played Noel McCudden. This would give me a game total between the 2 players. I would then like to search through the sorted results to see how many times in these sorted games was Dave Gibson's score greater than 0 (greater than 0 is a win). This would then provide total games played between the 2 players, and a winning % for both of them against each other. I would then roll this out across all players.

Any help or suggestions are appreciated. I have been trying INDEX & MATCH, but I really don't understand it well enough. I have enough issues with VLOOKUP !!

Thanks Peter

 

[ebea]

Hi Ebea, thanks again for your quick response. I really appreciate the time you have put into this. An example of what I need is as follows, using my data as the source:

Your spreadsheet shows Mick Hayward and Clark Joyce meeting 4 times. I would like to know the overall results of those 4 games. That is, how many has Mick won, and therefore how many Clark has won. The aim of this is to (a) have a career summary of all games won or lost (which your spreadsheet does really well) and (b) how each player has fared in games against each player.

I hope this makes sense.

Thanks Peter

I have updated the file, who already are Linked to, so you can download this. I added an extra Sheet, with the stats, including this with player to player!

 

[Peter_SSs]

but the Sheet was in a size, that I found it not to fit into the windows here in Forum.

Make a smaller example that still demonstrates the principle?

@Bender1964
Here is a macro approach if that interests you. I have assumed that data is in columns A:D and columns H:N can be used for the results (easy to change to another range or even another sheet).
Test with a copy of your workbook.

Sub ResultSummary()
  Dim d As Object
  Dim a As Variant
  Dim sPlayers As String, tmp As String
  Dim i As Long, winner As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If Len(a(i, 1)) * Len(a(i, 2)) > 0 Then
      If a(i, 1) > a(i, 2) Then
        tmp = a(i, 1)
        a(i, 1) = a(i, 2)
        a(i, 2) = tmp
        tmp = a(i, 3)
        a(i, 3) = a(i, 4)
        a(i, 4) = tmp
      End If
      winner = IIf(a(i, 3) > 0, 1, 2)
      sPlayers = a(i, 1) & ";" & a(i, 2)
      If d.exists(sPlayers) Then
        If winner = 1 Then
          d(sPlayers) = ";;" & Split(d(sPlayers), ";")(2) + 1 & ";;" & Split(d(sPlayers), ";")(4)
        Else
          d(sPlayers) = ";;" & Split(d(sPlayers), ";")(2) & ";;" & Split(d(sPlayers), ";")(4) + 1
        End If
      Else
        d(sPlayers) = IIf(winner = 1, ";;1;;0", ";;0;;1")
      End If
    End If
  Next i
  Columns("H:N").Clear
  With Range("H2:I2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Offset(, 2).Resize(, 1).FormulaR1C1 = "=RC[1]+RC[3]"
    With .Offset(, 4).Resize(, 1)
      .NumberFormat = "0.00%"
      .FormulaR1C1 = "=RC[-1]/RC[-2]"
    End With
    With .Offset(, 6).Resize(, 1)
      .NumberFormat = "0.00%"
      .FormulaR1C1 = "=RC[-1]/RC[-4]"
    End With
    .Columns(1).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Rows(0).Resize(, 7).Value = Array("Player 1", "Player 2", "Played", "P1 Wins", "P1 %", "P2 Wins", "P2 %")
  End With
  Columns("H:N").AutoFit
End Sub

Here is my small sample (data has been altered as well as reduced from your sample) and results of the code.

Bender1964 2020-08-05 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ROUND 1 February 6 2019							Player 1	Player 2	Played	P1 Wins	P1 %	P2 Wins	P2 %
2	Mick Hayward	Clark Joyce	0	13				Clark Joyce	Mick Hayward	4	2	50.00%	2	50.00%
3	Ken Selway	Craig Malzard	2	0				Craig Malzard	Ken Selway	3	1	33.33%	2	66.67%
4	Craig Malzard	Dave Gibson	87	0				Craig Malzard	Dave Gibson	4	3	75.00%	1	25.00%
5	Peter Hollins	Manfred Sonntag	0	28				Manfred Sonntag	Peter Hollins	3	1	33.33%	2	66.67%
6	Dave Gibson	Noel McCudden	59	0				Dave Gibson	Noel McCudden	2	1	50.00%	1	50.00%
7								Clark Joyce	Ken Selway	1	1	100.00%	0	0.00%
8	ROUND 2 February 6 2019							Noel McCudden	Peter Hollins	1	0	0.00%	1	100.00%
9	Clark Joyce	Mick Hayward	0	16				Alan Taylor	Manfred Sonntag	1	0	0.00%	1	100.00%
10	Clark Joyce	Ken Selway	41	0				Mick Hayward	Noel McCudden	1	0	0.00%	1	100.00%
11	Peter Hollins	Noel McCudden	133	0				Chris Finch	Ken Selway	1	1	100.00%	0	0.00%
12	Manfred Sonntag	Alan Taylor	34	0				Chris Finch	Clark Joyce	1	0	0.00%	1	100.00%
13								Alan Taylor	Peter Hollins	1	1	100.00%	0	0.00%
14	ROUND 3 February 13 2019
15	Mick Hayward	Clark Joyce	40	0
16	Ken Selway	Craig Malzard	0	129
17	Craig Malzard	Dave Gibson	0	70
18	Peter Hollins	Manfred Sonntag	27	0
19	Dave Gibson	Noel McCudden	0	13
20
21	ROUND 4 February 13 2019
22	Craig Malzard	Dave Gibson	6	0
23	Noel McCudden	Mick Hayward	38	0
24	Chris Finch	Ken Selway	2	0
25	Clark Joyce	Chris Finch	94	0
26	Alan Taylor	Peter Hollins	16	0
27
28	ROUND 5 February 20 2019
29	Mick Hayward	Clark Joyce	0	18
30	Ken Selway	Craig Malzard	210	0
31	Craig Malzard	Dave Gibson	36	0
32	Peter Hollins	Manfred Sonntag	28	0
33	Dave Gibson	Noel McCudden
34
Sheet1

 

[Peter_SSs]

Adding a sort on names might be beneficial? Add the blue line where shown

    .Columns(1).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Rows(0).Resize(, 7).Value = Array("Player 1", "Player 2", "Played", "P1 Wins", "P1 %", "P2 Wins", "P2 %")
    .Resize(, 7).Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, Header:=xlNo
  End With
  Columns("H:N").AutoFit

Results on same data:

Bender1964 2020-08-05 1.xlsm
	H	I	J	K	L	M	N	O
1	Player 1	Player 2	Played	P1 Wins	P1 %	P2 Wins	P2 %
2	Alan Taylor	Manfred Sonntag	1	0	0.00%	1	100.00%
3	Alan Taylor	Peter Hollins	1	1	100.00%	0	0.00%
4	Chris Finch	Clark Joyce	1	0	0.00%	1	100.00%
5	Chris Finch	Ken Selway	1	1	100.00%	0	0.00%
6	Clark Joyce	Ken Selway	1	1	100.00%	0	0.00%
7	Clark Joyce	Mick Hayward	4	2	50.00%	2	50.00%
8	Craig Malzard	Dave Gibson	4	3	75.00%	1	25.00%
9	Craig Malzard	Ken Selway	3	1	33.33%	2	66.67%
10	Dave Gibson	Noel McCudden	2	1	50.00%	1	50.00%
11	Manfred Sonntag	Peter Hollins	3	1	33.33%	2	66.67%
12	Mick Hayward	Noel McCudden	1	0	0.00%	1	100.00%
13	Noel McCudden	Peter Hollins	1	0	0.00%	1	100.00%
14
Sheet1

 

[Bender1964]

Hi Ebea & Peter, thanks again for your input. Peter, I have done as requested and pasted a small data sample below which has the functionality Ebea provided with a manually created table below his table. It is this manually created table that I would love to automate, as we have hundreds of games in our database built up over years. Your help is appreciated. And Peter, I'm happy to work with macros as we have them in our scoring spreadsheet to clear cells and change names etc. Thanks Peter

Mr Excel darts all time games ranking spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ROUND 1		Home score	Away score	Met number of times	Winner / Loser X are winner			Players	Played number of times	Won	Lost	Percent Won	Rank
2	Player 1	Player 6	0	13	2		X		Player 1	10	5	5	50.00%	3
3	Player 2	Player 5	2	0	2	X			Player 2	10	5	5	50.00%	3
4	Player 3	Player 4	87	0	2	X			Player 3	10	1	9	10.00%	6
5									Player 4	10	5	5	50.00%	3
6	ROUND 2								Player 5	10	7	3	70.00%	1
7	Player 1	Player 2	0	16	2		X		Player 6	10	7	3	70.00%	1
8	Player 5	Player 3	41	0	2	X
9	Player 6	Player 4	133	0	2	X
10
11	ROUND 3								The table below is what I would love to have done automatically
12	Player 2	Player 6	40	0	2	X			Results vs each player			Played	Won	Lost	% won
13	Player 3	Player 1	0	129	2		X
14	Player 4	Player 5	0	70	2		X		Player 1	vs	Player 2	2	1	1	50.00%
15											Player 3	2	2	0	100.00%
16	ROUND 4										Player 4	2	1	1	50.00%
17	Player 6	Player 5	6	0	2	X					Player 5	2	1	1	50.00%
18	Player 1	Player 4	38	0	2	X					Player 6	2	0	2	0.00%
19	Player 2	Player 3	2	0	2	X
20
21	ROUND 5
22	Player 3	Player 6	0	18	2		X
23	Player 4	Player 2	210	0	2	X
24	Player 5	Player 1	36	0	2	X
25
26	ROUND 6
27	Player 1	Player 6	0	13	2		X
28	Player 2	Player 5	0	121	2		X
29	Player 3	Player 4	0	59	2		X
30
31	ROUND 7
32	Player 1	Player 2	28	0	2	X
33	Player 5	Player 3	41	0	2	X
34	Player 6	Player 4	0	54	2		X
35
36	ROUND 8
37	Player 2	Player 6	0	40	2		X
38	Player 3	Player 1	0	22	2		X
39	Player 4	Player 5	0	70	2		X
40
41	ROUND 9
42	Player 6	Player 5	0	12	2		X
43	Player 1	Player 4	0	45	2		X
44	Player 2	Player 3	22	0	2	X
45
46	ROUND 10
47	Player 3	Player 6	0	26	2		X
48	Player 4	Player 2	115	0	2	X
49	Player 5	Player 1	0	36	2		X
Ark1

Cell Formulas
Range	Formula
F2:G49	F2	=IF(C2>0,"X","")
J2:J7	J2	=COUNTIF($A$2:$B$49,I2)
K2:K7	K2	=SUMPRODUCT(($A$2:$B$49=$I2)*($F$2:$G$49="X"))
N14:N18,L2:L7	L2	=J2-K2
O14:O18,M2:M7	M2	=K2/J2
N2:N7	N2	=RANK(M2,$M$2:$M$12)
E47:E49,E42:E44,E37:E39,E32:E34,E27:E29,E22:E24,E17:E19,E12:E14,E7:E9,E2:E4	E2	=SUMPRODUCT(($A$2:$A$49=A2)*($B$2:$B$49=B2)+($A$2:$A$49=B2)*($B$2:$B$49=A2))

 

[Peter_SSs]

.. a manually created table below his table. It is this manually created table that I would love to automate,

I just ran my code on the data from post 14. Doesn't the green area contain all the information in your manually created table (+ bit more which could be removed if required)?

Bender1964 2020-08-05 1.xlsm
	A	B	C	D				H	I	J	K	L	M	N
1	ROUND 1		Home score	Away score				Player 1	Player 2	Played	P1 Wins	P1 %	P2 Wins	P2 %
2	Player 1	Player 6	0	13				Player 1	Player 2	2	1	50.00%	1	50.00%
3	Player 2	Player 5	2	0				Player 1	Player 3	2	2	100.00%	0	0.00%
4	Player 3	Player 4	87	0				Player 1	Player 4	2	1	50.00%	1	50.00%
5								Player 1	Player 5	2	1	50.00%	1	50.00%
6	ROUND 2							Player 1	Player 6	2	0	0.00%	2	100.00%
7	Player 1	Player 2	0	16				Player 2	Player 3	2	2	100.00%	0	0.00%
8	Player 5	Player 3	41	0				Player 2	Player 4	2	0	0.00%	2	100.00%
9	Player 6	Player 4	133	0				Player 2	Player 5	2	1	50.00%	1	50.00%
10								Player 2	Player 6	2	1	50.00%	1	50.00%
11	ROUND 3							Player 3	Player 4	2	1	50.00%	1	50.00%
12	Player 2	Player 6	40	0				Player 3	Player 5	2	0	0.00%	2	100.00%
13	Player 3	Player 1	0	129				Player 3	Player 6	2	0	0.00%	2	100.00%
14	Player 4	Player 5	0	70				Player 4	Player 5	2	0	0.00%	2	100.00%
15								Player 4	Player 6	2	1	50.00%	1	50.00%
16	ROUND 4							Player 5	Player 6	2	1	50.00%	1	50.00%
17	Player 6	Player 5	6	0
18	Player 1	Player 4	38	0
19	Player 2	Player 3	2	0
20
21	ROUND 5
22	Player 3	Player 6	0	18
23	Player 4	Player 2	210	0
24	Player 5	Player 1	36	0
25
26	ROUND 6
27	Player 1	Player 6	0	13
28	Player 2	Player 5	0	121
29	Player 3	Player 4	0	59
30
31	ROUND 7
32	Player 1	Player 2	28	0
33	Player 5	Player 3	41	0
34	Player 6	Player 4	0	54
35
36	ROUND 8
37	Player 2	Player 6	0	40
38	Player 3	Player 1	0	22
39	Player 4	Player 5	0	70
40
41	ROUND 9
42	Player 6	Player 5	0	12
43	Player 1	Player 4	0	45
44	Player 2	Player 3	22	0
45
46	ROUND 10
47	Player 3	Player 6	0	26
48	Player 4	Player 2	115	0
49	Player 5	Player 1	0	36
Sheet2 (2)

 

[ebea]

Hi Ebea & Peter, thanks again for your input. Peter, I have done as requested and pasted a small data sample below which has the functionality Ebea provided with a manually created table below his table. It is this manually created table that I would love to automate, as we have hundreds of games in our database built up over years. Your help is appreciated. And Peter, I'm happy to work with macros as we have them in our scoring spreadsheet to clear cells and change names etc. Thanks Peter

Mr Excel darts all time games ranking spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ROUND 1		Home score	Away score	Met number of times	Winner / Loser X are winner			Players	Played number of times	Won	Lost	Percent Won	Rank
2	Player 1	Player 6	0	13	2		X		Player 1	10	5	5	50.00%	3
3	Player 2	Player 5	2	0	2	X			Player 2	10	5	5	50.00%	3
4	Player 3	Player 4	87	0	2	X			Player 3	10	1	9	10.00%	6
5									Player 4	10	5	5	50.00%	3
6	ROUND 2								Player 5	10	7	3	70.00%	1
7	Player 1	Player 2	0	16	2		X		Player 6	10	7	3	70.00%	1
8	Player 5	Player 3	41	0	2	X
9	Player 6	Player 4	133	0	2	X
10
11	ROUND 3								The table below is what I would love to have done automatically
12	Player 2	Player 6	40	0	2	X			Results vs each player			Played	Won	Lost	% won
13	Player 3	Player 1	0	129	2		X
14	Player 4	Player 5	0	70	2		X		Player 1	vs	Player 2	2	1	1	50.00%
15											Player 3	2	2	0	100.00%
16	ROUND 4										Player 4	2	1	1	50.00%
17	Player 6	Player 5	6	0	2	X					Player 5	2	1	1	50.00%
18	Player 1	Player 4	38	0	2	X					Player 6	2	0	2	0.00%
19	Player 2	Player 3	2	0	2	X
20
21	ROUND 5
22	Player 3	Player 6	0	18	2		X
23	Player 4	Player 2	210	0	2	X
24	Player 5	Player 1	36	0	2	X
25
26	ROUND 6
27	Player 1	Player 6	0	13	2		X
28	Player 2	Player 5	0	121	2		X
29	Player 3	Player 4	0	59	2		X
30
31	ROUND 7
32	Player 1	Player 2	28	0	2	X
33	Player 5	Player 3	41	0	2	X
34	Player 6	Player 4	0	54	2		X
35
36	ROUND 8
37	Player 2	Player 6	0	40	2		X
38	Player 3	Player 1	0	22	2		X
39	Player 4	Player 5	0	70	2		X
40
41	ROUND 9
42	Player 6	Player 5	0	12	2		X
43	Player 1	Player 4	0	45	2		X
44	Player 2	Player 3	22	0	2	X
45
46	ROUND 10
47	Player 3	Player 6	0	26	2		X
48	Player 4	Player 2	115	0	2	X
49	Player 5	Player 1	0	36	2		X
Ark1

Cell Formulas
Range	Formula
F2:G49	F2	=IF(C2>0,"X","")
J2:J7	J2	=COUNTIF($A$2:$B$49,I2)
K2:K7	K2	=SUMPRODUCT(($A$2:$B$49=$I2)*($F$2:$G$49="X"))
N14:N18,L2:L7	L2	=J2-K2
O14:O18,M2:M7	M2	=K2/J2
N2:N7	N2	=RANK(M2,$M$2:$M$12)
E47:E49,E42:E44,E37:E39,E32:E34,E27:E29,E22:E24,E17:E19,E12:E14,E7:E9,E2:E4	E2	=SUMPRODUCT(($A$2:$A$49=A2)*($B$2:$B$49=B2)+($A$2:$A$49=B2)*($B$2:$B$49=A2))

I have, as I wrote in #11 updated the file I uploaded previous, so you can redownload that again. It has the function you ask for.
PS: I was thinking on, after a look in all players numbers of times played, that there are a big difference in these. Is this conscious, or because you missing a way to control if all has played against each other, exact number of times . A Robin tournament list, can do the trick. I have such a schedule plan, made in Excel.

 

[ebea]

Make a smaller example that still demonstrates the principle?

@Bender1964
Here is a macro approach if that interests you. I have assumed that data is in columns A:D and columns H:N can be used for the results (easy to change to another range or even another sheet).
Test with a copy of your workbook.

Sub ResultSummary()
  Dim d As Object
  Dim a As Variant
  Dim sPlayers As String, tmp As String
  Dim i As Long, winner As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If Len(a(i, 1)) * Len(a(i, 2)) > 0 Then
      If a(i, 1) > a(i, 2) Then
        tmp = a(i, 1)
        a(i, 1) = a(i, 2)
        a(i, 2) = tmp
        tmp = a(i, 3)
        a(i, 3) = a(i, 4)
        a(i, 4) = tmp
      End If
      winner = IIf(a(i, 3) > 0, 1, 2)
      sPlayers = a(i, 1) & ";" & a(i, 2)
      If d.exists(sPlayers) Then
        If winner = 1 Then
          d(sPlayers) = ";;" & Split(d(sPlayers), ";")(2) + 1 & ";;" & Split(d(sPlayers), ";")(4)
        Else
          d(sPlayers) = ";;" & Split(d(sPlayers), ";")(2) & ";;" & Split(d(sPlayers), ";")(4) + 1
        End If
      Else
        d(sPlayers) = IIf(winner = 1, ";;1;;0", ";;0;;1")
      End If
    End If
  Next i
  Columns("H:N").Clear
  With Range("H2:I2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Offset(, 2).Resize(, 1).FormulaR1C1 = "=RC[1]+RC[3]"
    With .Offset(, 4).Resize(, 1)
      .NumberFormat = "0.00%"
      .FormulaR1C1 = "=RC[-1]/RC[-2]"
    End With
    With .Offset(, 6).Resize(, 1)
      .NumberFormat = "0.00%"
      .FormulaR1C1 = "=RC[-1]/RC[-4]"
    End With
    .Columns(1).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Rows(0).Resize(, 7).Value = Array("Player 1", "Player 2", "Played", "P1 Wins", "P1 %", "P2 Wins", "P2 %")
  End With
  Columns("H:N").AutoFit
End Sub

Here is my small sample (data has been altered as well as reduced from your sample) and results of the code.

Bender1964 2020-08-05 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ROUND 1 February 6 2019							Player 1	Player 2	Played	P1 Wins	P1 %	P2 Wins	P2 %
2	Mick Hayward	Clark Joyce	0	13				Clark Joyce	Mick Hayward	4	2	50.00%	2	50.00%
3	Ken Selway	Craig Malzard	2	0				Craig Malzard	Ken Selway	3	1	33.33%	2	66.67%
4	Craig Malzard	Dave Gibson	87	0				Craig Malzard	Dave Gibson	4	3	75.00%	1	25.00%
5	Peter Hollins	Manfred Sonntag	0	28				Manfred Sonntag	Peter Hollins	3	1	33.33%	2	66.67%
6	Dave Gibson	Noel McCudden	59	0				Dave Gibson	Noel McCudden	2	1	50.00%	1	50.00%
7								Clark Joyce	Ken Selway	1	1	100.00%	0	0.00%
8	ROUND 2 February 6 2019							Noel McCudden	Peter Hollins	1	0	0.00%	1	100.00%
9	Clark Joyce	Mick Hayward	0	16				Alan Taylor	Manfred Sonntag	1	0	0.00%	1	100.00%
10	Clark Joyce	Ken Selway	41	0				Mick Hayward	Noel McCudden	1	0	0.00%	1	100.00%
11	Peter Hollins	Noel McCudden	133	0				Chris Finch	Ken Selway	1	1	100.00%	0	0.00%
12	Manfred Sonntag	Alan Taylor	34	0				Chris Finch	Clark Joyce	1	0	0.00%	1	100.00%
13								Alan Taylor	Peter Hollins	1	1	100.00%	0	0.00%
14	ROUND 3 February 13 2019
15	Mick Hayward	Clark Joyce	40	0
16	Ken Selway	Craig Malzard	0	129
17	Craig Malzard	Dave Gibson	0	70
18	Peter Hollins	Manfred Sonntag	27	0
19	Dave Gibson	Noel McCudden	0	13
20
21	ROUND 4 February 13 2019
22	Craig Malzard	Dave Gibson	6	0
23	Noel McCudden	Mick Hayward	38	0
24	Chris Finch	Ken Selway	2	0
25	Clark Joyce	Chris Finch	94	0
26	Alan Taylor	Peter Hollins	16	0
27
28	ROUND 5 February 20 2019
29	Mick Hayward	Clark Joyce	0	18
30	Ken Selway	Craig Malzard	210	0
31	Craig Malzard	Dave Gibson	36	0
32	Peter Hollins	Manfred Sonntag	28	0
33	Dave Gibson	Noel McCudden
34
Sheet1

I did try you'r code Peter; but something is not correct in this! You can even see it in you'r own example. There are missing names in the list, which already start on the 2. section of the name list (Column A - Clark Joyce - Mick Hayward). And so it continue the whole list. A lot of names are missing!

 

[Peter_SSs]

You can even see it in you'r own example. There are missing names in the list

Sorry, I am not following. I assume you are referring to posts 12 &/or 13? In my sample data in columns A:D in post 12 I have 23 game results. In column J the total games played is 23.

Are you saying that there are names in columns A:B that do not appear in columns H:I? If so can you specify 1 or 2 as they all appear to me to be there?
If that is not what you meant can you explain a different way & give specific example(s)?

 

[ebea]

Sorry, I am not following. I assume you are referring to posts 12 &/or 13? In my sample data in columns A:D in post 12 I have 23 game results. In column J the total games played is 23.

Are you saying that there are names in columns A:B that do not appear in columns H:I? If so can you specify 1 or 2 as they all appear to me to be there?
If that is not what you meant can you explain a different way & give specific example(s)?

If you, by example, takes the list from #4, and put into Excel, and run you'r code, then you will see, that the name list the code generate in Column H:I do missing names. It start already in the second section in Column A (Row 9 in you'r examples), and the longer the list becomes, the more names are missing.
The number of times each other met, are correct, but not the name list.

You can actually also see it in you'r code example in #12.
Another"strange" thing is, that the name change position in the generated list, which in case of playing two different places, can have a meaning!

 

[Peter_SSs]

Perhaps we are expecting different things.

If you, by example, takes the list from #4, and put into Excel, and run you'r code, then you will see, that the name list the code generate in Column H:I do missing names.

I don't see that at all. That is, unless you mean, say Ian Hollins who is listed in column F of post 4 but not in the results of my code. That is because Ian Hollins has not played any games - does not appear in columns A:B either.

It start already in the second section in Column A (Row 9 in you'r examples)

Again I do not understand what you are saying. Column A row 9 in my examples is Clark Joyce. In columns A:B in post 12 Clark Joyce appears a total of 6 times playing against 3 different people: Mick Hayward, Ken Selway and Chris Finch.
Clark Joyce is shown in the results in that post in rows 2, 7 and 12 in columns H:I where his 6 results against those 3 opponents are summarised by number played, number won & % won. So I still fail to see anything missing.

Bender1964 2020-08-05 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ROUND 1 February 6 2019							P1	P2	Played	P1 wins	P1 %	P2 wins	P2 %
2	Mick Hayward	Clark Joyce	0	13				Clark Joyce	Mick Hayward	4	2	50.00%	2	50.00%
3	Ken Selway	Craig Malzard	2	0				Craig Malzard	Ken Selway	3	1	33.33%	2	66.67%
4	Craig Malzard	Dave Gibson	87	0				Craig Malzard	Dave Gibson	4	3	75.00%	1	25.00%
5	Peter Hollins	Manfred Sonntag	0	28				Manfred Sonntag	Peter Hollins	3	1	33.33%	2	66.67%
6	Dave Gibson	Noel McCudden	59	0				Dave Gibson	Noel McCudden	2	1	50.00%	1	50.00%
7								Clark Joyce	Ken Selway	1	1	100.00%	0	0.00%
8	ROUND 2 February 6 2019							Noel McCudden	Peter Hollins	1	0	0.00%	1	100.00%
9	Clark Joyce	Mick Hayward	0	16				Alan Taylor	Manfred Sonntag	1	0	0.00%	1	100.00%
10	Clark Joyce	Ken Selway	41	0				Mick Hayward	Noel McCudden	1	0	0.00%	1	100.00%
11	Peter Hollins	Noel McCudden	133	0				Chris Finch	Ken Selway	1	1	100.00%	0	0.00%
12	Manfred Sonntag	Alan Taylor	34	0				Chris Finch	Clark Joyce	1	0	0.00%	1	100.00%
13								Alan Taylor	Peter Hollins	1	1	100.00%	0	0.00%
14	ROUND 3 February 13 2019
15	Mick Hayward	Clark Joyce	40	0
16	Ken Selway	Craig Malzard	0	129
17	Craig Malzard	Dave Gibson	0	70
18	Peter Hollins	Manfred Sonntag	27	0
19	Dave Gibson	Noel McCudden	0	13
20
21	ROUND 4 February 13 2019
22	Craig Malzard	Dave Gibson	6	0
23	Noel McCudden	Mick Hayward	38	0
24	Chris Finch	Ken Selway	2	0
25	Clark Joyce	Chris Finch	94	0
26	Alan Taylor	Peter Hollins	16	0
27
28	ROUND 5 February 20 2019
29	Mick Hayward	Clark Joyce	0	18
30	Ken Selway	Craig Malzard	210	0
31	Craig Malzard	Dave Gibson	36	0
32	Peter Hollins	Manfred Sonntag	28	0
33	Dave Gibson	Noel McCudden
Sheet1

Cell Formulas
Range	Formula
J2:J13	J2	=K2+M2
L2:L13	L2	=K2/J2
N2:N13	N2	=M2/J2