Sequence function in old verion of excel

[nmounir]

I am trying to write a formula where I can get numbers from 1 to 20 in 5 columns and 4 rows
So it should be like this
1,2,3,4,5;6,7,8,9,10;11,12,13,14,15;16,17,18,19,20
I have put the semi column above to show when the numbers goes from 5th column to the next row.
This can easily be done with the sequence function but not sure how it can be done with excel 2016. On cell A1 I tried index(row(1:20) ,row(indirect("1:4")&column(A1:E1) but it doesn't work. Any idea ?

 

[RoryA]

You could use:

((ROW(INDIRECT("A1:E4"))-1)*5+COLUMN(INDIRECT("A1:E4")))

 

[Rick Rothstein]

Put this in your starting cell and copy it across and down...

=ROW(A1)*5+COLUMN(A1)-5

 

[nmounir]

You could use:

((ROW(INDIRECT("A1:E4"))-1)*5+COLUMN(INDIRECT("A1:E4")))

This is a very clever way of doing it. Thank you so much for the help.

 

[nmounir]

Put this in your starting cell and copy it across and

It works perfectly. Greatly appreciate d

Put this in your starting cell and copy it across and down...

=ROW(A1)*5+COLUMN(A1)-5

 

[nmounir]

It works perfectly. Greatly appreciate d

Put this in your starting cell and copy it across and down...

=ROW(A1)*5+COLUMN(A1)-5

It works very good. Much appreciated

 

[Eric W]

For what it's worth, here's a UDF that emulates the SEQUENCE function:

Public Function Sequence1(Optional r = 1, Optional c = 1, Optional s = 1, Optional step = 1)
Dim op(), r1 As Long, c1 As Long

    ReDim op(1 To r, 1 To c)
    For r1 = 1 To r
        For c1 = 1 To c
            op(r1, c1) = s
            s = s + step
        Next c1
    Next r1
    Sequence1 = op
   
End Function

Dynamic functions.xlsm
	A	B	C	D	E	F
1	1	2	3	4	5
2	6	7	8	9	10
3	11	12	13	14	15
4	16	17	18	19	20
5
Sheet5

Cell Formulas
Range	Formula
A1:E4	A1	=sequence1(4,5)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[nmounir]

For what it's worth, here's a UDF that emulates the SEQUENCE function:

Public Function Sequence1(Optional r = 1, Optional c = 1, Optional s = 1, Optional step = 1)
Dim op(), r1 As Long, c1 As Long

    ReDim op(1 To r, 1 To c)
    For r1 = 1 To r
        For c1 = 1 To c
            op(r1, c1) = s
            s = s + step
        Next c1
    Next r1
    Sequence1 = op
  
End Function

Dynamic functions.xlsm
	A	B	C	D	E	F
1	1	2	3	4	5
2	6	7	8	9	10
3	11	12	13	14	15
4	16	17	18	19	20
5
Sheet5

Cell Formulas
Range	Formula
A1:E4	A1	=sequence1(4,5)
Press CTRL+SHIFT+ENTER to enter array formulas.

This is brilliant thank you very much Eric

For what it's worth, here's a UDF that emulates the SEQUENCE function:

Public Function Sequence1(Optional r = 1, Optional c = 1, Optional s = 1, Optional step = 1)
Dim op(), r1 As Long, c1 As Long

    ReDim op(1 To r, 1 To c)
    For r1 = 1 To r
        For c1 = 1 To c
            op(r1, c1) = s
            s = s + step
        Next c1
    Next r1
    Sequence1 = op
  
End Function

Dynamic functions.xlsm
	A	B	C	D	E	F
1	1	2	3	4	5
2	6	7	8	9	10
3	11	12	13	14	15
4	16	17	18	19	20
5
Sheet5

Cell Formulas
Range	Formula
A1:E4	A1	=sequence1(4,5)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

For what it's worth, here's a UDF that emulates the SEQUENCE function:

Since, for your function, you have to select the range you want to fill in before using CTRL+SHIFT+ENTER to commit the array formula, there does not appear to be a need to have the user specify how many rows and columns to calculate as the function can do that on its own. Here is a function (I called it Sequencer) based on your function that only takes two arguments both of which are optional... the start number for the sequence and the step to apply to each value.

Function Sequencer(Optional ByVal Start As Long = 1, _
                   Optional ByVal Step As Long = 1)
  Dim R As Long, C As Long, Arr As Variant
  With Application.Caller
    ReDim Arr(1 To .Rows.Count, 1 To .Columns.Count)
    For R = 1 To .Rows.Count
      For C = 1 To .Columns.Count
        Arr(R, C) = Start
        Start = Start + Step
      Next
    Next
  End With
  Sequencer = Arr
End Function

 

[Eric W]

Here is a function (I called it Sequencer) based on your function that only takes two arguments both of which are optional... the start number for the sequence and the step to apply to each value.

Clever, Rick. I hadn't thought of that. I wrote UDF's for all the new Dynamic Array functions before I had access to them for real. So I tried to make them conform as closely as possible to the published descriptions. But of course there's no need to stick to that if it's not necessary.