repeat each item in a list n times, x times into rows ; in google sheets-CodePudding

So let's say I have the list

item1
item2
item3

I need a formula to make it look like this -

where each item in the list is repeated X times , Y times. So in this case each item is repeated 3 times 4 times - for a total of (3 * 3 * 4) rows

item1
item1
item1
item2
item2
item2
item3
item3
item3
item1
item1
item1
item2
item2
item2
item3
item3
item3
item1
item1
item1
item2
item2
item2
item3
item3
item3
item1
item1
item1
item2
item2
item2
item3
item3
item3

so far I have this

=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY(REPT(A1:A3&";", 3), ,999^99), ";")))

which repeats each item 5 times, once like

item1
item1
item1
item2
item2
item2
item3
item3
item3

CodePudding user response：

Try:

=ArrayFormula(FLATTEN(TRANSPOSE(SPLIT(REPT(FLATTEN(SPLIT(REPT(A1:A3 & ",",D2),",")) & ";",E2),";"))))

CodePudding user response：

try:

=INDEX(FLATTEN(TRIM(SPLIT(REPT(QUERY(
 FLATTEN(SPLIT(REPT(A1:A3&"♦♥", D1), "♥")),,9^9), D2), "♦"))))

CodePudding user response：

Along similar lines to the other answers so far:

=INDEX(SORT(SPLIT(FLATTEN(INT(SEQUENCE(1,D1*D2,0)/D1)*COUNTA(A1:A5) SEQUENCE(COUNTA(A1:A5))&"|"&A1:A5),"|")),,2)