seprate data in rows to columns

[CORAL]

1508hi
i have data like below
time time time time
2014/03/01 05:15 0.7 11:31 4 17:24 0.5
2014/03/02 00:04 4.3 05:59 0.5 12:19 3.9 18:08 0.6
2014/03/03 00:49 4.4 06:40 0.5 13:03 3.8 18:51 0.7
2014/03/04 01:31 4.4 07:21 0.5 13:49 3.7 19:33 1
2014/03/05 02:11 4.3 08:02 0.6 14:40 3.5 20:16 1.2
2014/03/06 02:47 4.1 08:41 0.7 15:44 3.3 21:02 1.6
2014/03/07 03:23 3.9 09:18 0.9 16:53 3.1 21:56 1.8
i want to have data with vb code like
2014/03/01 05:15 0.7
2014/03/01 11:31 4
2014/03/01 17:24 0.5
2014/03/02 00:04 4.3
2014/03/02 05:59 0.5
..................................
................................

[alansidman]

I think what you are looking for can be accomplished by normalizing your spreadsheet.

Normalizing a table with repeating fields -- DataWright Information Services (http://www.datawright.com.au/access_resources/normalizing_tables_with_repeating_fields_using_VBA .htm)

[Admin]

Hi

Try this


Option Explicit

Sub kTest()

Dim k, ka(), i As Long, n As Long, c As Long, Sht As Worksheet

k = Range("a1").CurrentRegion.Value2 '<<< adjust the range

ReDim ka(1 To UBound(k, 1) * UBound(k, 2), 1 To 3)

For i = 2 To UBound(k, 1) 'skip the header row
If Len(k(i, 1)) Then
For c = 2 To UBound(k, 2) - 1 Step 2
n = n + 1
ka(n, 1) = k(i, 1)
ka(n, 2) = k(i, c)
ka(n, 3) = k(i, c + 1)
Next
End If
Next

If n Then
On Error Resume Next
Set Sht = Worksheets("Output_")
Err.Clear: On Error GoTo 0
If Sht Is Nothing Then
Worksheets.Add.Name = "Output_"
Set Sht = ActiveSheet
End If

With Sht
.Range("a1:c1") = [{"Date","Time","Value"}]
.Range("a2").Resize(n, UBound(ka, 2)).Value = ka
.Range("a2").Resize(n).NumberFormat = "yyyy/mm/dd"
.Range("b2").Resize(n).NumberFormat = "[h]:mm"
End With
End If

End Sub