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.
HansVogelaar - I like the XLOOKUP solution because I understand XLOOKUP better than I do PIVOTBY. However, the Ws and Ls in the cells above the black diagonal cells should be the opposite of their order in the cells below the black diagonal cells. Like this:
So reading results by row, Boat1's results are L, L, W - each of those being below the diag line in each match's cell. Reading results by column, Boat1's results are L, L, W - each of those being above the diag line in each match's cell.
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.
