Formatting report - Pivot table

[onthecauseway]

I have a spreadsheet to track documents issued, when and a transmittal number. The transmittal can have multiple documents and each document can have multiple revisions.
Where I am stuck is trying to format this data into a report based on documents or on transmittal. I have tried using a pivot table but this doesnt give the correct format, for example I want the doc number & title on the same line. Below shows what I am after.
Is Pivot tables the way to go and if so how do i control the formatting?

[TABLE="width: 771"]
<tbody>[TR]
[TD="colspan: 5"][TABLE="width: 771"]
<tbody>[TR]
[TD="colspan: 5"]SOURCE DATA[/TD]
[/TR]
[TR]
[TD]Transmittal Ref[/TD]
[TD]Date[/TD]
[TD]Doc Number[/TD]
[TD]Revision[/TD]
[TD]Doc Title[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-001[/TD]
[TD="align: right"]01-Oct-19[/TD]
[TD]12345-dc-00-001[/TD]
[TD]A[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-001[/TD]
[TD="align: right"]01/10/2019[/TD]
[TD]12345-dc-00-002[/TD]
[TD]A[/TD]
[TD] FIRST FLOOR SECTION[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-002[/TD]
[TD="align: right"]02/10/2019[/TD]
[TD]12345-dc-00-003[/TD]
[TD]A[/TD]
[TD] NORTH ELEVATION[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-003[/TD]
[TD="align: right"]03/10/2019[/TD]
[TD]12345-dc-00-004[/TD]
[TD]A[/TD]
[TD]BUIDING EAST ELEVATION[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-002[/TD]
[TD="align: right"]02/10/2019[/TD]
[TD]12345-dc-00-001[/TD]
[TD]B[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-004[/TD]
[TD="align: right"]04/10/2019[/TD]
[TD]12345-dc-00-001[/TD]
[TD]C[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 472"]
<tbody>[TR]
[TD="colspan: 2"]REPORT BY TRANSMITTAL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-001[/TD]
[TD]01/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-DC-00-001[/TD]
[TD] A[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-dc-00-002[/TD]
[TD] A[/TD]
[TD] FIRST FLOOR SECTION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-002[/TD]
[TD]02/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-DC-00-001[/TD]
[TD] B[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-dc-00-003[/TD]
[TD] A[/TD]
[TD] NORTH ELEVATION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-003[/TD]
[TD]03/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-dc-00-004[/TD]
[TD] A[/TD]
[TD]BUIDING EAST ELEVATION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345-PL-CL-TM-004[/TD]
[TD]04/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12345-DC-00-001[/TD]
[TD] C[/TD]
[TD] 1ST FLOOR PLAN[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

PivotTable is not the way to go when manipulating text.

Here is my best guess for what will work for you:

Option Explicit

Sub ReportByTransmittal()

    Const sWorksheet As String = "Output"
    Dim lLastRow As Long
    Dim sTRef As String
    Dim lCheckRow As Long
    Dim sActiveSheet As String
    
    If ActiveSheet.Range("A1").Value <> "Transmittal Ref" Or _
        ActiveSheet.Name = sWorksheet Then
        MsgBox "Start the code with the source data worksheet active." & vbLf & _
            "'Transmittal Ref' is expected to be in A1."
        GoTo End_Sub
    End If
    sActiveSheet = ActiveSheet.Name
    
    'Create output worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sWorksheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = _
        sWorksheet 'After last
    
    'Copy source data to output
    Worksheets(sActiveSheet).UsedRange.Copy _
        Destination:=Worksheets(sWorksheet).Range("A1")
    
    With ActiveWorkbook.Worksheets(sWorksheet)
        'Sort Output
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A2:A" & lLastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=Range("C2:C" & lLastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange ActiveWorkbook.Worksheets(sWorksheet).Range("A1:E" & lLastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        lCheckRow = 2
        sTRef = .Cells(lCheckRow, 1).Value
        Do While sTRef <> vbNullString
            .Rows(lCheckRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .Range(.Cells(lCheckRow, 3), .Cells(lCheckRow, 5)).Cut _
                Destination:=.Range(.Cells(lCheckRow + 1, 2), .Cells(lCheckRow + 1, 4))
            If lCheckRow > 2 Then
                If .Cells(lCheckRow, 1).Value = .Cells(lCheckRow - 2, 1) Then
                    .Cells(lCheckRow, 1).ClearContents
                End If
            End If
            lCheckRow = lCheckRow + 2
            sTRef = .Cells(lCheckRow, 1).Value
        Loop
    
    End With
    
End_Sub:

End Sub

If that does not wrok please answer these questions.

In your example the drawings for the same Transmittal Ref always have the same dates. Will this always be the case?
There are duplicated doc numbers for different Transmittal Refs can this actually occur?
Will your source data always show only the most recent revision for each