Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaflattengoogle-query-language

Repeat each row with empty rows n times - google sheets


I'm trying to repeat each row from the example column n times, very similar to this question (Repeat each row N times in Google Sheets) however, I am also looking to add n empty rows between each repeating element.

Example Column:

Name
Dog
Cat
Ball
------

Desired Output:

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

As answered in the question linked previously, the rows can be repeated by using:

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

5(n) being the amount of times the rows are repeated

I'm able to achieve 'empty' rows but only at the end of each full iteration using:

=TRANSPOSE(split(rept(join(";",A1:A3)&"; ; ; ; ;",3),";"))

(Each white space between the semicolon "; ; ; ;" acting as n+1)

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

I'm struggling to understand how to merge the two formulas or find a better solution.


Solution

  • try:

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

    enter image description here