excel problem - converting a long column of data into sections?

Favorite Answer

Edit: Sorry for the delay.... here is a way to do as you wish. The following example will create a table in columns M:AR, with the time increments in column M and the daily values for each in columns N:AR. It assumes the raw data is in columns A and B.

Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim i, j, k, n, LastRow, LastRowM
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("M:AR").ClearContents
Range("M1").Value = "Time"
Range("M2:M97").NumberFormat = "h:mm;@"
Range("M1:AR1").HorizontalAlignment = xlCenter
For i = 1 To LastRow
If Application.CountIf(Range("M:M"), TimeValue(Cells(i, "A").Value)) = 0 Then
ActiveSheet.Range("M" & Rows.Count).End(xlUp).Offset(1).Value = _
TimeValue(Cells(i, "A").Value)
End If
Next
LastRowM = Range("M" & Rows.Count).End(xlUp).Row
For i = 2 To LastRowM
For j = 1 To LastRow
If TimeValue(Cells(j, "A").Value) = Cells(i, "M").Value Then
Range("IV" & i).End(xlToLeft).Offset(0, 1).Value = Cells(i - 1, "B").Value
End If
Next j
Next i
n = 1
For k = 14 To 44
Cells(1, k).Value = "Day " & n
n = n + 1
Next
End Sub

Select the worksheet containing the data and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the code into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button w/white 'x').

To evaluate the data and create the table, simply double click any cell. If you wish to recreate the table at any time, in the event of new/changed data, simply double click any cell again. A new, clean, table will be created.

Advise by email if you have difficulty implementing, or you need to relocate the table to a different area of the worksheet....Show more