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]

Perhaps we are expecting different things.

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.

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

Now I see, why we see it different. In you'r case, you so to say "stack" the same players, if they have met each other more times, in a single row, and present the results of these meeting, in same row.
As I did see the Players meeting, and has showed in my example, I have listed the players (result and meetings) in each row, representing if they was playing Home or Away (Player in Column A vs B). And that's what I did mean, when I say Players are missing in you'r results.
But what's right, I don't know. The OP has not reflected.

 

[Peter_SSs]

But what's right, I don't know. The OP has not reflected.

I agree. Hopefully the OP will let us know in due course.

 

[ebea]

I agree. Hopefully the OP will let us know in due course.

Hopefully. I just noticed i #1 image, that OP has listed an H and A, as it was games, Home and Away!

 

[Peter_SSs]

I just noticed i #1 image, that OP has listed an H and A, as it was games, Home and Away!

You are right but as there was no mention of that in the written request in post #1 or in the description or columns I:O in post #14 I didn't see a need to use that information. Based on the request in post #14, I have amended my code.

Sub ResultSummary_v2()
  Dim d1 As Object, d2 As Object
  Dim a As Variant, ky As Variant
  Dim sPlayers As String, tmp As String, P1 As String
  Dim i As Long, j As Long, winner As Long, nr As Long
  
  Set d1 = CreateObject("Scripting.Dictionary")
  d1.CompareMode = 1
  Set d2 = CreateObject("Scripting.Dictionary")
  d2.CompareMode = 1
  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)
      For j = 1 To 2
        If d2.exists(a(i, j)) Then
          d2(a(i, j)) = Split(d2(a(i, j)), ";")(0) + 1 & ";" & Split(d2(a(i, j)), ";")(1) + IIf(winner = j, 1, 0)
        Else
          d2(a(i, j)) = "1;" & IIf(winner = j, 1, 0)
        End If
      Next j
      sPlayers = a(i, 1) & ";" & a(i, 2)
      If d1.exists(sPlayers) Then
        If winner = 1 Then
          d1(sPlayers) = ";;" & Split(d1(sPlayers), ";")(2) + 1 & ";;" & Split(d1(sPlayers), ";")(4)
        Else
          d1(sPlayers) = ";;" & Split(d1(sPlayers), ";")(2) & ";;" & Split(d1(sPlayers), ";")(4) + 1
        End If
      Else
        d1(sPlayers) = IIf(winner = 1, ";;1;;0", ";;0;;1")
      End If
    End If
  Next i
  Application.ScreenUpdating = False
  Columns("I:P").Clear
  With Range("I2:J2").Resize(d2.Count)
    .Rows(0).Resize(, 6).Value = Array("Player", "Played", "Won", "Lost", "% Won", "Rank")
    .Rows(0).Resize(, 6).Font.Bold = True
    .Value = Application.Transpose(Array(d2.keys, d2.Items))
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
    .Columns(2).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .Offset(, 3).Resize(, 1).FormulaR1C1 = "=RC[-2]-RC[-1]"
    .Offset(, 4).Resize(, 1).FormulaR1C1 = "=RC[-2]/RC[-3]"
    .Offset(, 4).Resize(, 1).NumberFormat = "0.00%"
    .Offset(, 5).Resize(, 1).FormulaR1C1 = "=RANK(RC[-1],R" & .Row & "C[-1]:R" & .Row + .Rows.Count - 1 & "C[-1])"
    a = .Columns(1).Value
  End With
  nr = UBound(a) + 4
  Range("I" & nr - 1).Resize(, 7).Value = Array("Player", "", "Opponent", "Played", "Won", "Lost", "% Won")
  Range("I" & nr - 1).Resize(, 7).Font.Bold = True
  For i = 1 To UBound(a)
    d2.RemoveAll
    P1 = a(i, 1)
    For Each ky In d1.keys
      Select Case True
        Case Split(ky, ";")(0) = P1
          d2(Split(ky, ";")(1)) = Mid(d1(ky), 2)
        Case Split(ky, ";")(1) = P1
          d2(Split(ky, ";")(0)) = ";" & Split(d1(ky), ";")(4) & ";" & Split(d1(ky), ";")(2)
      End Select
    Next ky
    With Range("K" & nr & ":L" & nr).Resize(d2.Count)
      .Cells(1, -1).Resize(, 2).Value = Array(P1, "v")
      .Value = Application.Transpose(Array(d2.keys, d2.Items))
      .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
      .Columns(2).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Semicolon:=True, Comma:=False, Space:=False, Other:=False
      .Offset(, 1).Resize(, 1).FormulaR1C1 = "=RC[1]+RC[2]"
      With .Offset(, 4).Resize(, 1)
        .NumberFormat = "0.00%"
        .FormulaR1C1 = "=RC[-2]/RC[-3]"
      End With
      nr = nr + d2.Count + 1
    End With
  Next i
  Columns("I:O").AutoFit
  Application.ScreenUpdating = True
End Sub

My sample data and results of code:

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