Find names & results in a list and tally them up

[Bender1964]

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

 

[ebea]

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]

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]

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
	A	B	C	D	E	F
1	ROUND 1 February 6 2019					Player name
2	Mick Hayward	Clark Joyce	0	13		Mick Hayward
3	Ken Selway	Craig Malzard	2	0		Clark Joyce
4	Craig Malzard	Dave Gibson	87	0		Ken Selway
5	Peter Hollins	Manfred Sonntag	0	28		Ian Hollins
6	Dave Gibson	Noel McCudden	59	0		Wayne Gatley
7						Neil Ulmer
8						Craig Malzard
9						Shaun Magnus
10	ROUND 2 February 6 2019					Chris Finch
11	Clark Joyce	Mick Hayward	0	16		Dave Gibson
12	Clark Joyce	Ken Selway	41	0		Noel McCudden
13	Peter Hollins	Noel McCudden	133	0		Peter Hollins
14	Manfred Sonntag	Alan Taylor	34	0		Manfred Sonntag
15						Jon Murphy
16						Alan Taylor
17						Ray Grimson
18
19	ROUND 3 February 13 2019
20	Ken Selway	Noel McCudden	40	0
21	Alan Taylor	Clark Joyce	0	129
22	Peter Hollins	Dave Gibson	0	70
23	Mick Hayward	Chris Finch	27	0
24	Chris Finch	Craig Malzard	0	13
25
26
27
28	ROUND 4 February 13 2019
29	Craig Malzard	Dave Gibson	6	0
30	Noel McCudden	Mick Hayward	38	0
31	Chris Finch	Ken Selway	2	0
32	Clark Joyce	Chris Finch	94	0
33	Alan Taylor	Peter Hollins	16	0
34
35
36
37
38	ROUND 5 February 20 2019
39	Manfred Sonntag	Dave Gibson	0	18
40	Noel McCudden	Chris Finch	210	0
41	Mick Hayward	Ken Selway	36	0
42	Clark Joyce	Manfred Sonntag	28	0
43
44
45
46
47	ROUND 6 February 20 2019
48
49	Mick Hayward	Noel McCudden	24	0
50	Clark Joyce	Dave Gibson	0	16
51	Ken Selway	Chris Finch	64	0
52	Manfred Sonntag	Mick Hayward	0	7
53	Ken Selway	Clark Joyce	0	16
54	Noel McCudden	Dave Gibson	10	0
55
56
57	ROUND 7 February 27 2019
58	Clark Joyce	Noel McCudden	28	0
59	Chris Finch	Manfred Sonntag	26	0
60	Craig Malzard	Mick Hayward	26	0
61	Ken Selway	Peter Hollins	36	0
62	Alan Taylor	Dave Gibson	0	40
63	Alan Taylor	Craig Malzard	2	0
64
65
66	ROUND 8 February 27 2019
67	Peter Hollins	Mick Hayward	0	83
68	Dave Gibson	Clark Joyce	10	0
69	Ken Selway	Manfred Sonntag	18	0
70	Chris Finch	Alan Taylor	2	0
71	Craig Malzard	Noel McCudden	12	0
72	Peter Hollins	Craig Malzard	24	0
73
74
75	ROUND 9 March 6 2019
76
77	Clark Joyce	Chris Finch	16	0
78	Alan Taylor	Craig Malzard	22	0
79	Manfred Sonntag	Dave Gibson	2	0
80	Ken Selway	Peter Hollins	0	40
81	Noel McCudden	Manfred Sonntag	0	10
82	Alan Taylor	Mick Hayward	9	0
83
84	ROUND 10 March 6 2019
85
86	Manfred Sonntag	Clark Joyce	36	0
87	Mick Hayward	Dave Gibson	0	98
88	Chris Finch	Peter Hollins	0	24
89	Manfred Sonntag	Craig Malzard	20	0
90	Ken Selway	Noel McCudden	0	66
91	Peter Hollins	Clark Joyce	24	0
92
93	ROUND 11 March 13 2019
94
95
96	Noel McCudden	Chris Finch	14	0
97
98	Chris Finch	Mick Hayward	0	41
99	Manfred Sonntag	Craig Malzard	2	0
100
101
102
103	Peter Hollins	Mick Hayward	0	16
104	Mick Hayward	Dave Gibson	2	0
105
106	ROUND 12 March 13 2019
107
108	Craig Malzard	Mick Hayward	56	0
109	Peter Hollins	Craig Malzard	0	2
110	Chris Finch	Dave Gibson	0	121
111	Manfred Sonntag	Peter Hollins	0	66
112	Noel McCudden	Manfred Sonntag	0	41
113
114	ROUND 13 March 20 2019
115
116	Chris Finch	Manfred Sonntag	29	0
117	Mick Hayward	Clark Joyce	0	4
118	Alan Taylor	Noel McCudden	0	12
119	Noel McCudden	Peter Hollins	0	8
120	Dave Gibson	Chris Finch	34	0
121	Craig Malzard	Clark Joyce	6	0
122
123	ROUND 14 March 20 2019
124
125	Clark Joyce	Alan Taylor	24	0
126	Manfred Sonntag	Noel McCudden	0	2
127	Peter Hollins	Dave Gibson	0	14
128	Manfred Sonntag	Mick Hayward	27	0
129	Craig Malzard	Chris Finch	19	0
130	Alan Taylor	Dave Gibson	0	85
131
132
133	ROUND 15 April 3 2019
134
135	Dave Gibson	Craig Malzard	0	11
136	Mick Hayward	Noel McCudden	66	0
137	Ken Selway	Manfred Sonntag	0	2
138	Manfred Sonntag	Clark Joyce	0	2
139	Peter Hollins	Alan Taylor	16	0
140	Alan Taylor	Ken Selway	0	23
141
142	ROUND 16 April 3 2019
143
144	Noel McCudden	Craig Malzard	0	36
145	Ken Selway	Mick Hayward	0	20
146	Alan Taylor	Manfred Sonntag	0	10
147	Clark Joyce	Peter Hollins	0	18
148	Craig Malzard	Clark Joyce	0	5
149	Ken Selway	Dave Gibson	17	0
150
151	ROUND 17 April 10 2019
152
153	Chris Finch	Alan Taylor	2	0
154	Alan Taylor	Noel McCudden	0	16
155	Chris Finch	Mick Hayward	0	8
156	Peter Hollins	Craig Malzard	0	6
157	Mick Hayward	Dave Gibson	0	4
158	Craig Malzard	Chris Finch	0	32
159
160	ROUND 18 April 10 2019
161
162	Noel McCudden	Peter Hollins	0	2
163	Dave Gibson	Noel McCudden	8	0
164	Mick Hayward	Craig Malzard	36	0
165	Chris Finch	Peter Hollins	0	23
166	Dave Gibson	Alan Taylor	19	0
167
168
169	ROUND 19 April 18 2019
170
171	Jon Murphy	Dave Gibson	0	110
172	Manfred Sonntag	Ken Selway	0	49
173	Alan Taylor	Chris Finch	14	0
174	Dave Gibson	Ken Selway	0	2
175	Noel McCudden	Craig Malzard	0	4
176
177	ROUND 20 April 18 2019
178	Craig Malzard	Jon Murphy	12	0
179	Chris Finch	Peter Hollins	0	8
180	Alan Taylor	Noel McCudden	0	4
181	Dave Gibson	Manfred Sonntag	0	2
182	Peter Hollins	Ken Selway	0	119
183
184
185	ROUND 21 April 24 2019
186
187	Jon Murphy	Alan Taylor	0	2
188	Dave Gibson	Ken Selway	2	0
189	Craig Malzard	Jon Murphy	50	0
190	Ken Selway	Craig Malzard	16	0
191	Peter Hollins	Jon Murphy	96	0
192	Alan Taylor	Craig Malzard	0	6
193
194	ROUND 22 April 24 2019
195
196	Jon Murphy	Dave Gibson	22	0
197	Alan Taylor	Ken Selway	4	0
198	Alan Taylor	Peter Hollins	60	0
199	Peter Hollins	Dave Gibson	16	0
200	Ken Selway	Jon Murphy	0	4
201
202	ROUND 23 May 1 2019
203	Noel McCudden	Clark Joyce	0	32
204	Ken Selway	Craig Malzard	0	16
205	Chris Finch	Ken Selway	0	3
206	Dave Gibson	Noel McCudden	54	0
207	Clark Joyce	Peter Hollins	40	0
208	Alan Taylor	Noel McCudden	16	0
209
210	ROUND 24 May 1 2019
211
212	Peter Hollins	Noel McCudden	6	0
213	Chris Finch	Alan Taylor	63	0
214	Dave Gibson	Chris Finch	106	0
215	Clark Joyce	Ken Selway	52	0
216	Craig Malzard	Dave Gibson	0	36
217
218
219	ROUND 25 May 8 2019
220
221	Noel McCudden	Clark Joyce	0	95
222	Chris Finch	Craig Malzard	3	0
223	Alan Taylor	Clark Joyce	4	0
224	Peter Hollins	Mick Hayward	0	19
225	Chris Finch	Noel McCudden	0	5
226
227
228
229	ROUND 26 May 8 2019
230
231	Craig Malzard	Mick Hayward	98	0
232	Mick Hayward	Clark Joyce	0	10
233	Alan Taylor	Peter Hollins	35	0
234	Clark Joyce	Chris Finch	5	0
235	Noel McCudden	Mick Hayward	0	2
236
237
238	ROUND 27 May 22 2019
239
240	Mick Hayward	Alan Taylor	6	0
241	Noel McCudden	Chris Finch	4	0
242	Clark Joyce	Craig Malzard	0	13
243	Ken Selway	Dave Gibson	0	35
244	Chris Finch	Peter Hollins	56	0
245	Mick Hayward	Ken Selway	2	0
246
247	ROUND 28 May 22 2019
248
249	Alan Taylor	Ken Selway	0	2
250	Peter Hollins	Craig Malzard	37	0
251	Clark Joyce	Dave Gibson	2	0
252	Chris Finch	Mick Hayward	8	0
253	Ken Selway	Noel McCudden	0	134
254
255
256	ROUND 29 June 12 2019
257
258	Clark Joyce	Noel McCudden	40	0
259	Chris Finch	Dave Gibson	132	0
260	Craig Malzard	Mick Hayward	0	4
261	Clark Joyce	Peter Hollins	0	2
262	Alan Taylor	Dave Gibson	0	11
263
264
265	ROUND 30 June 12 2019
266
267	Peter Hollins	Mick Hayward	0	2
268	Dave Gibson	Clark Joyce	0	109
269	Chris Finch	Alan Taylor	6	0
270	Craig Malzard	Noel McCudden	116	0
271
272
273	ROUND 31 June 19 2019
274
275	Alan Taylor	Clark Joyce	0	42
276	Noel McCudden	Dave Gibson	0	16
277	Clark Joyce	Chris Finch	8	0
278	Ken Selway	Peter Hollins	141	0
279	Alan Taylor	Noel McCudden	0	2
280
281
282	ROUND 32 June 19 2019
283
284	Ken Selway	Clark Joyce	0	2
285	Peter Hollins	Chris Finch	32	0
286	Dave Gibson	Ken Selway	0	8
287	Ken Selway	Noel McCudden	0	2
288
289
290	ROUND 33 June 26 2019
291
292	Noel McCudden	Alan Taylor	14	0
293	Chris Finch	Ken Selway	0	27
294	Dave Gibson	Chris Finch	20	0
295	Alan Taylor	Ken Selway	15	0
296	Craig Malzard	Alan Taylor	0	2
297	Peter Hollins	Dave Gibson	165	0
298
299	ROUND 34 June 26 2019
300
301	Craig Malzard	Ken Selway	0	16
302	Chris Finch	Craig Malzard	0	34
303	Noel McCudden	Peter Hollins	0	9
304	Dave Gibson	Noel McCudden	16	0
305	Alan Taylor	Peter Hollins	0	7
306
307
308	ROUND 34 July 3 2019
309
310	Manfred Sonntag	Craig Malzard	0	14
311	Chris Finch	Manfred Sonntag	15	0
312	Craig Malzard	Dave Gibson	47	0
313	Noel McCudden	Clark Joyce	0	15
314	Alan Taylor	Manfred Sonntag	2	0
315
316	ROUND 35 July 3 2019
317
318	Peter Hollins	Clark Joyce	6	0
319	Noel McCudden	Chris Finch	0	41
320	Chris Finch	Dave Gibson	8	0
321	Alan Taylor	Clark Joyce	20	0
322	Manfred Sonntag	Peter Hollins	0	20
Sheet1

 

[ebea]

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!

Dropbox

www.dropbox.com

 

[ebea]

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]

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]

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]

@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]

@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