Final Friday Fix: October 2024 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

This month’s challenge is a straightforward one.  We want to duplicate the values from one column into a new column.  The aim is to write a single formula (dynamic array?) that duplicates each of the values in a column, as shown below:

Background

Our challenge was inspired by a question we received.  The query received was to write a formula and then fill it down such that each value would be repeated.  They wrote a formula in the first two [2] rows of the new column to duplicate the first value.  Then they filled down, and voila! – they didn’t get what they wanted.  They wrote the formulae for the next two cells to continue the pattern and filled down, but that still didn’t give the result they were looking for.

For this challenge, use a formula, not Power Query or copying and pasting.  Brute force is not the solution (and I already have ignorance sewn up!), even though it can get the job done at times.

You can download the original question file here.

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