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

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
You can use a Sumproduct formula, to count two players meeting. By example: =SUMPRODUCT(($A$2:$A$30=G2)*($B$2:$B$30=H2)+($A$2:$A$30=H2)*($B$2:$B$30=G2))
Where I have listed Players name in Column G and H, and do the count there!
But it would be better, if you use the function XL2BB to upload the data set.
 

Bender1964

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
Hi Ebea, thanks for the swift reply. I'll give it a go and let you know how I went.

Time for bed over here. Thanks Peter
 

Bender1964

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

Ebea, I tried your formula and it worked perfectly for counting how many times 2 players had played each other. I downloaded xl2bb and have attached a better sample of my data.

So, an example of what I'm trying to achieve is as follows: If I calculate that player A has played player B 7 times. I want to know how many times player A has beaten player B, which would be if his score was greater than 0. Player names are in column A & B, while player A's score is in column C and player B's score is in column D.

So my end result table would be a list of all possible combinations of games played (A vs B, A vs C, A vs D, B vs C and so on), with a total for each of those games, and number of wins each.

Thanks in advance Peter

Sample data for mr excel.xls
ABCDEF
1ROUND 1 February 6 2019Player name
2Mick HaywardClark Joyce013Mick Hayward
3Ken SelwayCraig Malzard20Clark Joyce
4Craig MalzardDave Gibson870Ken Selway
5Peter HollinsManfred Sonntag028Ian Hollins
6Dave GibsonNoel McCudden590Wayne Gatley
7Neil Ulmer
8Craig Malzard
9Shaun Magnus
10ROUND 2 February 6 2019Chris Finch
11Clark JoyceMick Hayward016Dave Gibson
12Clark JoyceKen Selway410Noel McCudden
13Peter HollinsNoel McCudden1330Peter Hollins
14Manfred SonntagAlan Taylor340Manfred Sonntag
15Jon Murphy
16Alan Taylor
17Ray Grimson
18
19ROUND 3 February 13 2019
20Ken SelwayNoel McCudden400
21Alan TaylorClark Joyce0129
22Peter HollinsDave Gibson070
23Mick HaywardChris Finch270
24Chris FinchCraig Malzard013
25
26
27
28ROUND 4 February 13 2019
29Craig MalzardDave Gibson60
30Noel McCuddenMick Hayward380
31Chris FinchKen Selway20
32Clark JoyceChris Finch940
33Alan TaylorPeter Hollins160
34
35
36
37
38ROUND 5 February 20 2019
39Manfred SonntagDave Gibson018
40Noel McCuddenChris Finch2100
41Mick HaywardKen Selway360
42Clark JoyceManfred Sonntag280
43
44
45
46
47ROUND 6 February 20 2019
48
49Mick HaywardNoel McCudden240
50Clark JoyceDave Gibson016
51Ken SelwayChris Finch640
52Manfred SonntagMick Hayward07
53Ken SelwayClark Joyce016
54Noel McCuddenDave Gibson100
55
56
57ROUND 7 February 27 2019
58Clark JoyceNoel McCudden280
59Chris FinchManfred Sonntag260
60Craig MalzardMick Hayward260
61Ken SelwayPeter Hollins360
62Alan TaylorDave Gibson040
63Alan TaylorCraig Malzard20
64
65
66ROUND 8 February 27 2019
67Peter HollinsMick Hayward083
68Dave GibsonClark Joyce100
69Ken SelwayManfred Sonntag180
70Chris FinchAlan Taylor20
71Craig MalzardNoel McCudden120
72Peter HollinsCraig Malzard240
73
74
75ROUND 9 March 6 2019
76
77Clark JoyceChris Finch160
78Alan TaylorCraig Malzard220
79Manfred SonntagDave Gibson20
80Ken SelwayPeter Hollins040
81Noel McCuddenManfred Sonntag010
82Alan TaylorMick Hayward90
83
84ROUND 10 March 6 2019
85
86Manfred SonntagClark Joyce360
87Mick HaywardDave Gibson098
88Chris FinchPeter Hollins024
89Manfred SonntagCraig Malzard200
90Ken SelwayNoel McCudden066
91Peter HollinsClark Joyce240
92
93ROUND 11 March 13 2019
94
95
96Noel McCuddenChris Finch140
97
98Chris FinchMick Hayward041
99Manfred SonntagCraig Malzard20
100
101
102
103Peter HollinsMick Hayward016
104Mick HaywardDave Gibson20
105
106ROUND 12 March 13 2019
107
108Craig MalzardMick Hayward560
109Peter HollinsCraig Malzard02
110Chris FinchDave Gibson0121
111Manfred SonntagPeter Hollins066
112Noel McCuddenManfred Sonntag041
113
114ROUND 13 March 20 2019
115
116Chris FinchManfred Sonntag290
117Mick HaywardClark Joyce04
118Alan TaylorNoel McCudden012
119Noel McCuddenPeter Hollins08
120Dave GibsonChris Finch340
121Craig MalzardClark Joyce60
122
123ROUND 14 March 20 2019
124
125Clark JoyceAlan Taylor240
126Manfred SonntagNoel McCudden02
127Peter HollinsDave Gibson014
128Manfred SonntagMick Hayward270
129Craig MalzardChris Finch190
130Alan TaylorDave Gibson085
131
132
133ROUND 15 April 3 2019
134
135Dave GibsonCraig Malzard011
136Mick HaywardNoel McCudden660
137Ken SelwayManfred Sonntag02
138Manfred SonntagClark Joyce02
139Peter HollinsAlan Taylor160
140Alan TaylorKen Selway023
141
142ROUND 16 April 3 2019
143
144Noel McCuddenCraig Malzard036
145Ken SelwayMick Hayward020
146Alan TaylorManfred Sonntag010
147Clark JoycePeter Hollins018
148Craig MalzardClark Joyce05
149Ken SelwayDave Gibson170
150
151ROUND 17 April 10 2019
152
153Chris FinchAlan Taylor20
154Alan TaylorNoel McCudden016
155Chris FinchMick Hayward08
156Peter HollinsCraig Malzard06
157Mick HaywardDave Gibson04
158Craig MalzardChris Finch032
159
160ROUND 18 April 10 2019
161
162Noel McCuddenPeter Hollins02
163Dave GibsonNoel McCudden80
164Mick HaywardCraig Malzard360
165Chris FinchPeter Hollins023
166Dave GibsonAlan Taylor190
167
168
169ROUND 19 April 18 2019
170
171Jon MurphyDave Gibson0110
172Manfred SonntagKen Selway049
173Alan TaylorChris Finch140
174Dave GibsonKen Selway02
175Noel McCuddenCraig Malzard04
176
177ROUND 20 April 18 2019
178Craig MalzardJon Murphy120
179Chris FinchPeter Hollins08
180Alan TaylorNoel McCudden04
181Dave GibsonManfred Sonntag02
182Peter HollinsKen Selway0119
183
184
185ROUND 21 April 24 2019
186
187Jon MurphyAlan Taylor02
188Dave GibsonKen Selway20
189Craig MalzardJon Murphy500
190Ken SelwayCraig Malzard160
191Peter HollinsJon Murphy960
192Alan TaylorCraig Malzard06
193
194ROUND 22 April 24 2019
195
196Jon MurphyDave Gibson220
197Alan TaylorKen Selway40
198Alan TaylorPeter Hollins600
199Peter HollinsDave Gibson160
200Ken SelwayJon Murphy04
201
202ROUND 23 May 1 2019
203Noel McCuddenClark Joyce032
204Ken SelwayCraig Malzard016
205Chris FinchKen Selway03
206Dave GibsonNoel McCudden540
207Clark JoycePeter Hollins400
208Alan TaylorNoel McCudden160
209
210ROUND 24 May 1 2019
211
212Peter HollinsNoel McCudden60
213Chris FinchAlan Taylor630
214Dave GibsonChris Finch1060
215Clark JoyceKen Selway520
216Craig MalzardDave Gibson036
217
218
219ROUND 25 May 8 2019
220
221Noel McCuddenClark Joyce095
222Chris FinchCraig Malzard30
223Alan TaylorClark Joyce40
224Peter HollinsMick Hayward019
225Chris FinchNoel McCudden05
226
227
228
229ROUND 26 May 8 2019
230
231Craig MalzardMick Hayward980
232Mick HaywardClark Joyce010
233Alan TaylorPeter Hollins350
234Clark JoyceChris Finch50
235Noel McCuddenMick Hayward02
236
237
238ROUND 27 May 22 2019
239
240Mick HaywardAlan Taylor60
241Noel McCuddenChris Finch40
242Clark JoyceCraig Malzard013
243Ken SelwayDave Gibson035
244Chris FinchPeter Hollins560
245Mick HaywardKen Selway20
246
247ROUND 28 May 22 2019
248
249Alan TaylorKen Selway02
250Peter HollinsCraig Malzard370
251Clark JoyceDave Gibson20
252Chris FinchMick Hayward80
253Ken SelwayNoel McCudden0134
254
255
256ROUND 29 June 12 2019
257
258Clark JoyceNoel McCudden400
259Chris FinchDave Gibson1320
260Craig MalzardMick Hayward04
261Clark JoycePeter Hollins02
262Alan TaylorDave Gibson011
263
264
265ROUND 30 June 12 2019
266
267Peter HollinsMick Hayward02
268Dave GibsonClark Joyce0109
269Chris FinchAlan Taylor60
270Craig MalzardNoel McCudden1160
271
272
273ROUND 31 June 19 2019
274
275Alan TaylorClark Joyce042
276Noel McCuddenDave Gibson016
277Clark JoyceChris Finch80
278Ken SelwayPeter Hollins1410
279Alan TaylorNoel McCudden02
280
281
282ROUND 32 June 19 2019
283
284Ken SelwayClark Joyce02
285Peter HollinsChris Finch320
286Dave GibsonKen Selway08
287Ken SelwayNoel McCudden02
288
289
290ROUND 33 June 26 2019
291
292Noel McCuddenAlan Taylor140
293Chris FinchKen Selway027
294Dave GibsonChris Finch200
295Alan TaylorKen Selway150
296Craig MalzardAlan Taylor02
297Peter HollinsDave Gibson1650
298
299ROUND 34 June 26 2019
300
301Craig MalzardKen Selway016
302Chris FinchCraig Malzard034
303Noel McCuddenPeter Hollins09
304Dave GibsonNoel McCudden160
305Alan TaylorPeter Hollins07
306
307
308ROUND 34 July 3 2019
309
310Manfred SonntagCraig Malzard014
311Chris FinchManfred Sonntag150
312Craig MalzardDave Gibson470
313Noel McCuddenClark Joyce015
314Alan TaylorManfred Sonntag20
315
316ROUND 35 July 3 2019
317
318Peter HollinsClark Joyce60
319Noel McCuddenChris Finch041
320Chris FinchDave Gibson80
321Alan TaylorClark Joyce200
322Manfred SonntagPeter Hollins020
Sheet1
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I have made an example, with statistic, and results on a sheet. There you will find formulas, and a way to do it. If it's so, you want to set it up, I don't know. But I upload the file (in Link below), and you can play around with that. I think you can see in the file, how it all works. Else just ask!

 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
