Transpose data of different lenghts

Hi

I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally

Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.

Is there any way that a macro could be created to do this.

JB

Performance.xlsx

Pivoting those data:
If you want to use the below without modification, delete your "Output" sheet first.

Sub Macro1()
Dim LR As Long
Dim LC As Long

LR = Sheets("Input").Cells(Rows.Count, "A").End(xlUp).Row
LC = Sheets("Input").Cells(1, Columns.Count).End(xlToLeft).Column

Sheets.Add.Name = "Output"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Input!R1C1:R" & LR & "C" & LC, _
    Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="Output!R3C1", _
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

With Sheets("Output")
.PivotTables("PivotTable1").PivotFields("Portfolio").Orientation = xlRowField
.PivotTables("PivotTable1").PivotFields("Portfolio").Position = 1
.PivotTables("PivotTable1").PivotFields("Client Number").Orientation = xlRowField
.PivotTables("PivotTable1").PivotFields("Client Number").Position = 2
.PivotTables("PivotTable1").PivotFields("Month End").Orientation = xlColumnField
.PivotTables("PivotTable1").PivotFields("Month End").Position = 1
.PivotTables("PivotTable1").PivotFields("Month End").AutoSort xlDescending, "Month End"
End With


ActiveSheet.PivotTables("PivotTable1").AddDataField _
ActiveSheet.PivotTables("PivotTable1").PivotFields("Performance"), "PerformanceTmp", xlAverage

With ActiveSheet.PivotTables("PivotTable1")
.PivotFields("PerformanceTmp").NumberFormat = "0.00"
.RowAxisLayout xlTabularRow
.PivotFields("Portfolio").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Client Number").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Month End").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Performance").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
.NullString = "0"
End With
End Sub

Maybe something like this?

Sub Test()
Sheets("Input").Activate
Sheets("Output").Cells.Clear
Sheets("Output").Cells(1, 1) = "Portfolio"
Sheets("Output").Cells(1, 2) = "Client Number"
Sheets("Output").Rows(1).NumberFormat = "dd/mm/yyyy"
Cells(1, 1).CurrentRegion.Sort Header:=xlYes, Key1:=Cells(1, 1), Order1:=xlAscending, Key1:=Cells(1, 2), Order2:=xlAscending
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If WorksheetFunction.CountIf(Sheets("Output").Rows(1), Cells(N, 3)) = 0 Then
        Sheets("Output").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(N, 3)
    End If
    Sheets("Output").Columns.AutoFit
    TargetColumn = Sheets("Output").Rows(1).Find(Left(Cells(N, 3), 10), , xlValues, xlWhole).Column
    
    If WorksheetFunction.CountIfs(Sheets("Output").Columns(1), Cells(N, 1), Sheets("Output").Columns(2), Cells(N, 2)) = 0 Then
        Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Cells(N, 1)
        Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = Cells(N, 2)
    End If
    Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Offset(0, TargetColumn - 1) = Cells(N, 4)
Next N
End Sub

Thanks any chance you can let me know how this works