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 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.)
OK, SnowMan55, I think I have gotten everything to work in my workbook!
I got all the conditional formatting of the matrix to work
I got the XLOOKUP formula to work in the matrix (and finally understand HOW it is working)
I learned the use of "|" in the formula from HansVolgaar
I got your "contradictory results" formula to work checking the table for contradictory matches
So I think I am good to go!
My final question is...can I select both you and HansVolgaar as the solution, since I pulled tricks from both of you?
