Final Friday Fix: December 2023 Challenge

We hope you’re having a great Christmas break!  On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend. On the following Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you, but we do appreciate your enthusiasm!

The Challenge

Imagine that you're presented with an intriguing linguistic challenge: to craft a list of words from a specified set of letters.  Manually filtering every single keyword and copying / pasting them to a new location can be a tedious and time-consuming process. 

Consider the following.  You're provided with two Excel Tables: List_of_Letters and List_of_Words. The List_of_Letters list contains a diverse collection of words, while the List_of_Words list also comprises an assortment of words.  

Your objective is to develop one [1] dynamic Excel formula which will look up words from List_of_Words that may be formed from letters of a single word in List_of_Letters Table, as shown in the picture below:

For example, if the word ‘Epiphany‘ is included in the List_of_Letters Table and the word ‘Happy’ appears in the List_of_Words Table, the formula will check if all letters of the word ‘HAPPY’ are present in at least one word from the List_of_Letters.  Since the letter’s ‘H’, ‘A’, ‘P’, and ‘Y’ are all found in the word ‘Epiphany’, the word ‘Happy’ will be included in the filtered list.  You may download the question file here.

As always, there are some requirements:

• no Power Query / Get & Transform or VBA is allowed
• the formula(e) should be dynamic so that they shall update when a new entry is added
• the solution should be case-insensitive, e.g.it should treat ‘E’ and ‘e’ similarly.

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!