Forum Discussion
Excel formula for finding unique matches
- Jun 06, 2020
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.
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
- MountainViewJun 07, 2020Copper Contributor
Thank you for your help, Peterpeteryac60