Reputation: 173
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
Reputation: 1
try:
=ARRAYFORMULA(TRIM(FLATTEN(SPLIT(QUERY(
REPT(A1:A3&"♠", 5)&REPT(" ♠", 2),,999^99), "♠"))))
Upvotes: 2