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.

?

 

[Rick Rothstein]

Here is a UDF (user defined function) that should do what you want. To use it, simply pass it the last value you want it to print (for you posted question, that would be 999-1000). Note... you can just pass the first number of the pair (999) by itself if you wish. Also note that the first number of the pass value must be an odd number (the function does not check for this)...

[table="width: 500"]
[tr]
	[td]Function IndexByTwos(LastNumbers As String) As String
  IndexByTwos = Join(Evaluate("TRANSPOSE(2*ROW(A1:A" & (Split(LastNumbers, "-")(0) + 1) / 2 & ")-1&""-""&2*ROW(A1:A" & (Split(LastNumbers, "-")(0) + 1) / 2 & "))"), ",")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IndexByTwos just like it was a built-in Excel function. For example,

=IndexByTwos("999-1000")

or

=IndexByTwos(999)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ZachExcel]

Hello. Thank you for this. I tried installing the UDF on Mac Excel 2011. I simply entered it into the editor for VBA. When I enter it, it shows "=IndexByTwos" correctly, but if I do something like "=IndexByTwos(999)" without the quotes obviously, it comes up "NAME?". Do you know what I'm doing wrong? I really appreciate your help.

 

[Rick Rothstein]

Hello. Thank you for this. I tried installing the UDF on Mac Excel 2011. I simply entered it into the editor for VBA. When I enter it, it shows "=IndexByTwos" correctly, but if I do something like "=IndexByTwos(999)" without the quotes obviously, it comes up "NAME?". Do you know what I'm doing wrong? I really appreciate your help.

I do have a Mac so I know nothing about them. The code I posted works fine on my PC. I wonder, though, about that #NAME ? error... it almost sounds like you installed the code in the wrong code module. UDF's need to be installed in a general module (the same place macros are installed at).

 

[ZachExcel]

My bad! It worked. I put the script in Visual Basic Editor. I thought that was the spot on the Mac...but when I put it in the Macro area it worked immediately. Thank you so much!!!!

 

[ZachExcel]

So...I have a question. What parts need to be edited if I wanted to alter this for numerous reasons? For example, say I wanted 1-3,4-6,etc.

Sub test()
Function IndexByTwos(LastNumbers As String) As String
IndexByTwos = Join(Evaluate("TRANSPOSE(2*ROW(A1:A" & (Split(LastNumbers, "-")(0) + 1) / 2 & ")-1&""-""&2*ROW(A1:A" & (Split(LastNumbers, "-")(0) + 1) / 2 & "))"), ",")
End Function

I tried playing around with it, for example I know the center "-1" changes the last number displayed and the next number displayed...the 3*ROW does really weird stuff. I can't seem to figure it out. I appreciate your help.

 

[Rick Rothstein]

So...I have a question. What parts need to be edited if I wanted to alter this for numerous reasons? For example, say I wanted 1-3,4-6,etc.

Here is my UDF modified to take a second argument... the number of values per group. So, for your original question, you would use this formula (note the name change for the UDF)...

=IndexBy(23,2)

For your question in Message #6 , you would use this formula (assuming the end group contains 23)...

=IndexBy(23,3)

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

 

[ZachExcel]

Wow, that is so cool. Thank you SO much for doing this for me. It really helps when I have a huge PDF and need to split PDF or do print ranges. Versus typing these ranges, the macro automatically does it. Thank you so much again...you are awesome!

 

[Peter_SSs]

(note the name change for the UDF)...

=IndexBy(23,2)

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

Rick, did you post the wrong code?

 

[Rick Rothstein]

Rick, did you post the wrong code?

Looks like I did... thanks for noting it.

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