# Find names & results in a list and tally them up

#### Bender1964

##### New Member
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
235 KB · Views: 35

#### ebea

##### Board Regular
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.

### 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

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Board Regular
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
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))
.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))
.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

Replies
0
Views
105
Replies
2
Views
100
Replies
1
Views
143
Replies
1
Views
165
Replies
8
Views
369

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

1,151,468
Messages
5,764,508
Members
425,219
Latest member
datdanigg

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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