Excel formula for finding unique matches

[MountainView]

Hello. I run the schedule for a recreational soccer league and need help with a formula. Columns D and G list the teams versing on Mondays while columns L and O list the teams versing on Wednesdays. Teams play each other at least once and some teams will play each other twice. I need a formula that looks through all the games and counts how many distinct teams each team plays. This will ensure that the schedule is correct and each team plays all the other teams at least once.

I wanted to have the formula in Y47 through Y56 and if the schedule is correct, the value the formula should return is 9 (10 teams means that the number of possible distinct games is 9).

Is this possible to to? Can anyone help with this? Thank you much for any assistance you can provide. I attached the file to the post.

[#2]

@MountainView 

Hi 

I am not sure if this is what you want?

In column Y I have added up how many times each team plays on Monday. This shows that team WHITE plays 10 times including playing twice against both GREEN and RED. I am guessing this is wrong?

In column AB I show the totals for the Wednesday matches. 

Have a look and see id this is what you need.

thanks

Peter

[#3]

@MountainView I am able to obtain the expected results as shown in the attached workbook with 2 alternative solutions:

- The 1st one: create a pivot table from 2 Home Team - Away Team ranges (D10:G46 and L10:O46). To create a pivot table from multiple ranges, use shortcut Alt+D+P then follow instructions at this link: https://www.contextures.com/xlPivot08.html.

TeamA in my pivot table is actually Row field and TeamB is actually Value field. Then I use additional column next to the pivot table to count the occurrence of same team pair but with reverse order. Finally, the result will be the addition of 2 simple countif formulas

- The 2nd one: use TextJoin to combine Home Team and Away Team from both ranges (D:G and L:O) into a same column A in a new Tab. Then use formula to fill all counts in the 1st matrix of all combinations. The 2nd matrix is based on the result of the 1st matrix and only generate distinct count. Finally, sum up either horizontally or vertically for each team to get the desired output

[#4 · Accepted solution]

@MountainView 

I couldn't quite find a single formula that would do the trick but I did populate your desired cells with the desired results.  I kept all my formulas to the right so they could be easily hidden.  My formulas utilize the new SORT and UNIQUE functions so this spreadsheet will not work properly in any version of Excel prior to 2016.  I also added an alternative view of the team pairs so you can see which team pairs play only once and which pairs play more than once.  I did this because the answer you are looking for could be misleading.  If I changed one of the teams playing together of a pair that is playing 2 games in the season then uniqueness will remain at 9.  If I changed one of the teams for a pair that is playing only 1 game together then the uniqueness drops to 8 as expected.

The approach that I used was to first get versing teams to consistently match up the same way.  I did this using CONCAT(SORT(D10:G10,,1,TRUE))  This gives me something like "BlackOrange" even if the home team is Orange.  I then added a COUNT on the results making it easy to see which team pairs play only one game together and which ones play more than once.

[#5]

Thank you for your help, Peter@peteryac60 

[#6]

Thank you for your help. I like the second option.@hynguyen 

[#7]

Thank you much. That's what I was looking for@xspJody