Bam
Bam

Reputation: 173

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.

Upvotes: 2

Views: 480

Answers (1)

player0
player0

Reputation: 1

try:

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

enter image description here

Upvotes: 2

Related Questions