Variable Sequences

[test3xc31]

I am trying to make a formula where I can generate the following variable sequence depending on the value in A1.

If A1 = 2 the formula would cycle between A1,A2,B1,B2,A1,A2,B1,B2, and so on, but if the A1 = 5 it would cycle between A1,A2,A3,A4,A5,B1,B2,B3,B4,B5,C1,C2,C3,C4,C5,D1,D2,D3,D4,D5,E1,E2,E3,E4,E5,A1,A2,A3,A4,A5, before returning to A and repeating

Any pointers would be appreceated!

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2
3	A1	A2	A3	B1	B2	B3	C1	C2	C3	A1	A2	A3	B1	B2	B1	C1	C2	C3	A1	A2
4	A1	A2	A3	A4	B1	B2	B3	B4	C1	C2	C3	C4	D1	D2	D3	D4	A1	A2	A3	A4

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also will the output always be 20 columns?

 

[test3xc31]

Very good point and done, thankyou for the suggestion! I would prefer an option that didn't have a limit on the output however right now if it can only work for 20 columns I'll take it! I've been trying to work this one out for nearly week now!

 

[Fluff]

Thanks for that.
How about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	2	A	A	B	B	A	A	B	B	A	A	B	B	A	A	B	B	A	A	B	B
3	3	A	A	A	B	B	B	C	C	C	A	A	A	B	B	B	C	C	C	A	A
4	4	A	A	A	A	B	B	B	B	C	C	C	C	D	D	D	D	A	A	A	A
Data

Cell Formulas
Range	Formula
B2:U4	B2	=CHAR(MOD(INT(SEQUENCE(,20,0)/A2),A2)+65)
Dynamic array formulas.

To adjust the number of columns just change the 20 inside the sequence function

 

[Fluff]

Just realised that you have changed your original request, so how about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2
3	3	A1	A2	A3	B1	B2	B3	C1	C2	C3	A1	A2	A3	B1	B2	B3	C1	C2	C3	A1	A2
4	4	A1	A2	A3	A4	B1	B2	B3	B4	C1	C2	C3	C4	D1	D2	D3	D4	A1	A2	A3	A4
Data

Cell Formulas
Range	Formula
B2:U4	B2	=CHAR(MOD(INT(SEQUENCE(,20,0)/A2),A2)+65)&MOD(SEQUENCE(,20,0),A2)+1
Dynamic array formulas.

 

[test3xc31]

Just realised that you have changed your original request, so how about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2	A1	A2	B1	B2
3	3	A1	A2	A3	B1	B2	B3	C1	C2	C3	A1	A2	A3	B1	B2	B3	C1	C2	C3	A1	A2
4	4	A1	A2	A3	A4	B1	B2	B3	B4	C1	C2	C3	C4	D1	D2	D3	D4	A1	A2	A3	A4
Data

Cell Formulas
Range	Formula
B2:U4	B2	=CHAR(MOD(INT(SEQUENCE(,20,0)/A2),A2)+65)&MOD(SEQUENCE(,20,0),A2)+1
Dynamic array formulas.

Honestly I cannot thank you enough! though I'm going to have to spend the next week trying to work out how on earth that works!

 

[Fluff]

You're welcome & thanks for the feedback.