convert range of years to series
 

I have the following range of years in column A
02-08
91-99
92-96
01-03

What I need (in column B) is
02 03 04 05 06 07 08
91 92 93 94 95 96 97 98 99
92 93 94 95 96
01 02 03

Thanks for your help!

convert range of years to series
 

Sub expandyears()
Dim Outputstr As String
RowCount = 1
Do While Range("A" & RowCount) < ""
Years = Range("A" & RowCount)
FirstYear = Val(Trim(Years))
LastYear = Val(Trim(Mid(Years, InStr(Years, "-") + 1)))
Outputstr = ""
YearCount = FirstYear Mod 100
Do While YearCount < LastYear
If Outputstr = "" Then
Outputstr = Format(YearCount, "#00")
Else
Outputstr = Outputstr & Format(YearCount, " #00")
End If
YearCount = (YearCount + 1) Mod 100
Loop
Range("B" & RowCount).NumberFormat = "@"
Range("B" & RowCount) = Outputstr
RowCount = RowCount + 1
Loop

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145620

convert range of years to series
 

Sub test3()
Dim i As Long
Dim first As Long, last As Long
dim s as String
Dim cel As Range, rng As Range

Set rng = Range("A1:A4")

For Each cel In rng
first = Val(Left$(cel.Value, 2))
last = Val(Right$(cel.Value, 2))
s = ""
For i = first To last
s = s & Right$("0" & i, 2)
If i < last Then
s = s & " "
End If
Next
cel.Offset(, 1) = s
Next

End Sub

I assum A1:A4 is already formatted as text to accept strings like 02-08 with
converting to a date or evalutaing the subtration.

Regards,
Peter T


"autoguy" wrote in message
...
I have the following range of years in column A
02-08
91-99
92-96
01-03

What I need (in column B) is
02 03 04 05 06 07 08
91 92 93 94 95 96 97 98 99
92 93 94 95 96
01 02 03

Thanks for your help!

convert range of years to series
 

try this UDF

Function evalua(inputv)
R1 = Left(inputv, 2)
R2 = Right(inputv, 2)
R3 = R2 - R1
For i = 0 To R3
REsult = REsult & " " & Format(R1 + i, "00")
Next i
evalua = REsult
End Function


On Oct 19, 7:19*pm, autoguy wrote:
I have the following range of years in column A
02-08
91-99
92-96
01-03

What I need (in column B) is
02 03 04 05 06 07 08
91 92 93 94 95 96 97 98 99
92 93 94 95 96
01 02 03

Thanks for your help!

convert range of years to series
 

What happens if the data is 98-02. See my solution


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145620

convert range of years to series
 

"joel" wrote in message

What happens if the data is 98-02.

Running my macro you'd immediately realise the input data was incorrect, ie
not a range of years in a single decade :-)

Regards,
Peter T

convert range of years to series
 

There is nothing wrong with years being 98-02. It is you code that
isn't very robust. the programmer should write code that will woprk in
any codition without giving an error.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145620

convert range of years to series
 

Hello,

Just for the fun of it: array-enter
=MultiCat(TEXT(ROW(INDIRECT(SUBSTITUTE(A1,"-",":"))),"00")," ")

MultiCat you can find he
http://sulprobil.com/html/concatenate.html

Regards,
Bernd

convert range of years to series
 

You may have missed the point but not to worry.

Regards,
Peter T

"joel" wrote in message
...

There is nothing wrong with years being 98-02. It is you code that
isn't very robust. the programmer should write code that will woprk in
any codition without giving an error.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=145620

convert range of years to series
 

Running Joel's code, this is what I get
96-99 96 97 98
89-91 89 90
91-96 91 92 93 94 95
91-96 91 92 93 94 95
87-92 87 88 89 90 91
03-07 03 04 05 06
85-95 85 86 87 88 89 90 91 92 93 94
02-06 02 03 04 05
99-00 99
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
92-94 92 93
85-91 85 86 87 88 89 90
04-05 04
99-03 99 00 01 02
95-98 95 96 97
83-94 83 84 85 86 87 88 89 90 91 92 93
95-05 95 96 97 98 99 00 01 02 03 04
83-94 83 84 85 86 87 88 89 90 91 92 93

It's cutting the last number out ... any idea how to rectify that?
THANKS!

convert range of years to series
 

While this isn't joel's version, here is a function I developed which will
do what you want...

Sub FillAcross()
Dim X As Long, Z As Long, LastRow As Long, S As String, V As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Cells(X, "A").Value Like "##-##" Then
V = Split(Cells(X, "A").Value, "-")
S = ""
For Z = V(0) To V(1) - 100 * (V(1) < V(0))
S = S & " " & Format(Format(Z, "00"), "!@@")
Next
Cells(X, "B") = Trim(S)
End If
Next
End Sub

--
Rick (MVP - Excel)


"autoguy" wrote in message
...
Running Joel's code, this is what I get
96-99 96 97 98
89-91 89 90
91-96 91 92 93 94 95
91-96 91 92 93 94 95
87-92 87 88 89 90 91
03-07 03 04 05 06
85-95 85 86 87 88 89 90 91 92 93 94
02-06 02 03 04 05
99-00 99
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
92-94 92 93
85-91 85 86 87 88 89 90
04-05 04
99-03 99 00 01 02
95-98 95 96 97
83-94 83 84 85 86 87 88 89 90 91 92 93
95-05 95 96 97 98 99 00 01 02 03 04
83-94 83 84 85 86 87 88 89 90 91 92 93

It's cutting the last number out ... any idea how to rectify that?
THANKS!

convert range of years to series
 

another one -

Sub test4()
Dim i As Long
Dim first As Long, last As Long
Dim cel As Range, rng As Range

Set rng = Range("A1:A26")

For Each cel In rng
first = Val(Left$(cel.Value, 2)) + 1900
If first < 1950 Then first = first + 100

last = Val(Right$(cel.Value, 2)) + 1900
If last < 1950 Then last = last + 100
s = ""
For i = first To last
s = s & Right$("0" & i, 2)
If i < last Then
s = s & " "
End If
Next
cel.Offset(, 1) = s
Next

End Sub

change the "1950" to suit, ie 50 = 1950 and 49 = 2049

Regards,
Peter T
"autoguy" wrote in message
...
Running Joel's code, this is what I get
96-99 96 97 98
89-91 89 90
91-96 91 92 93 94 95
91-96 91 92 93 94 95
87-92 87 88 89 90 91
03-07 03 04 05 06
85-95 85 86 87 88 89 90 91 92 93 94
02-06 02 03 04 05
99-00 99
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
92-94 92 93
85-91 85 86 87 88 89 90
04-05 04
99-03 99 00 01 02
95-98 95 96 97
83-94 83 84 85 86 87 88 89 90 91 92 93
95-05 95 96 97 98 99 00 01 02 03 04
83-94 83 84 85 86 87 88 89 90 91 92 93

It's cutting the last number out ... any idea how to rectify that?
THANKS!

convert range of years to series
 

Of course the code I posted is not a "function"... it is a "macro". By the
way, I didn't mention it, but the code will properly ignore cell values that
are not constructed as digit-digit-dash-digit-digit, so you can have text
and blank cells within the range and that will not "screw" things up any.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
While this isn't joel's version, here is a function I developed which will
do what you want...

Sub FillAcross()
Dim X As Long, Z As Long, LastRow As Long, S As String, V As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
If Cells(X, "A").Value Like "##-##" Then
V = Split(Cells(X, "A").Value, "-")
S = ""
For Z = V(0) To V(1) - 100 * (V(1) < V(0))
S = S & " " & Format(Format(Z, "00"), "!@@")
Next
Cells(X, "B") = Trim(S)
End If
Next
End Sub

--
Rick (MVP - Excel)


"autoguy" wrote in message
...
Running Joel's code, this is what I get
96-99 96 97 98
89-91 89 90
91-96 91 92 93 94 95
91-96 91 92 93 94 95
87-92 87 88 89 90 91
03-07 03 04 05 06
85-95 85 86 87 88 89 90 91 92 93 94
02-06 02 03 04 05
99-00 99
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
96-05 96 97 98 99 00 01 02 03 04
90-95 90 91 92 93 94
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
02-06 02 03 04 05
92-94 92 93
85-91 85 86 87 88 89 90
04-05 04
99-03 99 00 01 02
95-98 95 96 97
83-94 83 84 85 86 87 88 89 90 91 92 93
95-05 95 96 97 98 99 00 01 02 03 04
83-94 83 84 85 86 87 88 89 90 91 92 93

It's cutting the last number out ... any idea how to rectify that?
THANKS!

convert range of years to series
 

Rick: Worked perfectly! Thanks!

convert range of years to series
 

try this

Sub expandyears()
Dim Outputstr As String
RowCount = 1
Do While Range("A" & RowCount) < ""
Years = Range("A" & RowCount)
FirstYear = Val(Trim(Years))
LastYear = Val(Trim(Mid(Years, InStr(Years, "-") + 1)))
Outputstr = ""
YearCount = FirstYear Mod 100
Do While (1)
If Outputstr = "" Then
Outputstr = Format(YearCount, "#00")
Else
Outputstr = Outputstr & Format(YearCount, " #00")
End If
If YearCount = LastYear Then
Exit Do
Else
YearCount = (YearCount + 1) Mod 100
End If
Loop
Range("B" & RowCount).NumberFormat = "@"
Range("B" & RowCount) = Outputstr
RowCount = RowCount + 1
Loop

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145620