Forum Discussion
Populating a Matrix from a Table
Re: Re 1 — Yes, and the volume of information gets "better", as you will see in my responses to your notes.
Re: Re 2 — You will see the problems in the second attached workbook. result2 is the (intententional) cause of the duplication, not involved in prevention of such. The LENgth functions are just another way of checking for empty strings.
If your Flight + Match combinations are unique, you won't get "contradictory results" by changing boat/team/skipper names. (But rematches late in the regatta would be a problem, which we've not discussed.)
Re: Smokey and the Bandit — No, I never knew that, as I did not watch those movies.
I don't understand your data, for example:
In Day 1, Match 1, Boat 1 both loses to and wins from Boat 2. What am I missing?
HansVogelaar - Thanks for your patience. I was trying to make up a pared-down example, but here is a real example from Round Robin 1 of the 2025 Congressional Cup match race regatta. So I'm attaching a different excel file. I've changed the W/L column headers to Points, because that is really how match racing works. One can lose a race and get 0 points, or one can lose AND get penalized, thus ending up with negative points for that race. And it's important to know which boat entered the starting area on the port tack versus the starboard tack (dictated by the race schedule), as starboard is an advantage, thus the 25 wins in the Starboard column vs the 20 wins in the Port column.
Let me know if this example makes more sense.
Here is your new workbook with formulas and conditional formatting.
Thanks HansVogelaar! Very clean, easy to understand XLOOKUP, both in the matrix and in the conditional formatting. But how does the "|" work in the formula? I've never seen this character used before.
The "|" is probably not necessary, but I used it as separator between the team names. I chose "|" because it is unlikely that a team name will contain that character. I could have used another character just as well, for example "#" or "@".