And that's exactly what I meant in post 4. Some VBA can generate that ID. Then CROSSTAB should work.
Code:
Sub SetBlockID()
Dim c As Integer, r As Integer, intID As Integer
r = 2
c = 1
intID = 1
While Not IsEmpty(Worksheets("Sheet1").Range("A" & r))
If c <= 8 Then
Worksheets("Sheet1").Cells(r, 3) = intID
c = c + 1
If c = 9 Then
intID = intID + 1
c = 1
End If
End If
r = r + 1
Wend
End Sub
Or example of VBA code in workbook that transposes on worksheet:
Code:
Sub Transpose()
Dim c As Integer, r As Integer, x As Integer
x = 2
r = 2
c = 3
While Not IsEmpty(Worksheets("Sheet1").Range("A" & x))
If c < 12 Then
c = c + 1
Worksheets("Sheet1").Cells(r, 4) = r - 1
Worksheets("Sheet1").Cells(r, c) = Worksheets("Sheet1").Range("B" & x)
x = x + 1
Else
c = 4
r = r + 1
End If
Wend
End Sub
Assumes: each block has 8 rows; no blank cells in column A until the end.