I don't know if you downloaded the File, but I have updated the files on Dropbox, with small changes. So try download the latest File!
 

Bender1964

New Member
Joined
Aug 3, 2020
Messages
5
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi Ebea, thanks again for your quick response. I really appreciate the time you have put into this. An example of what I need is as follows, using my data as the source:

Your spreadsheet shows Mick Hayward and Clark Joyce meeting 4 times. I would like to know the overall results of those 4 games. That is, how many has Mick won, and therefore how many Clark has won. The aim of this is to (a) have a career summary of all games won or lost (which your spreadsheet does really well) and (b) how each player has fared in games against each player.

I hope this makes sense.

Thanks Peter
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
I will take a look at it, and send a new file to Dropbox. But it's a bit difficult to know, how you want to set up the structure in the Sheets you actualla use, vs the sheet design I have made. So if you have an idea by yourself, then send a note about it (or maybe a sheet). Should all this statistic be on a new sheet, or, and so on!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
@ebea
Please do do answer questions simply by uploading a file and providing a link. Refer to #4 (especially the last two paragraphs) of the Forum Rules

Providing a file & link is acceptable as additional support for an answer but it should not be the answer, which should appear in the thread as per rule 4.


@Bender1964
Are you specifically looking for a formula solution or macro solution? If you are looking for a formula solution but it doesn't seem reasonably feasible, would a macro solution be acceptable.

While I think I know what you are after, I am not certain. It would be helpful if you created a much smaller sample than post 4 and included the expected results (& post again with XL2BB) so that we can confirm exactly what you want and how you want it to appear. I think that is what ebea is also asking in post 8.
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
@ebea
Please do do answer questions simply by uploading a file and providing a link. Refer to #4 (especially the last two paragraphs) of the Forum Rules

Providing a file & link is acceptable as additional support for an answer but it should not be the answer, which should appear in the thread as per rule 4.


@Bender1964
Are you specifically looking for a formula solution or macro solution? If you are looking for a formula solution but it doesn't seem reasonably feasible, would a macro solution be acceptable.

While I think I know what you are after, I am not certain. It would be helpful if you created a much smaller sample than post 4 and included the expected results (& post again with XL2BB) so that we can confirm exactly what you want and how you want it to appear. I think that is what ebea is also asking in post 8.
I'm aware of the forum rules, but the Sheet was in a size, that I found it not to fit into the windows here in Forum. But I will remember, that ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,087
Messages
5,576,035
Members
412,694
Latest member
Deaf1Too
Top