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

Topic

New Reply

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.)

Reply | Quote

Replies

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote