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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,493
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,792
Messages
5,574,322
Members
412,587
Latest member
Krucial155
Top