MEUserII
Board Regular
- Joined
- Oct 27, 2017
- Messages
- 91
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
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}
n x m = 2 x 1
Formula Output in A1={1; 2}
n x m = 2 x 2
Formula Output in A1={1, 2; 3, 4}
n x m = 2 x 3
Formula Output in A1={1, 2, 3; 4, 5, 6}
n x m = 3 x 2
Formula Output in A1={1, 2; 3, 4; 5, 6}
n x m = 3 x 3
Formula Output in A1={1, 2, 3; 4, 5, 6; 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 .
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 .