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,
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!