Monday Morning Mulling: October 2024 Challenge

On the final Friday of each month, set a challenge in Excel for you to solve over the weekend.  On the Monday, we publish one suggested solution.  No-one is stating this is the best approach, it’s just the one we selected.  If you don’t like it, lump it – or contact us with your preferred solution.

The Challenge

On Friday, we gave you a straightforward challenge – simply duplicate some values.  We wanted you to write a single formula that would duplicate the values from a column in a new column, as shown in the example below:

You can download the original question file here.  We have a list of values, and we need a single formula that will duplicate each value.  Bonus points were available if you could write a formula that gives a configurable number of duplicates.

Suggested Solution

Whilst there are undoubtedly many solutions to this challenge, we picked one that we found to be concise and flexible (or else it was the first one we thought of…).  The formula we came up with is as follows (our values are in the range C11:C17):

This one is all about manipulating text, so we start by using the REPT function to repeat the values with an added comma two [2] times.  We didn’t want to hard-code the two, so we refer to cell E8.  We can change E8 to repeat the values any number of times (within a generous limit imposed by Excel regarding string length).

As an interim step, it gives an array of the values repeated with commas, as shown here:

We then use CONCAT to join the values in that array.

This yields a string with all the repeated values separated by comma as below:

Finally, we can use TEXTSPLIT to create a single column with all the values, one per row.  We leave the col_delimiter argument empty, since we don’t want to split it into columns.  We specify a comma as the row_delimiter and use 1 in the ignore_empty argument, which prevents it from adding a blank cell to our output from the trailing comma on the last value in the list.

You can download our solution file here.  

The Final Friday Fix will return on Friday 29 November 2024 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.