Forum Discussion
Excel formula for finding unique matches
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.
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
Thank you for your help. I like the second option.hynguyen