Forum Discussion
Lookup five characters appearing in any order in a wordlist
Worn out!
I was finally able to make Filter work to find all possible matches for all legal words with this formula.
=IFERROR(TRANSPOSE(FILTER($A$2:$A$3570,ISNUMBER(SEARCH(AA451,$F$2:$F$3570)))),"")
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)?
Addendum:
=FILTER($AK$451:$JL$451,RIGHT($AK$451:$JL$451,LEN(AI451))=AI451,”No Results!”)
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.
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.
Unfortunately, I don't know how to implement your formula, but on the plus side... I got it to work, finally, with a nested filter and an "or" function. I kept making little errors in the formula, which is why it wasn't working. And then I would try another approach, and another...
=FILTER(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),(RIGHT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=I57)+(LEFT(IFERROR(TRANSPOSE(FILTER($A$2:$A$3528,ISNUMBER(SEARCH(AA57,$F$2:$F$3528)))),""),1)=H57))
The problem does draw one back. I had a go at using recursion to remove words that have any letter that is missing from the square. Although, on average, this requires far fewer comparisons, the calculation time only went down from 5sec to 3sec.
The methods I use have, in the main, only become possible with 365 and I have dropped almost all the standard practices of traditional spreadsheet development. The new and the traditional approaches share a common function library and work on a 2D grid but little else is similar.
