Transposing a vertical table into a horizontal table

[georgiboy]

I am sure others will be along with another version but I have to go home now so will leave you with what I had created before - albeit very crude.

As you can see from below I had a bit of a counter overload... x, y, n, c, e

Sub test()    
    Dim rng As Range, tmp As String, col As New Collection
    Dim var As Variant, OutVar() As Variant
    Dim x As Long, y As Long, n As Long, c As Long, e As Long
    
    Set rng = Sheet1.UsedRange
    Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
    var = rng.Value
    
    For x = 1 To UBound(var)
        On Error Resume Next
            tmp = var(x, 1)
            col.Add var(x, 1), CStr(var(x, 1)) ' var rows
            If Err.Number <> 0 And var(x, 1) = tmp Then
                c = c + 1
            Else
                If c > e Then
                    e = c: c = 0 ' e = max column count
                End If
            End If
        On Error GoTo 0
    Next x
    
    ReDim OutVar(col.Count - 1, e + 2) ' resize array for the data (+2 for the two fixed columns)
    
    y = -1: n = 2
    For x = 1 To UBound(var)
        If var(x, 1) <> tmp Then
            y = y + 1: n = 2
            OutVar(y, 0) = var(x, 1)
            OutVar(y, 1) = var(x, 2)
            OutVar(y, 2) = var(x, 4)
        Else
            n = n + 1
            OutVar(y, n) = var(x, 4)
        End If
        tmp = var(x, 1)
    Next x
    Sheet3.Range("A2").Resize(UBound(OutVar) + 1, UBound(OutVar, 2) + 1) = OutVar
End Sub

Of course I may have missed the point completely - I am good at that.

Hope this helps