Find names & results in a list and tally them up


New Member
Aug 3, 2020
Office Version
  1. 2007
  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


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


Board Regular
Jul 12, 2008
Office Version
  1. 2013
  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
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
21ROUND 4 February 13 2019
22Craig MalzardDave Gibson60
23Noel McCuddenMick Hayward380
24Chris FinchKen Selway20
25Clark JoyceChris Finch940
26Alan TaylorPeter Hollins160
28ROUND 5 February 20 2019
29Mick HaywardClark Joyce018
30Ken SelwayCraig Malzard2100
31Craig MalzardDave Gibson360
32Peter HollinsManfred Sonntag280
33Dave GibsonNoel McCudden
Cell Formulas
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.

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying


Board Regular
Jul 12, 2008
Office Version
  1. 2013
  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!


MrExcel MVP, Moderator
May 28, 2005
Office Version
  1. 365
  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)
          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)
          d1(sPlayers) = ";;" & Split(d1(sPlayers), ";")(2) & ";;" & Split(d1(sPlayers), ";")(4) + 1
        End If
        d1(sPlayers) = IIf(winner = 1, ";;1;;0", ";;0;;1")
      End If
    End If
  Next i
  Application.ScreenUpdating = False
  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)
    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
  Application.ScreenUpdating = True
End Sub

My sample data and results of code:

Bender1964 2020-08-05 1.xlsm
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%
37Mick HaywardvClark Joyce42250.00%
38Noel McCudden1010.00%
40Noel McCuddenvDave Gibson21150.00%
41Mick Hayward110100.00%
42Peter Hollins1010.00%
44Peter HollinsvAlan Taylor1010.00%
45Manfred Sonntag32166.67%
46Noel McCudden110100.00%
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back