Forum Discussion
Lookup five characters appearing in any order in a wordlist
You say you are using 365, in which case formulae such as
= LET(
characters, TEXTSPLIT(letters,"-"),
hasChr?, ISNUMBER(FIND(characters, wordlist#)),
wordMatch?, BYROW(hasChr?, LAMBDA(x, AND(x))),
FILTER(wordlist#, wordMatch?)
)might provide a good starting point.
- Hi, Peter,
Thank you for this. I don't how to implement. Would you mind explaining a bit more how this would work with the sample spreadsheet I sent in reply to Patrick? In the meantime, I will do some research on the functions that are unfamiliar to me.Column B = words from the wordlist that contain one or more of the twelve letters
What do you mean one or more?
In the attached from Peter Bartholomew ,I have found All 5 letters matched at the same time not include 1,2,3,4 these 4 scenarios.
About the picture you uploaded,why diptych and forward match?
Are they the expected result?
I am really confused about the expected result?
Can you give more detail explains by an example?
Can you explain
The word list is long... 214,066 words. First, we need to eliminate every word that has adjacent letters from the same side of the square and every word that includes one of the 14 letters of the alphabet that are not included in the puzzle square. That will narrow the list down to 2,000 (give or take 1,000) words.
From the remaining list, the challenge is to find the fewest number of words combined that use all the letters in the puzzle squared. I shoot for two words. That is the real challenge.
Today's letters are
Top of puzzle square - YOD
Right side of puzzle square - WHI
Bottom of puzzle square - ATC
Left side of puzzle square - FRP.
"Forward" contains only letters from the puzzle square (in red), no letters that are on the same side of the square are adjacent to each other, and no letters that aren't in the puzzle, so it meets all the rules.
"Diptych" contains all the rest of the letters in the puzzle square, no letters that are on the same side of the square are adjacent to each other, no letters that aren't in the puzzle, and it starts with "D," the last letter of "Forward," thus meeting all conditions of the puzzle.
Forward and Diptych are one matching pair, but there could be others, and they all must meet the same conditions.
On some days, It might be possible for there to be a word that uses 11 of the letters in the square and meets all other conditions of the puzzle. In that case, a two-letter word could be a match if it starts with the last letter of the first word and uses the twelfth letter in the square.
The second word can re-use letters from the first word as long as it also uses all the remaining letters from the square and meets all the other conditions.
I was mistaken about a one-letter word, because if the first word uses all twelve letters and meets all the other conditions, the puzzle is solved with just the one word. It's rare, though.
Thanks,
Michael
Sorry, this task is going to take too long. So far all I have done is filter the original scabble list to return only words comprising letters taken from the square. The adjacent letters rule is not as straightforward but most likely your excluded character pairs approach will prove effective.
The new Excel functions LET and LAMBDA enable the development of array solutions that do not look remotely similar to any past spreadsheet practice. LET introduces local variables scoped to the formula. LAMBDA builds on that to allow values to be passed to such variables by using an argument list. The result is to allow a spreadsheet formula to be laid out like a program module, calling other Lambda functions where required.
I've come a lot closer to solving the problem. All I am missing now is a way to find all solutions for each valid word in the list. VLOOKUP and XLOOKUP will only return the first match in the list. I tried using Filter in place of XLOOKUP but got a #calc error.
Here is a screenshot from the spreadsheet after hiding the dozen or more columns that do all the work.
Gauche is also a valid word with a solution, but the formula only finds the first instance that meets the conditions, and that word happens to be invalid, so gauche is excluded from the list.
I would love to see an example of the LET LAMBDA combo as it applies to this problem.
Thanks for your help,
Michael
