Final Friday Fix: February 2022 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There are no prizes at this stage: you are playing for bragging rights only!

The Challenge

Sometimes, when you work with pivoted data that has a structure similar to a PivotTable, it is difficult to look up a value based on multiple column and row criteria.  To make it simpler, we usually unpivot the data.  Most of the time, you may choose to use Unpivot Columns function in Power Query.

This challenge is designed to make you think outside the box to find another way using only Excel formulas.  You can download the question file here.

This month’s challenge is to write a formula in one cell using Dynamic Arrays that will spill a range of cells to unpivot only the last three [3] columns (i.e. x, y and z) of the array in the file above.  The result should look like the array generated on the right (below):

As always, there are some requirements:

• the formula needs to be in just one cell (no “helper” cells)
• this is a formula challenge; no Power Query / Get & Transform or VBA!
• the formula needs to be flexible, so that if we adjust the number of rows and / or columns of the input table, the formula should still work
• obviously, the numbers of rows / columns of the output table cannot exceed the row / column limitations of Excel.

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