Data Comparison

[kalakriti]

I have a huge data set running into thousands of line which has data for the current and previous year


I am attaching the excel file for reference


First sheet contains sample data and it is reflected in the Data _sheet


I am looking for a VBA solution which can extract , and present me data comparing under following three criteria

1. Country

2. Line of business

3 Customer

The result expected is shown in the output sheet.

I need to do this every month to analyze and report management about the variation over previous period

Currently extracting the same through pivot table but it is very time consuming

appreciate any kind of assistance on the same


Thanks

[Bob Phillips]

3 simple pivot tables is the easiest, and probably the fastest way.

[Paul_Hossler]

Pivot tables are the way to go -- you can copy the PT and format the copies




Option Explicit
Dim ptTemp As PivotTable
Dim wsData As Worksheet, wsTemp As Worksheet, wsOut As Worksheet
Sub FormatData()
Dim rData As Range


Application.ScreenUpdating = False


Set wsData = Worksheets("Data_Sheet")

'delete old sheets if exist
Call pvtDelete("Temp")
Call pvtDelete("Output")

'add temp and a final sheet
Worksheets.Add.Name = "Temp"
Set wsTemp = ActiveSheet

Worksheets.Add.Name = "Output"
Set wsOut = ActiveSheet


Set rData = wsData.Cells(3, 1).CurrentRegion

'create country PT
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rData, Version:=6).CreatePivotTable _
TableDestination:=wsTemp.Cells(1, 1), TableName:="PivotTable1", DefaultVersion:=6
Set ptTemp = wsTemp.PivotTables(1)


With ptTemp
With .PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With

With .PivotFields("Country")
.Orientation = xlRowField
.Position = 1
End With


.AddDataField .PivotFields("Qty.(Kgs)"), "Sum of Qty.(Kgs)", xlSum
.DataPivotField.PivotItems("Sum of Qty.(Kgs)").Caption = "Qty (Kgs)"
.PivotFields("Qty (Kgs)").NumberFormat = "#,##0.00"


.AddDataField .PivotFields(" Gross sls"), "Sum of Gross sls", xlSum
.DataPivotField.PivotItems("Sum of Gross sls").Caption = "Gross Sales"
.PivotFields("Gross Sales").NumberFormat = "#,##0.00"

.RowGrand = False

.CompactLayoutRowHeader = "Country"

'copy Country to Output
Call pvtCopy(wsOut.Range("A1"))


'reformat PT as LOB
.PivotFields("Country").Orientation = xlHidden
With .PivotFields("Line of Business")
.Orientation = xlRowField
.Position = 1
End With
.CompactLayoutRowHeader = "Line of Business"


'copy LOB to Output
Call pvtCopy(wsOut.Range("G1"))

'reform PT as Customer Name
.PivotFields("Line of Business").Orientation = xlHidden
With .PivotFields("CustomerName")
.Orientation = xlRowField
.Position = 1
End With
.CompactLayoutRowHeader = "CustomerName"

'copy Customer to Output
Call pvtCopy(wsOut.Range("M1"))

End With

'format output
With wsOut
.Rows(1).Delete
Call pvtFormat(.Range("A1"))
Call pvtFormat(.Range("G1"))
Call pvtFormat(.Range("M1"))

.Select

.Range("A1").Select
End With

Call pvtDelete("Temp")
Application.ScreenUpdating = False
End Sub
Private Sub pvtDelete(s As String)
Application.DisplayAlerts = False
On Error Resume Next
Worksheets(s).Delete
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Private Sub pvtCopy(rOut As Range)
ptTemp.TableRange1.Copy
rOut.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
rOut.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub


Private Sub pvtFormat(rIn As Range)
Dim rYear As Range, r As Range

Set r = rIn.CurrentRegion

r.CurrentRegion.EntireColumn.AutoFit
For Each rYear In r.Rows(1).SpecialCells(xlCellTypeConstants, xlNumbers).Cells
rYear.Resize(1, 2).Merge
rYear.HorizontalAlignment = xlHAlignCenter
Next
With r
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlNone
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End With
End Sub

[Bob Phillips]

Personally, I would do it in Power Query.

Fiver simple queries:-

GetData

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
data.Types = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Qty.(Kgs)", type number}, {" Gross sls", type number}})
in
data.Types

a shared function
AnalyseData

(criteria as any, format as text, sortby as any, remove as any) =>

let
Source = GetData,
criteria.Group = Table.Group(Source, criteria,
{{"Qty", each List.Sum([#"Qty.(Kgs)"]), type number},
{"Sales", each List.Sum([#" Gross sls"]), type number}}),
qty.Format = Table.AddColumn(criteria.Group, "Quantity", each Number.ToText([Qty], format)),
sales.Format = Table.AddColumn(qty.Format, "Gross Sales", each Number.ToText([Sales], format)),
criteria.sort = Table.Sort(sales.Format, sortby),
final.Layout = Table.RemoveColumns(criteria.sort, remove)
in
final.Layout

and 3 results queries

CountryAnalysis

let
_criteria = {"Year", "Country"},
_amt.format = "N0",
_criteria.sort = {{"Year", Order.Ascending}, {"Country", Order.Ascending}},
_cols.toremove = {"Qty", "Sales"},

Analysis = AnalyseData(_criteria, _amt.format, _criteria.sort, _cols.toremove)
in
Analysis

BusinessAnalysis

let
_criteria = {"Year", "Line of Business"},
_amt.format = "#,##0",
_criteria.sort = {{"Year", Order.Ascending}, {"Line of Business", Order.Ascending}},
_cols.toremove = {"Qty", "Sales"},

Analysis = AnalyseData(_criteria, _amt.format, _criteria.sort, _cols.toremove)
in
Analysis

CustomerAnalysis

let
_criteria = {"Year", "CustomerName"},
_amt.format = "#,##0",
_criteria.sort = {{"Year", Order.Ascending}, {"CustomerName", Order.Ascending}},

_cols.toremove = {"Qty", "Sales"},

Analysis = AnalyseData(_criteria, _amt.format, _criteria.sort, _cols.toremove)
in
Analysis

the three analysis queries are loaded to a worksheet, the other two are connection only.