Formatting report - Pivot table

onthecauseway

New Member
Joined
Oct 19, 2017
Messages
2
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?

SOURCE DATA
Transmittal RefDateDoc NumberRevisionDoc Title
12345-PL-CL-TM-00101-Oct-1912345-dc-00-001A 1ST FLOOR PLAN
12345-PL-CL-TM-00101/10/201912345-dc-00-002A FIRST FLOOR SECTION
12345-PL-CL-TM-00202/10/201912345-dc-00-003A NORTH ELEVATION
12345-PL-CL-TM-00303/10/201912345-dc-00-004ABUIDING EAST ELEVATION
12345-PL-CL-TM-00202/10/201912345-dc-00-001B 1ST FLOOR PLAN
12345-PL-CL-TM-00404/10/201912345-dc-00-001C 1ST FLOOR PLAN

<tbody>
</tbody>

<tbody>
</tbody>
REPORT BY TRANSMITTAL
12345-PL-CL-TM-00101/10/2019
12345-DC-00-001 A 1ST FLOOR PLAN
12345-dc-00-002 A FIRST FLOOR SECTION
12345-PL-CL-TM-00202/10/2019
12345-DC-00-001 B 1ST FLOOR PLAN
12345-dc-00-003 A NORTH ELEVATION
12345-PL-CL-TM-00303/10/2019
12345-dc-00-004 ABUIDING EAST ELEVATION
12345-PL-CL-TM-00404/10/2019
12345-DC-00-001 C 1ST FLOOR PLAN

<tbody>
</tbody>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
PivotTable is not the way to go when manipulating text.

Here is my best guess for what will work for you:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top