SEQUENCE function in opposite order

[wigi]

Hello all,

The SEQUENCE function is new in Excel. It can be very useful, certainly when we use it as input in other functions. (MrExcel has a cheap e-book on the topic of the new dynamic array functions)

Now my question: SEQUENCE generates a sequence of numbers row by row, and on every row, column by column.
Does anyone know how to generate the series in the opposite direction: first all rows of the first column, then all rows of the second column, and so on.

Thanks a lot !

Wigi

 

[Peter_SSs]

Is this what you mean?
If not could you clarify with a specific example?

20 05 16.xlsm
	F	G	H	I	J
1	1	2	3
2	4	5	6
3	7	8	9
4	10	11	12
5	13	14	15
6
7
8	1	4	7	10	13
9	2	5	8	11	14
10	3	6	9	12	15
SEQ

Cell Formulas
Range	Formula
F1:H5	F1	=SEQUENCE(5,3)
F8:J10	F8	=TRANSPOSE(SEQUENCE(5,3))
Dynamic array formulas.

 

[wigi]

Thanks Peter, I forgot about Transpose !
Though an additional parameter might be useful too, I guess, since SORT can do that too.

 

[wigi]

Peter, I was confused, apologies. I thought about Transpose but the range should have the same dimensions. Please have a look at the attached image.

 

[shaowu459]

新建Microsoft Excel 工作表.xlsx
	A	B	C	D	E
1	500	501		500	510
2	502	503		501	511
3	504	505		502	512
4	506	507		503	513
5	508	509		504	514
6	510	511		505	515
7	512	513		506	516
8	514	515		507	517
9	516	517		508	518
10	518	519		509	519
Sheet1

Cell Formulas
Range	Formula
A1:B10	A1	=SEQUENCE(10,2,500)
D1:E10	D1	=SMALL(SEQUENCE(10,2,500),ROW(1:10)+{0,10})
Dynamic array formulas.

 

[CA_Punit]

@shaowu459
Thanks..
This was great

 

[Peter_SSs]

@wigi
I assume that the example in post #4 of what you wanted is a typo?
The original sequence is 500-519 but your transformation is 501-520


I would modify shaowu459's suggestion to that shown in columns G:H ...

20 05 16.xlsm
	A	B	C	D	E	F	G	H
1	500	501		500	510		500	510
2	502	503		501	511		501	511
3	504	505		502	512		502	512
4	506	507		503	513		503	513
5	508	509		504	514		504	514
6	510	511		505	515		505	515
7	512	513		506	516		506	516
8	514	515		507	517		507	517
9	516	517		508	518		508	518
10	518	519		509	519		509	519
SEQ (2)

Cell Formulas
Range	Formula
A1:B10	A1	=SEQUENCE(10,2,500)
D1:E10	D1	=SMALL(SEQUENCE(10,2,500),ROW(1:10)+{0,10})
G1:H10	G1	=SMALL(SEQUENCE(10,2,500),SEQUENCE(10)+{0,10})
Dynamic array formulas.

.. the reason being that it is then doesn't fail if rows are inserted above

20 05 16.xlsm
	A	B	C	D	E	F	G	H
1
2
3	500	501		502	512		500	510
4	502	503		503	513		501	511
5	504	505		504	514		502	512
6	506	507		505	515		503	513
7	508	509		506	516		504	514
8	510	511		507	517		505	515
9	512	513		508	518		506	516
10	514	515		509	519		507	517
11	516	517		510	#NUM!		508	518
12	518	519		511	#NUM!		509	519
SEQ (2)

 

[wigi]

Thank you all. Yes there was a typo, my bad.

I hope that Microsoft adds a 5th, optional, argument such that we can do it in a more elegant way with only SEQUENCE. Your formula solutions are nice though !

 

[Peter_SSs]

You're welcome.

 

[wigi]

I replaced {0,10} with a 3rd SEQUENCE function, since then I can make the number 10 dynamic too.
=SEQUENCE( 1, 2, 0, 10 ) and 10 becomes a cell reference.
In my application that number is based on other parameters.