Nov 01 2022 12:04 PM
My club is in a tennis league and we regularly play matches with different partners. I would like to create a table that automatically combines the data so we can learn which two-player teams have the best win percentages. Normally, I would leverage a pivot table but I don't think it is the right use case.
Also, when scores are posted on the league website, the players are not normally listed in the same spot/column, which is another layer to this issue I'm having. Below is an example of the data I am collecting and how some players will be listed in the first position or second position. The second table is an example of how I'd like the final product to look like.
Player 1 | Player 2 | Set 1 | Set 2 | Set 3 | Win / Loss |
Player A | Player B | 3-6 | 0-6 | Loss | |
Player A | Player C | 6-2 | 3-6 | 7-5 | Win |
Player B | Player C | 6-0 | 6-3 | Win | |
Player A | Player C | 6-1 | 6-3 | Win |
Win Perc. | ||
Player A | Player B | 0% |
Player A | Player C | 100% |
Player B | Player C | 100% |
I'm sure this isn't a simple task or I probably would have figured out a solution by now. I appreciate your help with this matter. Just being pointed in the right direction is a big help. Thanks!
Nov 01 2022 12:32 PM - edited Nov 01 2022 12:45 PM
assuming you have excel 365 you should be able to do something like:
lets call table 1 "Data" and then in table 2 you have all the combinations listed (if you need a formula for that we can do that too), then I also suggest you might want to another column like # games so you can see if that 100% win is because they won 1 match or because they won 20/20 matches.
Formula options could be:
=SUM(--(LEN(FILTER(Data[Win / Loss],ISNUMBER(MATCH(Data[Player 1],Table2[@[Player 1]:[Player 2]],0)*MATCH(Data[Player 2],Table2[@[Player 1]:[Player 2]],0)),""))>2))
and similar for the count of wins but instead of LEN(...)>2 just use ="Win"
see example attached.
Edited for a couple mistakes and to attach sample
Nov 01 2022 12:50 PM
=(COUNTIFS($A$2:$A$10,B14,$B$2:$B$10,A14,$F$2:$F$10,"Loss")+COUNTIFS($A$2:$A$10,A14,$B$2:$B$10,B14,$F$2:$F$10,"Win"))/(COUNTIFS($A$2:$A$10,A14,$B$2:$B$10,B14)+COUNTIFS($A$2:$A$10,B14,$B$2:$B$10,A14))
You can try this formula.
Nov 01 2022 01:33 PM
Nov 01 2022 01:34 PM
Nov 01 2022 02:55 PM - edited Nov 01 2022 03:06 PM
@fensterbos I am attaching a new copy with a formula to automatically calc all combinations. I modified a code recently done for another recent post (challenge). In this example I used @OliverScheurich 's approach as I think it may be cleaner but either works
EDIT: modified attached to use array formulas for the total matches and Win %