Jul 05 2023 04:28 PM
Jul 05 2023 04:28 PM
I have a wordlist and want to write a formula that will find every word that contains five alphabet characters in any order.
Here is my formula. It indicates what the first letter must be (I1) and what letters must be in the word (anywhere from three to five characters). My formula only searches for words in the wordlis that contain the characters appearing in the same order as in the formula. If I search for w-o-n-d-e, it will find wonderful, but it won't find endow.
Column E is a string containing the letters to search for. Columns R - V show each character in the string in a separate cell.
Can someone show me a formula that will work?
Also, even if the order of the characters happens to be right, it will only find the first instance of a word that contains all the characters. I would like to have a list of all the words that contain all the characters.
Jul 06 2023 01:39 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Jul 06 2023 09:57 AM
Jul 06 2023 03:12 PM
O dear, that's far too complicated for me, sorry! I hope that the sample workbook enables someone else to help you.
Jul 07 2023 09:59 AM
Jul 07 2023 01:41 PM
Thank you for your reply.
Here is a screenshot with no formulas containing what I believe is all the information necessary to solve the problem from scratch and showing what the results might look like.
Jul 07 2023 02:26 PM
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.
Jul 07 2023 03:29 PM
Jul 07 2023 06:07 PM
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
Jul 07 2023 09:24 PM
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.
Jul 07 2023 10:28 PM
Suppose we have
Just include like absolute
or must match fully no redundancy letter like absolute
The first letter of the second word should be the last letter of the first word?
Only 2 words match whole puzzle square
How about 3?
, we need to eliminate every word that has adjacent letters from the same side of the squar
Can you provide a sample of this sentence?
eliminate every word that has adjacent letters like
with adjacent letter ss?
The word list is long... 214,066 words.
your dictionary has 214,066 words?
But my dictionary on hand only 13,532 words and phrases
Jul 08 2023 12:01 AM
Your third line "te" is missing a letter, but we can ignore that for our purposes.
*Yes, "absolute - exist" is okay. There can be redundancy.
*Yes, the first letter of the second word must be the last letter of the first word.
*Yes, you can use all the puzzle letters with three words "bobs - salute - exist," but I don't care to solve for that. I only want to solve for two words.
*Boundless is illegal because of the two ss. In your example letters, boundless would also be illegal because o and u are adjacent, and they appear together on one side of the square "olu."
*If your wordlist is too short, solutions may not be possible. I use the Scrabble word list. It doesn't have all the words that the puzzle maker uses, but it's close enough.
Jul 08 2023 10:32 AM
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.
Jul 08 2023 02:53 PM
Jul 08 2023 05:11 PM
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,
Jul 08 2023 05:20 PM
o and u are adjacent in the word doubtless, and o and u come from the same side of the square. The letters on the square can be in any order. We only look at the word for adjacency.
The pair, class - boundless are illegal because they have adjacent letters... s s and o u.
It is possible to solve the puzzle with two or more words that use letters from the square, each word's letters unique as to the other words, but that is not necessary. As you can see in the list below, letters from the square can be used multiple times in all the words that solve the puzzle, so long as none of the words have adjacent letters from the same side.
As I indicated previously, I only care about two-word solutions.
I am still working on a way to find all matches, not just the first one in the list.
Thank you for your help.
Jul 09 2023 04:43 PM - edited Jul 09 2023 04:43 PM
Don't blame you! I can't let it go, yet. I want to find all the instances of matching pairs that are in my dictionary...
Jul 10 2023 12:52 AM - edited Jul 10 2023 01:43 AM
I was finally able to make Filter work to find all possible matches for all legal words with this formula.
The word in column AH in the sheet snippet below is the first word to solve the puzzle and the words in columns AI, AJ, et. seq. are candidates for the second word.
I could not figure out how to make the formula return only words that start with the last letter of AH or end with the first letter of AH. Only the words highlighted in yellow in cells AI, AJ, et. seq., in the sheet below (a small snippet of the full list) solve the problem.
Will someone please help me revise the formula to only find legal words (in AI, AJ, et. seq.) that start with the last letter of the first word (AH) or end with the first letter of the first word (AH)?
This filter would filter the results of the above filter to find only the words that end with the first letter of the first word. So, I am part way there. I just need to add the filter that also finds any words that start with the last letter of the first word. Maybe these filters could be nested? That would be it, I think.
Jul 10 2023 02:54 AM
The formula used in the solution I posted above was
... firstWord, TOCOL(IF(allChar=maxChar, legalList, NA()), 3), secondWord, TOCOL(IF(allChar=maxChar, TRANSPOSE(legalList), NA()), 3), FILTER(HSTACK(firstWord, secondWord), RIGHT(firstWord, 1) = LEFT(secondWord, 1))
The next task would be to optimise the formula to improve performance.
For example, I checked every character within a Scrabble word to see whether it was drawn from the Square. Had I followed your suggestion and checked for the occurrence of other characters, then recursion would allow one to bail out before the whole word is validated.