Forum Discussion

mhlester's avatar
mhlester
Copper Contributor
Jun 29, 2023

finding words in a list

I am trying to create a spreadsheet using formulas to find words in a list that meet certain criteria. There are nearly 375000 words in the list and there are 50 criteria. This spreadsheet is intended to find solutions to the New York Times Letter Boxed puzzle. The puzzle is built around a square with three different letters of the alphabet on each side. The challenge is to solve the puzzle by using all twelve letters in as few words as possible. Solving the puzzle in two words is the ultimate goal. No word can have two letters from any of the four sides of the square adjacent to each other. There are 36 combinations of letters that cannot appear in a word used in the solution. There are 14 letters that do not appear on any side of the square and they cannot appear in a word used in the solution. One other condition is that the second word must start with the last letter of the first word.

 

With my limited knowledge of Excel, I could not find a way to create a formula that would take into account all these criteria, so I have 50 * 370,000 cells all filled with formulas. The spreadsheet takes forever to calculate, and it only solves for the first word. In my example, there are 2024 possible first words if my formulas are working right. Only a fraction of these words will work with a second word that meets the criteria.

 

The second word, as previously stated, must start with the last letter of the first word and must contain all the letters around the square that have not been used in the first word. It is permissible to also use letters in the second word already used in the first word. the same 50 criteria that apply to the first word apply to the second word.

 

I think a macro would be better suited to solve this problem, but I am not good with macros.

 

There are some websites that will solve the puzzle when you provide the twelve letters around the square. I don't care about those websites. It's fun to create excel spreadsheets that solve problems. If I were a programmer, knew VBA, or could write macros, I would do that, too.

 

Here are the letters in today's letter boxed puzzle.

 

Top - OLP

Left - BTH

Bottom - MAC

Right - EIR

 

Here is the link https://www.dropbox.com/scl/fi/2tefjg4todnb26x6u4h7c/Scrabble-Dicitionary-for-Letter-Boxed-230701.xlsx?rlkey=l7dhbf5s4scaop3g0ovbfruyo&dl=0 

 

I would appreciate any help you might be willing to provide.

 

Thanks,

Michael

excel
Formulas and Functions
Macros and VBA
office 365

3 Replies

  • peterson223's avatar
    peterson223
    Copper Contributor
    May 22, 2025

    Hey Michael,
    That sounds like a really fun and challenging project at a time. because building a spreadsheet to solve letter boxed is no small task with so many criteria. It sounds like a macro or a simple script might help speed things up and reduce the load on Excel. Something like VBA or even a small Python script could make things a lot easier by filtering words based on your rules before they even hit the spreadsheet. However, if you are just looking to check solutions or want help for daily puzzle letter boxed puzzle then you can visit here for today https://letterboxedanswer.net/ and can quickly get solution based on today letters and might be helpful reference for you to test your formulas. 

    Best of luck refining your spreadsheet😊

    • mhlester's avatar
      mhlester
      Copper Contributor
      Jun 29, 2023

      davidleal 

       

      Hi, Davidleal,

       

      I've been fiddling with the file and have it working, but it needs a final step or two.

       

      Here is the link to the new file. It's much smaller.https://www.dropbox.com/scl/fi/2tefjg4todnb26x6u4h7c/Scrabble-Dicitionary-for-Letter-Boxed-230701.xlsx?rlkey=l7dhbf5s4scaop3g0ovbfruyo&dl=0 

       

      Here is a key to the structure of the file...

       

      • Column A is a 214,066-word wordlist
      • Column B indicates whether the word is a legal word for the puzzle (In)
      • Column C counts the number of unique characters in the word in Column A
      • Column D is a list of all the legal characters
      • Column E is a list of the characters missing from the word in Column A
      • Column F is a list of the unique characters in the word in Column A
      • Column G tests only one word at a time to find another word that uses the missing characters and either starts with the last letter of the first word, or ends with the first letter of the first word

       

      The problem: The formula in Column G must be rewritten for each possible first word in Column A until a complementary second word in Column A is found.

       

      The proposed solution: Write a Macro that tests every legal word in Column A (that contains at least seven unique characters) to find every complementary second word in Column A and print a report of all the pairs found (see example from an online Letter Boxed Solver below).

       

       

      Thanks,

      Michael

Resources