Forum Discussion

mhlester's avatar
mhlester
Copper Contributor
Jul 05, 2023

Lookup five characters appearing in any order in a wordlist

Hi, all,

 

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.

 

=IF(C1>=9,VLOOKUP(I1&"*"&LEFT(E1,1)&"*"&MID(E1,2,1)&"*"&RIGHT(E1,1)&"*",$A$2:$F$1200,1,0),IF(C1=8,VLOOKUP(I1&"*"&S1&"*"&R1&"*"&U1&"*"&T1&"*",$A$2:$F$1200,1,0),VLOOKUP(I1&"*"&LEFT(E1,1)&"*"&MID(E1,2,1)&"*"&MID(E1,3,1)&"*"&MID(E1,4,1)&"*"&RIGHT(E1,1)&"*",$A$2:$F$1200,1,0)))

 

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.

 

Thanks,

Michael

excel
Formulas and Functions
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 06, 2023

    mhlester 

    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?

    • mhlester's avatar
      mhlester
      Copper Contributor
      Jul 06, 2023
      Hi, Hans,

      Thank you for your reply. Here is a link to the complete dropbox file.

      https://www.dropbox.com/scl/fi/otl468c3tqoap0cnetd66/Scrabble-Dicitionary-for-Letter-Boxed-230705-2.xlsx?rlkey=mazzcaauxulfrxz1ony9g9fku&dl=0

      The spreadsheet is intended to solve the New York Times Letter Boxed puzzle, which puts 12 unique alphabet characters around a square, three to a side. You must use all twelve letters in as few words as possible. No two characters from the same side of the square can be adjacent in any of the words. (I only want to solve for two words even though the puzzle lets you solve in more than two words.) The second word must start with the last letter of the first word.

      Column H is a solver but it can only solve one row at a time and I have to change the formula to reflect the row number each time. Too much work! I would like to automate that.

      Columns I through L are a second solver that could work to find the first legal word for each word in the list if the lookup criteria didn't have to appear in the same order as in the second word of the pair.

      Column A = wordlist
      Column B = words from the wordlist that contain one or more of the twelve letters
      Column C = number of unique letters in the word in each row
      Column D = filled with the twelve letters from the day's puzzle
      Column E = letters that are missing from the words in each row
      Column F = the unique letters in each row
      Column G = repeat of the wordlist (can delete)

      Thanks, Michael
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 06, 2023

        mhlester 

        O dear, that's far too complicated for me, sorry! I hope that the sample workbook enables someone else to help you.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 07, 2023

    mhlester 

    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.

    • mhlester's avatar
      mhlester
      Copper Contributor
      Jul 07, 2023
      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.
      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Jul 08, 2023

        mhlester 

         

        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 

    • mhlester's avatar
      mhlester
      Copper Contributor
      Jul 09, 2023

      PeterBartholomew1 

       

      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... 

    • mhlester's avatar
      mhlester
      Copper Contributor
      Jul 10, 2023

      PeterBartholomew1 

      HansVogelaar 

      @Patrick2788 

      @peiyezhu 

       

      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.

       

       

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Jul 10, 2023

        mhlester 

        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.