Sometimes it’s useful to generate a column of data based on a series repeating x number of times e.g. a series 1,2,3 repeated 3 times would give 1, 1, 1, 2, 2, 2, 3, 3, 3 (see column A in here for example). In my particular scenario I want to repeat week numbers for a series from 0-6. There are a number of ways you can do this like indexing row numbers but here’s a little formula I quickly threw together for Google Sheets:
=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(D2,",")&",", D3))), ","))
- 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?