Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move Row Data to Columns (Excel 2000 SR-1)

    We had a problem with some test results that have been downloaded to Excel. The test was giving us temperature readings for 5 different test zones every second for a period of 2 hours (5 columns of data each second). Each of these results has been recorded in a single row in Excel instead of in 5 different columns (person that set up the test forgot to enter the instructions for that crucial "Enter" key at the end of each test reading to return to the first column in the next row). Does anyone know of a way I can have every 5 records of the results moved to 5 different columns (i.e. column A being "Zone 1" results, column B being "Zone 2" results, column C being "Zone 3" results, etc.). I don't think a standard formula will work because I'm attempting to pull row data into columns. (I realize that some data was lost in the test due to the number of columns available in Excel, but I'd like to give them as much of the test result as possible.)

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

    Re: Move Row Data to Columns (Excel 2000 SR-1)

    Do you mean that everything is squashed into column A? If so, does Data | Text to Columns... do the job? Otherwise, could you provide a small example of what the data look like?

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Row Data to Columns (Excel 2000 SR-1)

    Each reading is in a separate column. The only exception is the first reading each time and that gives me both the date/time stamp and reading but I can separate that using "Right" and "Left " formulas.

    I've tried to attach a sample of the test results. If it doesn't work, here's what my data looks like. Each of the readings is showing up in its own column.

    0 0 0 0 0 134 134 134 134 134 228 228 228 228

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

    Re: Move Row Data to Columns (Excel 2000 SR-1)

    I hope that the following macro does what you want, or else can be adapted to your needs:

    Sub Row2Col()
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngIndex As Long
    Dim strVal As String
    Dim lngPos As Long
    Dim datTime As Date

    ' Clear from F1 to the end
    Range(Range("F1"), Range("F1").End(xlToRight)).Clear

    ' Initialize some variables
    lngRow = 2
    lngCol = 6
    strVal = Cells(2, lngCol).Value

    ' Loop through cells from F2 to the end
    Do While strVal <> ""
    Cells(2, lngCol).ClearContents
    If lngCol Mod 5 = 1 Then
    lngRow = lngRow + 1
    lngIndex = 0
    lngPos = InStr(strVal, "AM")
    If lngPos = 0 Then
    lngPos = InStr(strVal, "PM")
    End If
    datTime = CDate(Left(strVal, lngPos + 1))
    strVal = Mid(strVal, lngPos + 2)
    Cells(lngRow - 1, 6).Value = datTime
    Cells(lngRow - 1, 6).NumberFormat = "mm/dd/yyyy hh:mm:ss"
    If strVal = "" Then Exit Do
    End If
    lngIndex = lngIndex + 1
    Cells(lngRow, lngIndex) = CSng(strVal)
    lngCol = lngCol + 1
    strVal = Cells(2, lngCol).Value
    Loop
    End Sub

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move Row Data to Columns (Excel 2000 SR-1)

    It looks like your macro worked wonderfully. Thank you so much.

Posting Permissions

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