Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    transposing data (2002)

    I have some time series data in the following form, by rows for years and months, 1948 through 2004:
    Column A: Year
    Column B: Month
    Columns C through AG: DAY01 through DAY31

    All columns are full of data, with -99999 being used for days of the month that don

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transposing data (2002)

    Try this macro. The sheet containing the data must be the active sheet when the macro is run. The macro will create a new sheet, named "Time Series", for the transposed data.

    <pre>Option Explicit


    Public Sub BuildTimeSeries()
    Dim I As Long, J As Long, k As Long
    Dim oSrc As Worksheet, oTgt As Worksheet
    Set oSrc = ActiveSheet
    On Error Resume Next
    Set oTgt = Nothing
    Set oTgt = Worksheets("Time Series")
    On Error GoTo 0
    If oTgt Is Nothing Then
    Set oTgt = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
    End If
    oTgt.Cells.Delete
    oTgt.Name = "Time Series"
    oSrc.Activate
    k = 0
    For I = 0 To oSrc.Range("A65536").End(xlUp).Row - 1
    For J = 0 To 30
    If oSrc.Range("C1").Offset(I, J).Value > -99999 Then
    oTgt.Range("A1").Offset(k, 0).Value = DateSerial(oSrc.Range("A1").Offset(I, 0).Value, _
    oSrc.Range("B1").Offset(I, 0).Value, J + 1)
    oTgt.Range("B1").Offset(k, 0).Value = oSrc.Range("C1").Offset(I, J).Value
    k = k + 1
    End If
    Next J
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transposing data (2002)

    You can use a macro. I have attached a sample workbook.

    Sub Transform()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim strDate As String

    ' Adjust constants as needed
    ' days per month
    Const NumDays = 31
    ' 2 years
    Const NumMonths = 12 * 2

    Set wshSource = Worksheets("Sheet1")
    Set wshTarget = Worksheets("Sheet2")

    k = 2
    For i = 2 To NumMonths + 1
    For j = 3 To NumDays + 2
    strDate = (j - 2) & "-" & wshSource.Cells(i, 2) & "-" & wshSource.Cells(i, 1)
    If IsDate(strDate) Then
    wshTarget.Cells(k, 1) = CDate(strDate)
    wshTarget.Cells(k, 2) = wshSource.Cells(i, j)
    k = k + 1
    End If
    Next j
    Next i
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transposing data (2002)

    Not exactly sure how it works, but it does. I'll decipher the code later.
    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •