Number Sequence

[Scott Huish]

I know I could write a VBA macro for this, but I'm wondering if there was an easy way with formulas so I don't have to get my coworkers to enable macros.

So, I want to be able to enter a number in A1, and then have it generate the sequence as below. There will always be 4 columns of results. The number of rows will be the number in A1/4. The pattern starts at 1 in the 2nd column, goes up in the 3rd column, back down the 4th column, and then up the first column.

I gave a couple of samples to show how this goes.

16​		16​	1​	8​	9​
		15​	2​	7​	10​
		14​	3​	6​	11​
		13​	4​	5​	12​
20​		20​	1​	10​	11​
		19​	2​	9​	12​
		18​	3​	8​	13​
		17​	4​	7​	14​
		16​	5​	6​	15​

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	Number
2	20		20	1	10	11
3			19	2	9	12
4			18	3	8	13
5			17	4	7	14
6			16	5	6	15
Sheet5

Cell Formulas
Range	Formula
C2:F6	C2	=LET(r,A2/4,CHOOSE({1,2,3,4},SEQUENCE(r,,A2,-1),SEQUENCE(r),SEQUENCE(r,,A2/2,-1),SEQUENCE(r,,A2/2+1)))
Dynamic array formulas.

 

[Scott Huish]

Awesome!! Thank you!

 

[Fluff]

Bit late but another option

=LET(a,A1/4,CHOOSECOLS(TRANSPOSE(SEQUENCE(4,a)+ISEVEN(SEQUENCE(4))*SEQUENCE(,a,a-1,-2)),4,1,2,3))

 

[Scott Huish]

Bit late but another option

=LET(a,A1/4,CHOOSECOLS(TRANSPOSE(SEQUENCE(4,a)+ISEVEN(SEQUENCE(4))*SEQUENCE(,a,a-1,-2)),4,1,2,3))

Thanks, that works too.

 

[Fluff]

Glad we could help & thanks for the feedback.