Sequencing numbers

[Harry435]

How do you sequence numbers in cells from top to bottom then left to right?

1        6        11

2        7        12

3        8        13

4        9        14

5       10       15      etc.

 for 52 rows deep and 100 columns wide.

Your help/advice would be appreciated.

Harry435

Thanks.

[#2]

@Harry435 HI HARRY,
to make an array in a specific sequence, you need sequence function within excel

=SEQUENCE(rows,column,start,step)

in rows enter user defined rows

in column enter user defined columns

start- which number you want to start with eg started with 5

step- what difference the step should be , eg 2, which means add 2 to 5 through out array.

result

array work from left to right

[#3]

If you have office 365, I think you could try:
=transpose(sequence(100,52))

Otherwise, I believe you could use this (assume the formula is entered in C5 - change cell reference as needed, then copy across/down):

=ROWS(C$5:C5)+(COLUMNS($C5:C5)-1)*52

[#4]

@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.

[#5]

@Riny_van_Eekelen 

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

[#6]

My initial example was wrong as 1 can start in any cell in the first column. See in my detailed reply. Thanks.

[#7]

@Harry435 Well. That's cleared then, but for the record you'll end up with 101 columns. Not 100.

[#8]

Thanks Riny. Yes 101 columns. Column 1 starts at 0.