CODEWITHSUNDEEP
excelvbaformula
Michael Huber
Michael Huber

Reputation: 13

Return list of integers between two cells

I am hoping for a formula or vba function to add a list of values to a cell, given an upper and lower bound. For example, refer to columns A and B in the image below:

Example

I would like to generate a formula or function that returns a list of both the upper and lower bounds, as well as, all the values in between. See column C in the example above for the desired output.

The issue is the the range between the two cells vary (the output in column C will not always contain 4 integers - it could be more or less).

Does anyone know what the best way to accomplish this task would be? I really appreciate any and all help.

Upvotes: 1

Views: 2119

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

If you are on Office365 or Excel 2016 and higher, you can use the TextJoin function like so (entered as an array formula with Ctrl+Shift+Enter):

=TEXTJOIN(",",TRUE,ROW(INDEX(A:A,A2):INDEX(A:A,B2)))

Otherwise you'll need to use VBA to create a UDF as shown in Gary's Student's answer.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96773

With VBA:

Public Function Seq(n1 As Long, n2 As Long) As String
    For i = n1 To n2
        Seq = Seq & "," & i
    Next i
    Seq = Mid(Seq, 2)
End Function

enter image description here

EDIT#1:

In the most recent version of Excel, this array formula:

=TEXTJOIN(",",TRUE,ROW(1:5))

will return a sequence of values:

enter image description here

Perhaps a really clever person can tell us how to replace the 1:5 with cell references.

Upvotes: 1

Related Questions