How many times did two people play together?

[Porsche Everson]

Greetings Friends. I have a simple 3 column spreadsheet that tracks a golf group that plays together many times over the season at various courses. The columns are:

Course Group# Name
Capitol 1 Everson
Capitol 1 Erickson
Capitol 1 Wendland
Capitol 1 Tokerud
Capitol 2 Duffy
...
Dungeness 5 Everson
...

I want to determine how many times players have played together (same course, same group#) during the season. I was thinking of a matrix output with row and column headings with the people's names, and the matrix values being the number of times each pair has played together over the course of the season.

Everson Erickson Wendland Tokerud Duffy ....
Everson x
Erickson 3 x
Wendland 2 5 x
Tokerud 4 1 2 x
Duffy 3 4 3 2 x
...

Can anyone suggest a formula that would populate this matrix, or perhaps an entirely different approach?

Thanks in advance for your help and insight.
--Porsche

 

[Sajan]

Hello Porsche,
Check out the attached workbook's summary tab.


Please validate the calculated amounts.

Cheers,
Sajan.

 

[Porsche Everson]

Thanks Sajan. This works perfectly. While the solution is way above my pay grade for this volunteer job <grin>, I look forward to digging in to your solution to learn how you did this.

I am grateful to you for devising a solution!

 

[Sajan]

Hi Porsche,
Happy to help! After you have had a chance to look into the solution, let us know if you have any questions.

-Sajan.

 

[brisky]

Hi Porsche,
Happy to help! After you have had a chance to look into the solution, let us know if you have any questions.

-Sajan.

Love the solution but notice it only summarizes the first 39 golfers. Can you make it summarize more golfers