finding words in a list

[mhlester]

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 Letter Boxed Spreadsheet - Working 

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

Thanks,

Michael

[#2]

please share the excel file

[#3]

@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. Letter Boxed - Working 

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