Excel formula? 1

[MinnKota]

We have a spreadsheet that has a StartingNumber column and an EndingNumber column. Is there a formula that will return all of the numbers in between the two?

 

[SkipVought]

MinnKota,

A Formula returns ONE VALUE.

What do you mean by "...a StartingNumber column and an EndingNumber column"???

How many StartingNumber VALUES and EndingNumber VALUES do you have???

Please post an example?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

for instance the value in (d,3) is "1" and the value in (e,3) is "10" I want a fomula that makes the value of (j,3) to be "1,2,3,4,5,6,7,8,9,10" or "2,3,4,5,6,7,8,9

 

[SkipVought]

What, may I ask, is the purpose of such a TEXT VALUE?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[baddy]

You wan't the range of inbetween numbers to be in once cell like you had in quotes, or if there is a difference if 8 do you want 8 cells to have one number in each? I can't think of any way to do this but I'm thinking...

 

[mscallisto]

Can you show an example spreadsheet with the input and results you want.

Also it may help to know what you're trying to do.

 

[KenWright]

Assuming your starting Col is Col A, Ending Col is col B, and target col is Col C, headers in Row 1 and data below that:-

Sub Concat()

Dim LastRw As Long
Dim Rng As Range
Dim str As String
Dim cnt As Long
Dim x As Long
Dim y As Long
Dim z As Long

LastRw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(LastRw, "A"))

For x = 2 To Rng.Rows.Count
str = "'"
With Cells(x, "A")
y = .Value
z = .Offset(0, 1).Value
End With

For cnt = 1 To (z - y)
str = str & (y + cnt - 1) & ","
Next cnt
str = str & z
Cells(x, "C") = str
Next x

End Sub

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

well, not to be outdone...

paste this function in a MODULE and use it just like a spreadsheet function...

Function NumString(rStart As Range, rEnd As Range)
   Dim i As Integer
   Const COMMA = ","
   For i = rStart To rEnd
      NumString = NumString & i & COMMA
   Next
   NumString = Left(NumString, Len(NumString) - 1)
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[AlaskanDad]

Here's a shot at it for you. I know you specified D3, E3, etc. but bear with me. You can make a modification for your use.

Column A: Start Number
Column B: End Number
Column C: Result

A3: 2
B3: 10
C3: =D3&E3&F3 (&G3, etc.... results in: 3,4,...8,9)
D3: =A3+1 (I'll assume there's at least one number in between)
E3: =IF(($A3+COLUMN()-3)<($B3),IF(($A3+COLUMN()-3)>($A3+1),"," &($A3+COLUMN()-3),""),"")
F3: IF(($A3+COLUMN()-3)<($B3),IF(($A3+COLUMN()-3)>($A3+1),"," &($A3+COLUMN()-3),""),"")
G3: (etc. It's the exact same formula)

This large formula relies on the fact that the current column - 3 is how much you want to add to A3. For example, cell E3 is in the fifth column. Subtracting three from it and adding to A3 (2) gives us 4 which is the second value you wanted in your list.

If you move columns, then adjust the number "3" in the formula.

(not even gonna ask why you're needing this... )


-Rob

 

[AlaskanDad]

WOW!

Quite a few answers as I was pondering mine!

[]
-Rob

 

[KenWright]

LOL - Never outdone Skip, and my vote goes to your solution anyway

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[MinnKota]

Skip,

I like your solution also, but I am having trouble getting it to work. I either get #Value! or it just leaves the text like it was entered "=numstring(I4,J4)

 

[Zathras]

Nothing wrong with Skip's function. Did you put it in a MODULE as he specified?

This isn't the VBA forum, do you know enough VBA to handle this? If not, don't be bashful to admit it and we can walk you through the steps.

 

[MinnKota]

Zathras,

That is a tough question to answer...
I am pretty sure that I know enough VB to get this going. I have done a fair amount of VBA programming in Access, and I have used Modules with success, but I am not to "bashful" to ask for help as I have already done.

 

[SkipVought]

The CELL in which the formula resides ought to be formatted GENERAL, not TEXT.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip,

You hit the nail on the head! Turns out it wasn't a problem in your code or a problem with my VB skills, just a formatting issue.

Thanks!

 

[MinnKota]

Having used the Function with success, I now have a new problem. (sorry to bother you so much Skip) I have some numbers that are preceeded by zeros i.e. "00002345" and the function (I think the For loop) changes them to "2345" but I need them in their whole form.

 

[SkipVought]

Minn,

A number is a number.

01 EQUALS 1.

the NUMBER 1 is LOADS DIFFERENT than the STRING "01"

The NUMEBER 1 has a BINARY value of 1

The STRING "01" has a binary value of 110000 110001

That was FYI...

Just format the cell CUSTOM 00000000

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MinnKota]

Skip,

I have changed the code to add a prefix to the numbers.


Function NumString(rStart As Range, rEnd As Range, PreSerial As String)
Dim i As Long
Const COMMA = ","
For i = rStart To rEnd
NumString = NumString & preSerial & i & COMMA
Next
NumString = Left(NumString, Len(NumString) - 1)
End Function

I think this does not allow me to use this customization of format.

 

[SkipVought]

FORMAT 0000000 only works for NUMBERS. You don't have NUMBERS you have TEXT with NUMERIC CHARACTERS.

What is the PURPOSE of this?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884