Can I use Excel to solve this problem

P

pgilton

New Member
Joined
Oct 8, 2016
Messages
4
I manage a group of 40 senior golfers and each M, W and Friday I have to submit a roster of the pairings for that particular day. My challenge is to make sure that the same people are not always playing together. I'm looking for a tool that would display a count of how many times Player A has played with Player B - Z; Player B has played with Player A, C-Z; etc? I've tried to use Pivot tables in different configurations but to date have not been able to display the result I'm looking for.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
It is perfectly possible, but you'd need to share the layout of your file with us.
 
Upvote 0
I'm not sure how to properly use the forum to show you cells from my spreadsheet but this should do it for you:

Column A: List of Player 1 Names
Column B: List of Player 2 Names

In Column E (starting with row 2 I have a list of all potential players)
In Row 1 I have the same list of players starting in Column F and moving to the right (basically creating a matrix of names

In Cell F2 input formula: =countifs($A:$A,$E2,$B:$B,F$1)

CopyPaste Formula in the rest of the matrix.

You can then fill the cells black where a player would play themselves to not get confused and then add conditional formatting to the rest to make it easier to read.

I'm sure there are other solutions but that was the easiest way for me to see it. You can just continue to add Players and Opponents to columns A and B at will and the matrix will continue to update for you.
 
Upvote 0
Thanks For the quickreply. I understand the Matrix part and I have It built as youdescribed -- Column E Row 2 I have a list of all my players. I copied that listand Transposed it Starting in Column F Row 1 to create the matrix. I thenpasted your formula throughout the matrix. It is returning 0's everywhere butthe actual Player (Jon Doe is only finding Jon Doe).

I think My problemis in the data in Columns A and B. In Column A I have a list of all the playerswho played in September (Sample data to test). Some have played once some haveplayed nine times. in Column B I have the same exact list I'm not counting competitionsI'm trying to find out how many times Player A,Player, etc. has beenpaired with The other players in the group. I thinks I may need another data point like the dates that the players played.

Thanks in advance for your help.

 
Upvote 0
I also think your problem is with A and B... Instead of A being a list of all players in September, call A "Player 1" and call B "Player 2". In any order, you just type in each pairing and the matrix should tell you number of times played. For instance if A shows:

Bill
Tom
Bill
Bill
Frank

And B Shows:
Frank
Frank
Tom
Frank
Bill

You should see that Frank and Bill played 3 times, Frank and Tom played 1 time, and Bill and Tom played once in your matrix
 
Upvote 0
Thanks so much for your help!

Here's a small example of my actual data:

DateName 1Pairing
September 5, 2016Gary1A
September 5, 2016Matt1A
September 5, 2016Lyle1A
September 5, 2016Glenn1B
September 5, 2016Julian1B
September 5, 2016Marshall1B
September 5, 2016****1B
September 9, 2016Rudy1A
September 9, 2016Lyle1A
September 9, 2016Joe1A
September 9, 2016Julian1B
September 9, 2016Jose1B
September 9, 2016Joe1B

<colgroup><col><col><col></colgroup><tbody>
</tbody>

On September 5 Lyle was paired with Gary and Matt (Pairing 1A); Julian was paired with ****, Marshall and Glenn (Pairing 1B). On Sept 9 Lyle was paired with Joe and Rudy (Pairing 1A); Julian was paired with Jose and Joe (Pairing 1B). When I have those same names in both Column A and B COUNTIFS only returns data where the name is the same (Lyle 2, Julian 2, all others 1.

What I thought I would see was that Lyle has played with Gary, Matt (on 9/5) Joe and Rudy (on 9/9); likewise for Julian and the others. Maybe I just don't understand how countifs works.

Thanks for your patience.
 
Upvote 0
Here's a Month worth. It contains Date Played, who played on that date and the Pairing (1A played together on that day, 1B played together on that day, 18A played together on that day, etc.). I'm trying to solve how many times each player has been Paired with the other players in the list. Using COUNTIFS I can determine how many times each player has played but not how many times for example Gary has played with Matt, Lyle, Glenn, etc. Any help is greatly appreciated.

Thanks in advance

DateNamePairing
September 5, 2016Wingo,Gary1A
September 5, 2016Blender,Matt1A
September 5, 2016Dahlenburg,Lyle1A
September 5, 2016Francini,Glenn1B
September 5, 2016Coats,JC1B
September 5, 2016White,Marshall1B
September 5, 2016Kaucher,****1B
September 9, 2016D'Alonzo,Rudy1A
September 9, 2016Dahlenburg,Lyle1A
September 9, 2016Pollock,Joe1A
September 9, 2016Coats,JC1B
September 9, 2016Batlle,Jose1B
September 9, 2016Motil,Joe1B
September 9, 2016Goodroe,Ken18A
September 9, 2016Habiger,Andy18A
September 9, 2016Kaucher,****18A
September 9, 2016Gastley,****18A
September 9, 2016Busby,Fred18B
September 9, 2016Milton,Rick18B
September 9, 2016White,Marshall18B
September 9, 2016Farley,Ed18B
September 9, 2016Vandenbergh,Lew17A
September 9, 2016Hay,Albert17A
September 9, 2016Rogers,Dermott17A
September 9, 2016Tribble,Joe17A
September 14, 2016Milton,Rick1A
September 14, 2016Dahlenburg,Lyle1A
September 14, 2016Cook,Tom1A
September 14, 2016Wingo,Gary1A
September 14, 2016Busby,Fred1B
September 14, 2016Goodroe,Ken1B
September 14, 2016Jones,Jerry1B
September 14, 2016Kaucher,****1B
September 14, 2016Sigler,John18A
September 14, 2016D'Alonzo,Rudy18A
September 14, 2016White,Marshall18A
September 14, 2016Habiger,Andy18B
September 14, 2016Francini,Glenn18B
September 14, 2016Batlle,Jose18B
September 14, 2016Hiles,Ed18B
September 14, 2016Gastley,****17A
September 14, 2016Burros,Don17A
September 14, 2016Farley,Ed17A
September 14, 2016Pollock,Joe17A
September 15, 2016Goodroe,Ken1A
September 15, 2016Campbell,Joe1A
September 15, 2016Hall,Joel1A
September 15, 2016Hiles,Ed1A
September 15, 2016Leines,Egil1B
September 15, 2016White,Marshall1B
September 15, 2016Dolan,Rick1B
September 15, 2016Gastley,****1B
September 15, 2016Cook,Tom18A
September 15, 2016Burros,Don18A
September 15, 2016Pollock,Joe18A
September 15, 2016Vandenbergh,Lew18A
September 15, 2016Batlle,Jose18B
September 15, 2016Kaucher,****18B
September 15, 2016Wingo,Gary18B
September 15, 2016Gilton,Paul18B
September 21, 2016Milton,Rick1A
September 21, 2016Dolan,Rick1A
September 21, 2016Gilton,Paul1A
September 21, 2016Cook,Tom1B
September 21, 2016Hiles,Ed1B
September 21, 2016Hall,Joel1B
September 21, 2016Burros,Don1B
September 21, 2016Elberson,Gil18A
September 21, 2016Dalbey,Don18A
September 21, 2016Busby,Fred18A
September 21, 2016Habiger,Andy18A
September 21, 2016Batlle,Jose18B
September 21, 2016Gastley,****18B
September 21, 2016D'Alonzo,Rudy18B
September 21, 2016Vandenbergh,Lew18B
September 23, 2016Francini,Glenn1A
September 23, 2016Elberson,Gil1A
September 23, 2016Leines,Egil1A
September 23, 2016Dolan,Rick1B
September 23, 2016Campbell,Joe1B
September 23, 2016Habiger,Andy1B
September 23, 2016Goodroe,Ken18A
September 23, 2016Hiles,Ed18A
September 23, 2016Dalbey,Don18A
September 26, 2016Coats,JC1A
September 26, 2016Goodroe,Ken1A
September 26, 2016Kaucher,****1A
September 26, 2016Cook,Tom1B
September 26, 2016Habiger,Andy1B
September 26, 2016Gilton,Paul1B
September 26, 2016Wingo,Gary1B
September 28, 2016Hiles,Ed1A
September 28, 2016Cook,Tom1A
September 28, 2016Farley,Ed1A
September 28, 2016Goodroe,Ken1B
September 28, 2016Habiger,Andy1B
September 28, 2016Hall,Joel1B
September 28, 2016Francini,Glenn18A
September 28, 2016Dahlenburg,Lyle18A
September 28, 2016D'Alonzo,Rudy18A
September 28, 2016Dalbey,Don18B
September 28, 2016Batlle,Jose18B
September 28, 2016Coats,JC18B
September 30, 2016Batlle,Jose1A
September 30, 2016Kaucher,****1A
September 30, 2016Leines,Egil1A
September 30, 2016Farley,Ed1A
September 30, 2016Francini,Glenn1B
September 30, 2016Dahlenburg,Lyle1B
September 30, 2016White,Marshall1B
September 30, 2016Gilton,Paul1B
September 30, 2016Cook,Tom18A
September 30, 2016Wingo,Gary18A
September 30, 2016Dolan,Rick18A
September 30, 2016Etter,Alan18A
September 30, 2016Pollock,Joe18B
September 30, 2016Campbell,Joe18B
September 30, 2016Hiles,Ed18B
September 30, 2016Gastley,****18B
October 3, 2016Etter,Alan1A
October 3, 2016Dahlenburg,Lyle1A
October 3, 2016Habiger,Andy1B
October 3, 2016White,Marshall1B
October 3, 2016Kaucher,****1B
October 5, 2016D'Alonzo,Rudy1A
October 5, 2016Gastley,****1A
October 5, 2016Farley,Ed1A
October 5, 2016Batlle,Jose1B
October 5, 2016Goodroe,Ken1B
October 5, 2016Coats,JC1B
October 5, 2016Jones,Jerry1B
October 5, 2016Elberson,Gil18A
October 5, 2016White,Marshall18A
October 5, 2016Busby,Fred18A
October 5, 2016Dahlenburg,Lyle18A
October 7, 2016Tribble,Joe1A
October 7, 2016Elberson,Gil1A
October 7, 2016Hiles,Ed1A
October 7, 2016Dahlenburg,Lyle1B
October 7, 2016Cook,Tom1B
October 7, 2016Habiger,Andy1B
October 7, 2016Dolan,Rick1B
October 7, 2016Batlle,Jose18A
October 7, 2016White,Marshall18A
October 7, 2016Leines,Egil18A
October 7, 2016Goodroe,Ken18A
October 7, 2016Wingo,Gary18B
October 7, 2016Brown,Larry18B
October 7, 2016Pollock,Joe18B
October 7, 2016Coats,JC18B

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Golf Trip Pairings for 18 (3x6) with least amount of Repeats
Replies
0
Views
676
slaney7
S
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
175
etaf
etaf
S
  • Solved
Can I use VBA based on Cell Validation?
Replies
4
Views
409
sdmcavoy
S
D
  • Question
Pivot Table Filter - How can I filter out zero values within my uniquely formatted table?
Replies
0
Views
2K
dougemac
D
N
  • Question
Need Urgent Help Solving SUMIF Formula Issue Requesting urgent help to solve an issue with a SUMIF formula in Excel. I was asked by my manager to put
2 3
Replies
20
Views
1K
wk9128
W

Forum statistics

Threads
1,214,570
Messages
6,120,297
Members
448,954
Latest member
EmmeEnne1979

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