Count number of times players play in same game

G

GoDawgs

New Member
Joined
Oct 19, 2016
Messages
2
Random one here, I've got a round robin for tennis being set up and I want to count the number of times players are in the same match together (really just making sure I don't have the same people playing again and again). Table setup is below, but this can change, totally my setup for right now.

I've got a "Matchups" tab that has player numbers running down column A and across row 1...I want to set up a formula where, for example, player 1 and 5 meet they'll have a 1 in the cell because they're playing on court 1 in match 1...and if by mistake I have them playing multiple times it'll show how many time I have them on the same court/game.

Hope that makes sense...any help would be great, this'll be growing to many games and probably many more people so just want to have this setup beforehand. Thanks.



[TABLE="width: 333"]
<tbody>[TR]
[TD]Game[/TD]
[TD]Court[/TD]
[TD]Player[/TD]
[TD]GameCourt (formula)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes

Excel 2010
ABCDEFGHIJKL
1GameCourtPlayerGameCourtPlayerGame1Game2Court 1Court 2Court 3Court 0
2111111111001
3115112110200
4118113111010
51112114111001
6122125111010
7126126110101
8129127110110
91213128111010
10133139110101
111371310110110
1213101311111001
1313141312111001
141041013111100
1510111014110110
1610151015110011
1710161016110011
1821421
19211121
20211321
2121321
2222722
23221022
24221422
2522222
26231523
2723523
2823823
29231623
3020120
3120620
3220920
33201220
Sheet1
Cell Formulas
RangeFormula
G2=COUNTIFS(C$2:C$33,F2,A$2:A$33,1)
H2=COUNTIFS(C$2:C$33,F2,A$2:A$33,2)
I2=COUNTIFS(C$2:C$33,F2,B$2:B$33,1)
J2=COUNTIFS(C$2:C$33,F2,B$2:B$33,2)
K2=COUNTIFS(C$2:C$33,F2,B$2:B$33,3)
L2=COUNTIFS(C$2:C$33,F2,B$2:B$33,0)
D2=A2&B2
 
Upvote 0
You could follow up by setting up Conditional Formatting to highlight cells within G2:L17 that are greater than 1 (Which would call out Player 2 plays on court 2 twice)
 
Upvote 0
Thanks...close...I want to make sure the players aren't playing each other multiple times (really just count how many times they're playing against each other)...so count how many times they have the same court/game as each other.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
help with combinations
Replies
3
Views
368
skillilea
S
B
  • Question Question
Simulator for one event in blackjack game
Replies
2
Views
372
baha17
B
A
  • Solved
Count entry by time
Replies
3
Views
113
admvlad
A
S
  • Question Question
Cost Per Person Per Attendance
Replies
3
Views
77
Eric W
Eric W
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
235
Alex Blakenburg
A

Forum statistics

Threads
1,222,907
Messages
6,168,963
Members
452,228
Latest member
just4jeffrey

Share this page

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.
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 "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
Back
Top