Array 1-2,3-4,5-6...etc

[ZachExcel]

Hello!

So, I’m really bad at excel. I’m sure there are answers here on arrays, but I really am unsure how to edit the formula to match my question. I would like an array to write “1-2,3-4,5-6...” or with spaces between comma and next number is fine too. I would want to be able to end the array (2 numbers at a time) to any number. For instance, 999-1000.

I would really appreciate any expert advice! I’m really bad with formulas.

Thank you for your time.

?

 

[ZachExcel]

No worries. I noticed! It was just in the parenthesis part, so I got it going. Thank you!

 

[Peter_SSs]

... I got it going.

Are you sure?
I'm questioning that because of ..
a) Your initial example of ranges of 2 ending with 999-1000. Rick's latest function result for that ends with 997-998 not 999-1000. That is a similar situation in rows 1 & 2 below where the last range is missing.
b) In rows 3 & 4 below, Rick's function returns an error whereas I think there are legitimate results as shown by my function in column B
c) Not sure what you would want for row 5. I have my function returning an error because the first argument of the function (14) is not a legitimate starting value for ranges starting at 1 in groups of 3.

My UDF code for your consideration:

Function Ranges(LastNums As String, Sze As Long) As String
  Dim fn As Long, ln As Long
  
  fn = Split(LastNums, "-")(0)
  ln = Split(LastNums & -fn - Sze + 1, "-")(1)
  If fn Mod Sze <> 1 Or ln <> fn + Sze - 1 Then
    Ranges = "Error"
  Else
    Ranges = Mid(Join(Filter(Application.Transpose(Evaluate(Replace(Replace("if(mod(row(1:#),%)=1,"", ""&row(1:#),if(mod(row(1:#),%)=0,-row(1:#),""x""))", "#", ln), "%", Sze))), "x", False), ""), 3)
  End If
End Function

Excel Workbook
	B	C
1	1-2, 3-4, 5-6, 7-8, 9-10, 11-12, 13-14, 15-16, 17-18, 19-20, 21-22, 23-24	1-2,3-4,5-6,7-8,9-10,11-12,13-14,15-16,17-18,19-20,21-22
2	1-2, 3-4, 5-6, 7-8, 9-10, 11-12, 13-14, 15-16, 17-18, 19-20, 21-22, 23-24	1-2,3-4,5-6,7-8,9-10,11-12,13-14,15-16,17-18,19-20,21-22
3	1-3, 4-6, 7-9, 10-12, 13-15, 16-18	#VALUE!
4	1-3, 4-6, 7-9, 10-12, 13-15	#VALUE!
5	Error	1-3,4-6,7-9,10-12
6	Error	#VALUE!
Sheet5

 

[Rick Rothstein]

Are you sure?
I'm questioning that because of ..
a) Your initial example of ranges of 2 ending with 999-1000. Rick's latest function result for that ends with 997-998 not 999-1000. That is a similar situation in rows 1 & 2 below where the last range is missing.
b) In rows 3 & 4 below, Rick's function returns an error whereas I think there are legitimate results as shown by my function in column B
c) Not sure what you would want for row 5. I have my function returning an error because the first argument of the function (14) is not a legitimate starting value for ranges starting at 1 in groups of 3.

I believe the following modified code now works correctly...

[table="width: 500"]
[tr]
	[td]Function IndexBy(LastNumbers As String, Count As Long) As String
  IndexBy = Join(Evaluate(Replace(Replace("TRANSPOSE(#*(ROW(A1:A@))-#+1)&""-""&TRANSPOSE(#*(ROW(A1:A@)))", "#", Count), "@", Int((Split(LastNumbers, "-")(0) + Count - 1) / Count))), ",")
End Function[/td]
[/tr]
[/table]

My UDF code for your consideration:

Function Ranges(LastNums As String, Sze As Long) As String
  Dim fn As Long, ln As Long
  
  fn = Split(LastNums, "-")(0)
  ln = Split(LastNums & -fn - Sze + 1, "-")(1)
  If fn Mod Sze <> 1 Or ln <> fn + Sze - 1 Then
    Ranges = "Error"
  Else
    Ranges = Mid(Join(Filter(Application.Transpose(Evaluate(Replace(Replace("if(mod(row(1:#),%)=1,"", ""&row(1:#),if(mod(row(1:#),%)=0,-row(1:#),""x""))", "#", ln), "%", Sze))), "x", False), ""), 3)
  End If
End Function

I note that my code seems to handle a larger range of values than your code. For example, put this formula in a cell...

=IndexBy(999,5)

and turn Wrap Text on so you can see the full output... all ranges up to and including 996-1000 print out whereas your function outputs "Error". If you remove the If..Then error trap, your code outputs a value, but it end incorrectly with 996-1000,1001. I tried to decipher it, but was not sure about it... what exactly is your If..Then error trap protecting against?

 

[Peter_SSs]

For example, put this formula in a cell...

=IndexBy(999,5)

999 is not a valid "LastNums" starting value for groups of 5 starting at 1. For that case I deliberately returned an error to alert the user to that fact. Similar to if you put an invalid parameter in a function like
=SUM("a", 5), or
=SQRT(-8)

Whether the OP wants that or not I don't know.

I note that my code seems to handle a larger range of values than your code.

Given my point above, if you use a valid starting number of 996 then my function returns the full correct string.

As they are written your function does handle bigger ranges - but only because (I believe) of the different delimiters used. You use a comma as the delimiter whereas I have used comma-space (the OP suggested either). If our functions used the same delimiter then I believe they handle equal ranges. For example, if both use comma-space then our functions return the full string for
=functionname(6157,2)
but error for
=functionname(6159,2)
.. running into the cell character limit.

 

[Rick Rothstein]

999 is not a valid "LastNums" starting value for groups of 5 starting at 1. For that case I deliberately returned an error to alert the user to that fact. Similar to if you put an invalid parameter in a function like
=SUM("a", 5), or
=SQRT(-8)

Whether the OP wants that or not I don't know.

Okay, I see what you did now. I deliberately chose to allow the user to specify any value in the final range as the "last number" figuring he/she might know there are, say, 1000 pages to split into ranges spanning, say, 7 pages per range but might not know the exact starting value of the last range and might not want to try and calculate it.

Given my point above, if you use a valid starting number of 996 then my function returns the full correct string.

As they are written your function does handle bigger ranges - but only because (I believe) of the different delimiters used.

I never noticed that you used a comma space delimiter.