'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 1:^[1]

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

Where:

Range is the range of text to repeat
Reps is the number of repititions

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

Solution 2:^[2]

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.

A1	Name
A2	Dog
A3	Cat
A4	Ball

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

Solution 3:^[3]

here is another trick.

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	John AZ1
Solution 2
Solution 3	buddemat

'Repeat each row N times in Google Sheets

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]