Forum Discussion
Sequencing numbers
Harry435With Excel MS365 or 2021 that would be:
=TRANSPOSE(SEQUENCE(100,52,1,1))
or if you are an Office Insider, you could transform the formula provided by JMB17 into a LAMBDA function like this:
=MAKEARRAY(52,100,LAMBDA(r,c,r+(c-1)*52))
Both will spill an array of the dimension you specified with sequential numbers, starting at 1, going down 52 rows in the first column and continue on the first row of the second, and so on.
Hello Riny,
Thanks for taking the time to reply to my question.
The =TRANSPOSE(SEQUENCE...... formula suggested by yourself and @JMB17 partly solved the problem. However the start of the sequence in the first column is never likely to start in the first row, but all other columns will start in the first row with the numbers in their correct sequence.
The spreadsheet I am trying to develop is 100 columns wide and 52 rows deep. The numbers start from 1 through to 5200. The number 1 can start in any cell in column 1 (can be but rarely will it be the 1st cell) then numbered sequentially down to row 52, across the columns ending with 5200 in column 100.
As an example: Cell A47 is 1, A48 is 2, ... A52 is 6, then B1 will be 7 through to B52 will be 58. Then C1 will be 59 through to C52 which will be 110. D1 is 111 to D52 is 162, etc.
In column CW, CW1 is 5155 to CW46 is 5200.
I solved the problem by entering the numbers in columns A,B and C then dragged columns B and C combined across to column CW. All the cells then populated as I wanted them to do. I had to tidy the last column to end at 5200.
I don't know what the formula would be to do this but it worked as I have suggested.
Using this method, 1 can start in any cell in the first column and 5200 will end in the same cell 100 columns later.
I hope my explanation is easy to follow.
Regards.
Harry435