Search code examples
google-sheetssplitgoogle-sheets-formularepeattranspose

Repeat each row N times in Google Sheets


I have a column containing 3 rows and I want to be able to repeat those rows 5 times each.

Example

Name
Dog
Cat
Ball

Desired Output

Output
Dog
Dog
Dog
Dog
Dog
Cat
Cat
Cat
Cat
Cat
Ball
Ball
Ball
Ball
Ball

Here's what I have tried:

=TRANSPOSE(split(rept(join(";",A:A)&";",5),";"))

My attempt produces:

Output
Dog
Cat
Ball
Dog
Cat
Ball
Dog
Cat
Ball
Dog
Cat
Ball
Dog
Cat
Ball

Solution

  • =ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(
     REPT(A1:A3&"♠", 5), ,999^99), "♠"))))
    

    0


    =SORT(TRIM(TRANSPOSE(SPLIT(QUERY(ARRAYFORMULA(
     REPT(A1:A3&"♠", 5)), ,999^99), "♠"))), 1, 0)
    

    0