asked on

Need help with pivot summary or report

I have a file with thousands of lines of purhase data. It is summarized by month. I need to monitor the buy unit of measure price for deltas (price increases). I created a pivot summary that shows total spend by month AND the different prices paid however, I don't like the format at all. I need a format that can be easily scanned to identify price increases from month to month.
I'm hoping that someone can offer a robust pivot table report that can do just this.

Thanks
format-needed.xlsx

Jacques Geday

PLease check attached version under pivot price fluctuation
gowflow
format-needed-V01.xlsx

vpopper

ASKER

with thousands of lines, this format would still be hard to work with. you have to visually look at each data point...?

Edward Pamias

Try this one....
EE---format-needed.xlsx

Edward Pamias

This one I added a named range for when you add or remove date... just refresh the pivot.
EE---format-needed_rev2.xlsx

Shums Faruk

Hi,

Do you need vba approach which will do better formatting?

If yes, please paste below code in your Module and run "CreatePivot" macro:

Sub CreatePivot()
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PField As PivotField
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With
'Delete Preivous Pivot Table Worksheet &amp; Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotSummary").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotSummary"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotSummary")
Set DSheet = Worksheets("Sheet3")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="FilteredPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="FilteredPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Item")
 .Orientation = xlRowField
 .Position = 1
End With
'Insert Row Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Item Description")
 .Orientation = xlRowField
 .Position = 2
End With
'Insert Row Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Buy UOM Price")
 .Orientation = xlRowField
 .Position = 3
End With
'Insert Column Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Year")
 .Orientation = xlColumnField
 .Position = 1
End With
'Insert Column Fields
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Month")
 .Orientation = xlColumnField
 .Position = 2
End With
'Insert Data Field
With ActiveSheet.PivotTables("FilteredPivotTable").PivotFields("Total Spend")
 .Orientation = xlDataField
 .Position = 1
 .Function = xlSum
 .NumberFormat = "$ #,##0.00_);[Red]($ #,##0.00)"
 .Name = "Total Spendings"
End With
'Format Pivot Table
ActiveSheet.PivotTables("FilteredPivotTable").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutRowHeader = "Items"
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutRowHeader = "Item Description"
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutRowHeader = "Buy UOM Price"
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutColumnHeader = "Year"
ActiveSheet.PivotTables("FilteredPivotTable").CompactLayoutColumnHeader = "Month"
ActiveSheet.PivotTables("FilteredPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("FilteredPivotTable").TableStyle2 = "PivotStyleMedium9"
'Remove Subtotal
With ActiveSheet.PivotTables("FilteredPivotTable")
    For Each PField In .PivotFields
        PField.Subtotals(1) = False
    Next PField
End With
ActiveSheet.PivotTables("FilteredPivotTable").PivotSelect _
        "'Buy UOM Price'[All]", xlLabelOnly, True
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
ActiveSheet.PivotTables("FilteredPivotTable").PivotSelect "'Row Grand Total'", _
        xlDataAndLabel, True
Selection.Font.Bold = True
ActiveWorkbook.ShowPivotTableFieldList = False
With PSheet.Range("B2:T4")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
PSheet.Range("B2:T4").Font.Bold = True
PSheet.Range("B2").Select
PSheet.Columns.AutoFit
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Please find attached for your reference.
vpopper_Pivot-Table_v1.xlsm

Jacques Geday

check this one. it is an updated version of V01
gowflow
format-needed-V02.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vpopper

ASKER

I line the non vba - thanks so much

Shums Faruk

Sir,

If you like the non-vba solution, then why are you closing this question without awarding any of the solution?

Jacques Geday

I don't understand either vpopper action to close this question with 0 point awarded to him. I feel it is done by mistake.

@vpopper appreciate you advise what you did and explain your action. it is obvious you are not new to this site reason why we ask you to please clarifiy your action.

gowflow

Edward Pamias

I agree with the other experts. Please apply points to the post that helped you.

Edward Pamias

This should be reopened and points applied.

Jacques Geday

Agree ... let's wait for his comments
gowflow

vpopper

ASKER

I don't understand the issue here, I looked at the other examples and I felt that Shum provided the best - I do appreciate everyone's help but as I stated, I was looking for something visually easy to scan through and Shum's we best for me.

Thanks

vpopper

ASKER

Visually the best solution for me

Edward Pamias

That's all we wanted. Originally you selected to delete the question. Thanks for correcting it. :)