=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(D2,",")&",", D3))), ","))
where
- D2 is a comma separated series e.g. Week 0,Week 1, Week …
- D3 is the number of times to repeat
How does it work?
Like a lot of spreadsheet formula is starts in the middle with SPLIT(D2,",")
which turns our series of values into an array. If you use this in a single cell in a Google Sheet the values Week 0, Week 1 will be split out across the columns.
Next we want to repeat Week 0 and so on x number of times. This is done with the a combination of REPT, which repeats a given string x times. If we use this by itself it will only apply to the first column of data from the SPLIT so we wrap it in an ARRAYFORMULA like so ARRAYFORMULA(REPT(SPLIT(D2,",")&",",D3))),","))
. This repeats the series value the number of times specified in D3. Something to note is the &“,” in the REPT. This adds a comma at the end of each repeated value.
ARRAYFORMULA Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
This now gives us our columns with the repeating text but across several columns e.g. “Week 0,Week 0,Week 0”, “Week 1,Week 1,We..” etc To get a single value in each column we use a trick of using a JOIN to turn our array of columns into a single cell value separated with a comma. We then use the SPLIT formula again to turn this single cell into multiple cells.
The final part is to use TRANSPOSE to convert our columns of data into rows. Here is the finished version of the Google Sheet with the stages broken down.
Can you think of a better way to do this?