Find names & results in a list and tally them up

Bender1964

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
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
 

Attachments

  • Table image for mr excel Aug 2020.jpg
    Table image for mr excel Aug 2020.jpg
    235 KB · Views: 27

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1ROUND 1 February 6 2019P1P2PlayedP1 winsP1 %P2 winsP2 %
2Mick HaywardClark Joyce013Clark JoyceMick Hayward4250.00%250.00%
3Ken SelwayCraig Malzard20Craig MalzardKen Selway3133.33%266.67%
4Craig MalzardDave Gibson870Craig MalzardDave Gibson4375.00%125.00%
5Peter HollinsManfred Sonntag028Manfred SonntagPeter Hollins3133.33%266.67%
6Dave GibsonNoel McCudden590Dave GibsonNoel McCudden2150.00%150.00%
7Clark JoyceKen Selway11100.00%00.00%
8ROUND 2 February 6 2019Noel McCuddenPeter Hollins100.00%1100.00%
9Clark JoyceMick Hayward016Alan TaylorManfred Sonntag100.00%1100.00%
10Clark JoyceKen Selway410Mick HaywardNoel McCudden100.00%1100.00%
11Peter HollinsNoel McCudden1330Chris FinchKen Selway11100.00%00.00%
12Manfred SonntagAlan Taylor340Chris FinchClark Joyce100.00%1100.00%
13Alan TaylorPeter Hollins11100.00%00.00%
14ROUND 3 February 13 2019
15Mick HaywardClark Joyce400
16Ken SelwayCraig Malzard0129
17Craig MalzardDave Gibson070
18Peter HollinsManfred Sonntag270
19Dave GibsonNoel McCudden013
20
21ROUND 4 February 13 2019
22Craig MalzardDave Gibson60
23Noel McCuddenMick Hayward380
24Chris FinchKen Selway20
25Clark JoyceChris Finch940
26Alan TaylorPeter Hollins160
27
28ROUND 5 February 20 2019
29Mick HaywardClark Joyce018
30Ken SelwayCraig Malzard2100
31Craig MalzardDave Gibson360
32Peter HollinsManfred Sonntag280
33Dave GibsonNoel McCudden
Sheet1
Cell Formulas
RangeFormula
J2:J13J2=K2+M2
L2:L13L2=K2/J2
N2:N13N2=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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,499
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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
ABCDHIJKLMNO
1ROUND 1 February 6 2019PlayerPlayedWonLost% WonRank
2Mick HaywardClark Joyce013Alan Taylor21150.00%4
3Ken SelwayCraig Malzard20Chris Finch21150.00%4
4Craig MalzardDave Gibson870Clark Joyce64266.67%1
5Peter HollinsManfred Sonntag028Craig Malzard74357.14%3
6Dave GibsonNoel McCudden590Dave Gibson62433.33%10
7Ken Selway52340.00%8
8ROUND 2 February 6 2019Manfred Sonntag42250.00%4
9Clark JoyceMick Hayward016Mick Hayward52340.00%8
10Clark JoyceKen Selway410Noel McCudden42250.00%4
11Peter HollinsNoel McCudden1330Peter Hollins53260.00%2
12Manfred SonntagAlan Taylor340
13PlayerOpponentPlayedWonLost% Won
14ROUND 3 February 13 2019Alan TaylorvManfred Sonntag1010.00%
15Mick HaywardClark Joyce400Peter Hollins110100.00%
16Ken SelwayCraig Malzard0129
17Craig MalzardDave Gibson070Chris FinchvClark Joyce1010.00%
18Peter HollinsManfred Sonntag270Ken Selway110100.00%
19Dave GibsonNoel McCudden013
20Clark JoycevChris Finch110100.00%
21ROUND 4 February 13 2019Ken Selway110100.00%
22Craig MalzardDave Gibson60Mick Hayward42250.00%
23Noel McCuddenMick Hayward380
24Chris FinchKen Selway20Craig MalzardvDave Gibson43175.00%
25Clark JoyceChris Finch940Ken Selway31233.33%
26Alan TaylorPeter Hollins160
27Dave GibsonvCraig Malzard41325.00%
28ROUND 5 February 20 2019Noel McCudden21150.00%
29Mick HaywardClark Joyce018
30Ken SelwayCraig Malzard2100Ken SelwayvChris Finch1010.00%
31Craig MalzardDave Gibson360Clark Joyce1010.00%
32Peter HollinsManfred Sonntag280Craig Malzard32166.67%
33Dave GibsonNoel McCudden
34Manfred SonntagvAlan Taylor110100.00%
35Peter Hollins31233.33%
36
37Mick HaywardvClark Joyce42250.00%
38Noel McCudden1010.00%
39
40Noel McCuddenvDave Gibson21150.00%
41Mick Hayward110100.00%
42Peter Hollins1010.00%
43
44Peter HollinsvAlan Taylor1010.00%
45Manfred Sonntag32166.67%
46Noel McCudden110100.00%
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,477
Members
412,595
Latest member
slim313
Top