Formula or Macro to convert a single column to rows and columns

[Eann1872]

I have data that is in a single column ( shown as raw data below) and I need to have it in a table with 5 columns (time, name 1, name 2, extras 1, extras 2). There are roughly 100 rows each week, however they are always in the same format and each row is always populated. Either a formula or macro would be great.

Raw Data		Time	Name 1	Name 2	Cost	Extras 1	Extras 2
14:00​		14:00​	John	Janet	125​	2.5​	3.75​
John		17:00​	Mary	Joseph	250​	1.75​	2.25​
Janet
125​
2.5​
3.75​
17:00​
Mary
Joseph
250​
1.75​
2.25​

 

[Alex Blakenburg]

See if this does what you want.
It is currently set to output the result to the same sheet at D1. It assumes the header is not there an will create it at D1.

Sub TransposeData()

    Dim shtSrc As Worksheet
    Dim rngSrc As Range, rngDest As Range
    Dim arrSrc As Variant, arrDest As Variant
    Dim lastRowSrc As Long, iSrc As Long, iDest As Long
    
    Set shtSrc = ActiveSheet
    With shtSrc
        lastRowSrc = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rngSrc = .Range(.Cells(2, "A"), .Cells(lastRowSrc, "A"))
        arrSrc = rngSrc
    End With
    
    Set rngDest = shtSrc.Range("D1")                        ' <--- Change this to where you want the output
    
    ReDim arrDest(1 To UBound(arrSrc), 1 To 6)
    iDest = 0
    
    For iSrc = 1 To UBound(arrSrc) Step 6
        iDest = iDest + 1
        arrDest(iDest, 1) = arrSrc(iSrc, 1)
        arrDest(iDest, 2) = arrSrc(iSrc + 1, 1)
        arrDest(iDest, 3) = arrSrc(iSrc + 2, 1)
        arrDest(iDest, 4) = arrSrc(iSrc + 3, 1)
        arrDest(iDest, 5) = arrSrc(iSrc + 4, 1)
        arrDest(iDest, 6) = arrSrc(iSrc + 5, 1)
    Next iSrc
    
    ' Write out heading and transposed data
    rngDest.Resize(1, UBound(arrDest, 2)) = Array("Time", "Name 1", "Name 2", "Cost", "Extras 1", "Extras 2")
    rngDest.Offset(1).Resize(iDest, UBound(arrDest, 2)) = arrDest
     
End Sub

 

[Eann1872]

Yes perfect thank you. Had to make a few adjustments as you suggested with cell references etc. That will save me a lot of time.

 

[Alex Blakenburg]

You're welcome. Glad I could help.