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 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:
Great solution and very useful to know for the future. I didn't know you could format a cell without referencing the data in the cell itself.
Thanks for the quick reply, Patrick2788. And you're right about the data error...good catch! Still studying your solution.