Repeat Range of Cells N times with each item in range repeating X times

[deLockloire]

Hi all,

I'd like to have a formula that takes a range of cells, let say (A1:A3):

	A
1	Value1
2	Value2
3	Value3

and repeats them in a way that each item in that range gets repeated X times one after the other and the whole result gets repeated N times. For instance, if X=2 and N=3, this is how the result would look like (B1:B18):

	B
1	Value1
2	Value1
3	Value2
4	Value2
5	Value3
6	Value3
7	Value1
8	Value1
9	Value2
10	Value2
11	Value3
12	Value3
13	Value1
14	Value1
15	Value2
16	Value2
17	Value3
18	Value3

I kept tinkering with TOCOL, IF and SEQUENCE, but could not make it work in the way I wanted. Any ideas how I could do this?

Regards,
deL

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1		2	3
2	Value 1	Value 1
3	Value 2	Value 1
4	Value 3	Value 2
5		Value 2
6		Value 3
7		Value 3
8		Value 1
9		Value 1
10		Value 2
11		Value 2
12		Value 3
13		Value 3
14		Value 1
15		Value 1
16		Value 2
17		Value 2
18		Value 3
19		Value 3
Sheet6

Cell Formulas
Range	Formula
B2:B19	B2	=TOCOL(IF(SEQUENCE(,C1),TOCOL(IF(SEQUENCE(,B1),A2:A4))),,1)
Dynamic array formulas.

 

[deLockloire]

How about

Fluff.xlsm
	A	B	C
1		2	3
2	Value 1	Value 1
3	Value 2	Value 1
4	Value 3	Value 2
5		Value 2
6		Value 3
7		Value 3
8		Value 1
9		Value 1
10		Value 2
11		Value 2
12		Value 3
13		Value 3
14		Value 1
15		Value 1
16		Value 2
17		Value 2
18		Value 3
19		Value 3
Sheet6

Cell Formulas
Range	Formula
B2:B19	B2	=TOCOL(IF(SEQUENCE(,C1),TOCOL(IF(SEQUENCE(,B1),A2:A4))),,1)
Dynamic array formulas.

Thanks. I tried to do exactly that and now I also see where I got it wrong. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Cubist]

An option with INDEX.

=INDEX(A2:A4,MOD(SEQUENCE(ROWS(A2:A4)*B1*C1,,0)/B1,C1)+1)

 

[Cubist]

An option with INDEX.

=INDEX(A2:A4,MOD(SEQUENCE(ROWS(A2:A4)*B1*C1,,0)/B1,C1)+1)

Typo.

=INDEX(A2:A4,MOD(SEQUENCE(ROWS(A2:A4)*B1*C1,,0)/B1,ROWS(A2:A4))+1)