Generate Repeated Sequences

[Cubist]

Hi,

I'm wondering if this is possible with formula. Generating an alphabetical sequence of length m, and repetition of each element n times. m is assumed to be divisible by n. For example, m=10, n = 2.

Book1
	A	B	C
1	Length	10
2	Rep of each element	2
3
4	A
5	A
6	B
7	B
8	C
9	C
10	D
11	D
12	E
13	E
14
Sheet3

 

[shaowu459]

there are many ways could do that, below is one of them.

工作簿1
	A	B	C
1	Length	10
2	Rep of each element	2
3
4		A
5		A
6		B
7		B
8		C
9		C
10		D
11		D
12		E
13		E
14		F
15		F
16		G
17		G
18		H
19		H
20		I
21		I
22		J
23		J
24
Sheet1

Cell Formulas
Range	Formula
B4:B23	B4	=TOCOL(TAKE(CHAR(ROW(65:90)),B1)&T(SEQUENCE(,B2)))
Dynamic array formulas.

 

[Cubist]

I just needed minor adjustment to get the length of 10 rather than 10 alphabetical. I've never seen T function before. This is neat.

Book1
	A	B	C	D
1	Length	10
2	Rep of each element	2
3
4		A
5		A
6		B
7		B
8		C
9		C
10		D
11		D
12		E
13		E
14
Sheet4

Cell Formulas
Range	Formula
B4:B13	B4	=TOCOL(TAKE(CHAR(ROW(65:90)),B1/B2)&T(SEQUENCE(,B2)))
Dynamic array formulas.

 

[shaowu459]

oh sorry, i didn't understand your description well. now i am fully understand your requirement, the formula could be:

工作簿1
	A	B	C
1	Length	12
2	Rep of each element	2
3
4		A
5		A
6		B
7		B
8		C
9		C
10		D
11		D
12		E
13		E
14		F
15		F
16
Sheet1

Cell Formulas
Range	Formula
B4:B15	B4	=TAKE(TOCOL(CHAR(ROW(65:90)&T(SEQUENCE(,B2)))),B1)
Dynamic array formulas.

 

[Peter_SSs]

If I have understood correctly, I think this col B formula would not only be shorter, but also more robust.
I have assumed though that the alphabetical sequence does not go past "Z". If that is possible please clarify.

24 04 03.xlsm
	A	B	C	D
1	Length	12
2	Rep of each element	2
3
4		A		A
5		A		A
6		B		B
7		B		B
8		C		C
9		C		C
10		D		D
11		D		D
12		E		E
13		E		E
14		F		F
15		F		F
16
Repeat Seq

Cell Formulas
Range	Formula
B4:B15	B4	=CHAR(SEQUENCE(B1,,65,1/B2))
D4:D15	D4	=TAKE(TOCOL(CHAR(ROW(65:90)&T(SEQUENCE(,B2)))),B1)
Dynamic array formulas.

The reason I mention robustness is look what happens if a new row 1 is subsequently inserted in the worksheet.

24 04 03.xlsm
	A	B	C	D
1
2	Length	12
3	Rep of each element	2
4
5		A		B
6		A		B
7		B		C
8		B		C
9		C		D
10		C		D
11		D		E
12		D		E
13		E		F
14		E		F
15		F		G
16		F		G
17
Repeat Seq

Cell Formulas
Range	Formula
B5:B16	B5	=CHAR(SEQUENCE(B2,,65,1/B3))
D5:D16	D5	=TAKE(TOCOL(CHAR(ROW(66:91)&T(SEQUENCE(,B3)))),B2)
Dynamic array formulas.

 

[Cubist]

Hi Peter,
It's alpha only. Using decimals is also neat. I didn't know char takes decimals like that.

 

[Peter_SSs]

Cheers. Glad to contribute.