Formula for generating delimited list of incremented numbers

Question

Looking for a formula that takes a min and max value, then generates a delimited list of numbers including and between those values.

For example:

min = 2015

max = 2017

result = 2015,2016,2017

Not sure what to call it/how to word it, everything I've tried gives me results unrelated.

score 2 · Answer 1 · answered Feb 18 '22 at 21:18

2

After help from @Mayukh Bhattacharya, I was able to accomplish my desired output by using his suggested macro for SEQUENCE alone.

Function SEQUENCE(min As Integer, max As Integer, steps As Integer) As String
  Dim i As Integer
  For i = min To max Step steps
    SEQUENCE = SEQUENCE & i & ","
  Next i
  SEQUENCE = Left(SEQUENCE, Len(SEQUENCE) - 1)
End Function

Written as =SEQUENCE(1987,1990,1) gave me 1987,1988,1989,1990

answered Feb 18 '22 at 21:18

Casey

55
7

Wow! Good One. Thank You for the share! – Mayukh Bhattacharya Feb 18 '22 at 21:21

score 1 · Accepted Answer · answered Feb 18 '22 at 18:48

1

You may try

=TEXTJOIN(",",,SEQUENCE(1,3,2015,1))

answered Feb 18 '22 at 18:48

Mayukh Bhattacharya

12,541
5
21
32

1

Any chance I can do it without `TEXTJOIN`? It Isn't available in my version of Excel :( – Casey Feb 18 '22 at 18:50
@Casey what is your `Excel` Version? – Mayukh Bhattacharya Feb 18 '22 at 18:50
1

Office Professional Plus 2016 – Casey Feb 18 '22 at 18:53
Alright, one moment! – Mayukh Bhattacharya Feb 18 '22 at 18:53
1

Another caveat is the sequence may not always be 3 steps, it can be 1-many. – Casey Feb 18 '22 at 18:57
Aha okay, you need VBA then,! – Mayukh Bhattacharya Feb 18 '22 at 18:59
@Casey are you using any cell references ? – Mayukh Bhattacharya Feb 18 '22 at 19:00
1

I got yours working in Google sheets with the caveat by doing `=TEXTJOIN(",",,SEQUENCE(1,MINUS(max,min)+1,min,1))` – Casey Feb 18 '22 at 19:00
@Casey so is it resolved now then? – Mayukh Bhattacharya Feb 18 '22 at 19:01
1

Yes, the `max` and `min` above are cell references. Now to find the VBA code to make my own `TEXTJOIN` and `SEQUENCE` for Excel! Thank you for your help! – Casey Feb 18 '22 at 19:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/242161/discussion-between-mayukh-bhattacharya-and-casey). – Mayukh Bhattacharya Feb 18 '22 at 19:05
1

@Casey you can refer this link on `TEXTJOIN` using VBA -- https://stackoverflow.com/questions/42165179/ms-excel-concat-with-a-delimiter/42165195#42165195 – Mayukh Bhattacharya Feb 18 '22 at 19:07
@Casey refer the answer by Scott Craner! – Mayukh Bhattacharya Feb 18 '22 at 19:09
1

@Casey For `SEQUENCE` https://stackoverflow.com/questions/40168311/generate-equidistant-sequence-of-number-in-one-cell-using-vba – Mayukh Bhattacharya Feb 18 '22 at 19:15

Formula for generating delimited list of incremented numbers

2 Answers2