Repeat each row N times in Google Sheets

Question

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

Example

Desired Output

Here's what I have tried:

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

My attempt produces:

TheMaster · Answer 1 · 2021-12-16T14:16:18.553

11

Without splitting and joining and other string manipulations,

=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(ROWS(A2:A4),5),A2:A4)))

A2:A4 The range to repeat
5 Number of times to repeat
SEQUENCE to create a 2D array of numbers
IF to transpose a 1D array A2:A4 to a 2D array of size equal to the SEQUENCE array created
FLATTEN the 2D to 1D array.

edited Dec 16 '21 at 14:16

answered Dec 16 '21 at 14:10

TheMaster

2

This is the best solution. Obviously it has been overlooked by whoever else voted on the rest. – gciriani Aug 19 '22 at 21:17
1

This simpler solution is easier to explain than other more cryptic solutions – abd3721 Dec 16 '22 at 02:39

player0 · Accepted Answer · 2019-04-02T18:39:18.870

8

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

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

edited Apr 02 '19 at 18:39

answered Apr 02 '19 at 18:30

player0

1

Did what you posted and it worked. Then, I narrowed it down to just `SORT()` and it still seemed to work great! Thanks for pointing me in the right direction – Wes Foster Apr 02 '19 at 18:39
2

@WesFoster note that JOIN is limited to 50000 characters – player0 Apr 02 '19 at 18:42

score 1 · Answer 3 · answered Oct 30 '20 at 00:31

1

=transpose(split(join("~",ArrayFormula(rept({Balls}&"~",3))),"~"))

Where:

Note if your text contains a ~ change all instances of "~" to another character

answered Oct 30 '20 at 00:31

John AZ1

score 0 · Answer 4 · edited Dec 21 '21 at 13:59

0

here is another trick.

A1:A3 are dog, cat, ball repeated 4 times in my example.

=ARRAYFORMULA( FLATTEN(A1:A3 & SPLIT(REPT(" |",4),"|")))

edited Dec 21 '21 at 13:59

buddemat

answered Dec 16 '21 at 10:52

lejanus12

4 Answers4