Final Friday Fix: January 2023 Challenge

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

The Challenge                            

Filtering data in a Table in Excel is as easy as clicking the filter button then ticking the data you want, right?  However, if you want to see all but a few choice options in a field with many different entries, you may find yourself scrolling tirelessly to find and untick the few you don’t want to see.  Luckily, there are several ways to filter data based off of a list of values to exclude, which can be achieved using only formulae.  In this month’s challenge we invite you to do just that, you can download the question file here.

This month’s challenge is to write a formula to replicate data in a Table, removing entries as specified in a second Table.  The starting Table (here, imaginatively called Data) might be as follows:

The data to remove Table (named Remove) may look like this:

The result, using the current inputs, should look similar to the below:

As always, there are some requirements: 

• the formula needs to be within just one cell (no “helper” cells) 
• this is a formula challenge; no Power Query / Get & Transform or VBA
• the formula should be dynamic enough to update when entries are added to the Remove Table. 

Feel free to use the attached Excel file to assist you.

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