Final Friday Fix: December 2021 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!

2022 is coming…

This is a special challenge of Final Friday Fix series.  Hope you enjoy it while celebrating the New Year with your family, friends and colleagues!

The Challenge

Sometimes, you may need to append separate tables into one big table.  To solve this, you may choose to manually copy and paste one table below another or use another more advanced way in Power Query, i.e. Append Queries.

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 vertically append two arrays in the file above.  The input tables in the question are different, but the result should look similar to 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 two input tables, 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!