I did this using CONCAT(SORT(D10:G10,1,TRUE)) This gives me something like "BlackOrange" even if the home team is Orange. The approach that I used was to first get versing teams to consistently match up the same way. 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. 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. I did this because the answer you are looking for could be misleading. 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. 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 kept all my formulas to the right so they could be easily hidden. 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 couldn't quite find a single formula that would do the trick but I did populate your desired cells with the desired results. Finally, sum up either horizontally or vertically for each team to get the desired couldn't quite find a single formula that would do the trick but I did populate your desired cells with the desired results. The 2nd matrix is based on the result of the 1st matrix and only generate distinct count. Then use formula to fill all counts in the 1st matrix of all combinations. 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. Finally, the result will be the addition of 2 simple countif formulas ![]() Then I use additional column next to the pivot table to count the occurrence of same team pair but with reverse order. ![]() TeamA in my pivot table is actually Row field and TeamB is actually Value field. ![]() To create a pivot table from multiple ranges, use shortcut Alt+D+P then follow instructions at this link. The 1st one: create a pivot table from 2 Home Team - Away Team ranges (D10:G46 and L10:O46). Am able to obtain the expected results as shown in the attached workbook with 2 alternative solutions:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |