Forum Discussion
Populating a Matrix from a Table
I want to populate a matrix of sailing regatta competitors showing how each boat did against each of the other boats based on a list of matches. I have attached a simplified version with only four boats. The list of match results is on the left and the matrix I want to populate is on the right. In the matrix, I show by color which matches are on which day, but I'd prefer that a conditional format do that for me.
In the matrix, the rows are the boats in Col. 1 of the table and the columns are boats in Col. 2 of the table. So for instance, for Day 1 / Match 1, Boat 1 (I6) would get aN "L" in K6, to show that Boat 1 lost to Boat 2. And Boat 2 (I7) would get a "W" in J7 to show that Boat 2 beat Boat 1. And those two cells (K6 and J7) would be green, as that is the color corresponding to Day 1 of the regatta.
So how can I populate that matrix like that? And how can I conditionally format the color of the cell based on the day of the regatta?
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.
28 Replies
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.
I contend that you do not need to enter two lines of data per match, and because of the extra typing and potential errors, you should not do so.
The attached workbook demonstrates this with one line of data per match. Because it uses the VSTACK function, Excel 365 or Excel for the web is required.
SnowMan55 - your attachment will take me some time to get through, but I appreciate the explanation. And you picked great boat names!
SnowMan55 - you're right, if I were creating the data. But the data come from a website in the format shown in my example and I'd have to reformat the data from the website every time.
Very well, though you can end up with inconsistent match results (and you did, as Patrick and Hans and I all noticed).
So in this newer attached workbook, I have formulas that can handle either one row or two rows of competition data per match, and it includes formulas to identify inconsistent match results.
(When you find a solution that meets your needs & desires, please mark it as "the" solution.)
I think your demo has a mistake with how the data is entered for Day 1 versus the expected results. I've corrected the entries.
My solution involves tabling the data and then doing some aggregation:
=PIVOTBY(RegattaTbl[Col. 1], RegattaTbl[Col. 2], RegattaTbl[Result1], SINGLE, , 0, , 0)Patrick2788, the only problem with your solution is that I like the upper left cell of the matrix to show that the results are for the boats in the rows and are from Col. 1 of the table:
But your PIVOTBY formula is in that cell. Also, I like peiyeshu's solution that has each cell of the matrix showing the result for both the row and column boat, like this:
Is there a way to use PIVOTBY to do this?
If you really need that for display purposes, I'd leave the formula as it is and do it through custom cell formatting:
Thanks for the quick reply, Patrick2788. And you're right about the data error...good catch! Still studying your solution.
See the attached version.
HansVogelaar,
I like your solution, but I also like peiyeshu's solution that has each cell of the matrix showing the result for both the row and column boat, like this:
Is there a way to use XLOOKUP to do this?
How about this?
create temp table aa as
select f01,f02,f03,f06,f04 from Sheet1 group by f01,f02;
create temp table bb as
select * from aa union all
select f01,f02,f06,f03,iif(f04='L','W','L') from aa;
select * from bb order by f01,f02;
cli_create_two_dim~bb~f06~f04;
select * from bb_two_dim;
select * from Sheet1;
create temp table aa as
select f03,f06,'<div style="background:'||case f01 when 1 then 'Grey' when 2 then 'red' when 3 then 'Yellow' else 'White' end ||'">'||f04||'</div>' o from Sheet1 union
select f06,f03,'<div style="background:'||case f01 when 1 then 'Grey' when 2 then 'red' when 3 then 'Yellow' else 'White' end ||'">'||f05||'</div>' o from Sheet1;
//select * from aa;
cli_create_two_dim~aa~f06~o;
select * from aa_two_dim;
only use one column result f04:
select * from Sheet1;
create temp table aa as
select f03,f06,'<div style="background:'||case f01 when 1 then 'Grey' when 2 then 'red' when 3 then 'Yellow' else 'White' end ||'">'||f04||'</div>' o from Sheet1;
cli_create_two_dim~aa~f06~o;
select * from aa_two_dim;
