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>
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,232
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top