macro to transpose data

Try this:

Sub tranpose2()
Dim arr, dat As Variant
Dim r&, x&, y&, z As Long
With Sheets("raw_data")
    arr = .UsedRange.Value
    r = .UsedRange.Rows.Count
End With
ReDim dat(1 To r / 2, 1 To 4)

For x = 12 To 1650 Step 39
    y = 3 + 16 * (x - 12) / 39
    dat(y, 1) = arr(x, 4)
    For z = 5 To 35 Step 2
        dat(y + (z - 5) / 2, 2) = arr(x + z, 3)
        dat(y + (z - 5) / 2, 3) = arr(x + z, 8)
        dat(y + (z - 5) / 2, 4) = arr(x + z, 11)
    Next z
Next x
Sheets("sheet2").Range("b3").Resize(r / 2, 4).Value = dat
End Sub

You're welcome. Glad I could be helpful