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.
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?
You can mark only one reply as the solution - choose whichever you prefer. You might give the other one a "Like".
SnowMan55,
Thanks for your detailed "Info" sheet. It really helped me understand the formulas. I am attaching:
- your Excel file with a new date and my comments in your Info worksheet
- my actual 2025 Con Cup Results workbook, to which I am trying to apply what I have learned from you and the other Excel geniuses.
Re 1., this has been an opportunity to learn so many Excel tricks that I didn't know. I thought I would just learn how to do XLOOKUP for rows and columns at the same time, or maybe VLOOKUP and HLOOKUP in the same formula. But I've learned LET, UNIQUE(VSTACK), OFFSET(COUNTA), dynamic named ranges, use of the # in a cell reference... Crazy!
Re 2., if you open my Con Cup workbook, you'll see that I applied many of the tricks I've learned, though I have not even gotten to the conditional formatting yet. But the crux of my challenge is the XLOOKUP in the matrix, and I can't get it to work on my Con Cup worksheet, hence my attaching it. I've followed your format, but substituted my names ranges. And I think I understand the LET convention, but I'm not understanding the XLOOKUP nested within it.
Is it true that result2 is to confirm that there is no duplication of that match?
How does the length of result1 or result 2 play into it?
And can you explain the use of the "|" character in that formula?
Also, I got your Col. H formula (to confirm no "contradictory results") to "work" (meaning no error message) in my Con Cup workbook, but when I test it by changing competitor names to create two contradictory matches, the formula does not return "contradictory results...".
Thanks so much for your continuing patience.
PS - is your Microsoft Community "handle" in honor of Burt Reynold's sidekick in the movie Smokey and the Bandit?
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.
SnowMan55,
Thanks for all the help. I'm going to close this out, as I have another Excel challenge on which I want to get started. Take care!
