Formula to create a n x m array of sequential numbers?

[MEUserII]

I am looking for an Excel formula to create a n (row) x m (column) array of sequential numbers; would anyone know of such a formula? As an elaboration, consider the following examples below of the desired output for different array sizes of n x m where the output array desired via formula entry is in A1. While the desired output array is entered in A1; I have showed the values visually "spilling" in to nearby cells for illustration.
For reference, I am aware that the SEQUENCE() function can do this, but I am trying to find a formula not using the SEQUENCE() function.

System Information:
OS version: Windows 10 (64-bit)
Excel Version: Excel 2016

Example Desired Formula Outputs:
n x m = 1 x 2
Formula Output in A1={1, 2}

R/C​	A​	B​
1​	1​	2​
2​

n x m = 2 x 1
Formula Output in A1={1; 2}

R/C​	A​	B​
1​	1​	​
2​	2​

n x m = 2 x 2
Formula Output in A1={1, 2; 3, 4}

R/C​	A​	B​
1​	1​	2​
2​	3​	4​

n x m = 2 x 3
Formula Output in A1={1, 2, 3; 4, 5, 6}

R/C​	A​	B​	C​
1​	1​	2​	3​
2​	4​	5​	6​
3​

n x m = 3 x 2
Formula Output in A1={1, 2; 3, 4; 5, 6}

R/C​	A​	B​	C​
1​	1​	2​
2​	3​	4​
3​	5​	6​

n x m = 3 x 3
Formula Output in A1={1, 2, 3; 4, 5, 6; 7, 8, 9}

R/C​	A​	B​	C​
1​	1​	2​	3​
2​	4​	5​	6​
3​	7​	8​	9​

For reference, there is a similar problem to this one of doing the reverse: taking a n x n array of values and outputting those values in to a n x 1 column; solved by Chip Pearson here: Table To Column .

 

[Eric W]

Maybe:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	R/C
2	3	1	2	3	4	#N/A	#N/A
3	4	5	6	7	8	#N/A	#N/A
4		9	10	11	12	#N/A	#N/A
5		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
Sheet12

Cell Formulas
Range	Formula
B2:G6	B2	=TRANSPOSE(ROW(INDIRECT("1:"&A3)))+(ROW(INDIRECT("1:"&A2))-1)*A3
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Another...

Pasta3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	n	m		Matrix
2	4	5		1	2	3	4	5
3				6	7	8	9	10
4				11	12	13	14	15
5				16	17	18	19	20
6
7
8
9
10
11
12
Plan1

Cell Formulas
Range	Formula
D2:M11	D2	=IF(OR(ROWS(D$2:D2)>$A$2,COLUMNS($D2:D2)>$B$2),"",$B$2*ROWS(D$2:D2)-($B$2-1)+COLUMNS($D2:D2)-1)

Insert the formula in D2; copy down and across, say, to the range D2:M11 (gray area)

Then, try changing the values in A2 and B2 (n x m)

Hope this helps

M.

 

[MEUserII]

Maybe:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	R/C
2	3	1	2	3	4	#N/A	#N/A
3	4	5	6	7	8	#N/A	#N/A
4		9	10	11	12	#N/A	#N/A
5		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
Sheet12

Cell Formulas
Range	Formula
B2:G6	B2	=TRANSPOSE(ROW(INDIRECT("1:"&A3)))+(ROW(INDIRECT("1:"&A2))-1)*A3
Press CTRL+SHIFT+ENTER to enter array formulas.

@Eric W, this is very clever, ; I didn't mean for the late reply as I was kept busy by some other committments.
I've rewritten your formula in to a more general version where the n x m array of sequential numbers can be represented by the respective n, m paramaters entered in to: $A$1, $B$1.

Formula entered in to $C$1: =(((ROW(INDIRECT((("$")&(1) )&(":")&(("$")&($A$1) ) ) ) )-(1) )*($A$1) )+(TRANSPOSE(ROW(INDIRECT((("$")&(1) )&(":")&(("$")&($B$1) ) ) ) ) )

Another...

Pasta3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	n	m		Matrix
2	4	5		1	2	3	4	5
3				6	7	8	9	10
4				11	12	13	14	15
5				16	17	18	19	20
6
7
8
9
10
11
12
Plan1

Cell Formulas
Range	Formula
D2:M11	D2	=IF(OR(ROWS(D$2:D2)>$A$2,COLUMNS($D2:D2)>$B$2),"",$B$2*ROWS(D$2:D2)-($B$2-1)+COLUMNS($D2:D2)-1)

Insert the formula in D2; copy down and across, say, to the range D2:M11 (gray area)

Then, try changing the values in A2 and B2 (n x m)

Hope this helps

M.

@Marcelo Branco, I definitely will take a deeper look at this formula, and see how it works; and I hope to update this thread afterwards.

 

[Eric W]

I'm not sure how your version is more general, since my version also read the parameters from A2 and A3? Also, in your version, you don't really need the $ sign inside an INDIRECT, and you have a lot of unnecessary parentheses. But if you understand it, and it works for you, that's the main thing.

 

[Rahul1987]

Maybe:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	R/C
2	3	1	2	3	4	#N/A	#N/A
3	4	5	6	7	8	#N/A	#N/A
4		9	10	11	12	#N/A	#N/A
5		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
Sheet12

Cell Formulas
Range	Formula
B2:G6	B2	=TRANSPOSE(ROW(INDIRECT("1:"&A3)))+(ROW(INDIRECT("1:"&A2))-1)*A3
Press CTRL+SHIFT+ENTER to enter array formulas.

Superb ?