Well, maybe an UnPivot or Reverse CrossTab process in Excel with VBA does not require the same amount of courage as Meriwether Lewis (pictured here) must have had as he and Clark set out on their exploration of the Louisiana Territory – nonetheless, the process can seem a bit overwhelming to a newcomer. I’ll do my best to explain it in a straight-forward manner.
Many times, we receive data in a crosstab format but we want it in a normalized format suitable for use with Pivot Tables or Excel Tables :
Here’s a fairly standard layout to retrieve data from a P&L Cube in Essbase using the Essbase Spreadsheet Add-in. This layout, however, is not conducive for creating PivotTables or for analyzing with Excel Tables. There are ways in Essbase to get this is a better “Normalized” format, but I have this at hand so it suits my needs for now.
Tell Me What You Need….
I marked-up the spreadsheet to show which fields I am interested in and in what order I would like them in the final output :
- Organization – Some division, region, district, store in the company under analysis
- Scenario – Budget, Forecast, Actual and various versions of the aforementioned
- Time – I’m showing periods in the screen shot, could be any measure of time
- Accounts – The name and or number of the account
- Measure – What we are really interested in : Amounts, statistics, ratios, etc…
How Would You Like That….
Here is the desired output :
Some of the values in the desired output are static, that is, the same value from the same cell must be output over and over again. Other times, I need to move across columns and still others I need to move down rows
Segue To Some Functions…
I’ll need a coupe of Functions so that my code is dynamic to some extent. I do not know the last column or the last row during development stage so I will need to determine those values at run-time.
Last Used Column
This function has only one argument, a worksheet, it will return the column number of the last used cell on the worksheet
Public Function GetLastColumn(ws As Worksheet) As Long
'Input : Worksheet
'Output : A column number of type long
'Declare variables
Dim rng As Range
Dim lngColumn As Long
'Get range address of last cell on worksheet
Set rng = ws.Cells.SpecialCells(xlCellTypeLastCell)
'Get column number of last cell
lngColumn = rng.Column
'Pass value to function
GetLastColumn = lngColumn
'Tidy up
Set rng = Nothing
End Function
Last Used Row
This function has only one argument, a worksheet, it will return the row number of the last used cell in Column A :
Public Function GetRows(ws As Worksheet) As Long
'Input : Worksheet
'Output : A row number of type long
'Assumptions : First column (A)
'Declare variables
Dim r As Long
'Get last row
With ws
r = .Cells(Rows.Count, 1).End(xlUp).Row
End With
'Pass value to function
GetRows = r
End Function
The Main Procedure
Here is the main procedure. The main thing to pay attention to is the different variable and static cell values used to get the correct output. Take a look at the code inside the For..Next Loops :
Option Explicit
Sub UnPivotData()
'Purpose : Convert crosstab data to normalized data
'Author : Winston Snyder
'Date : 5/26/2014
'Declare variables
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsDataNormalized As Worksheet
Dim MaxColumns As Long
Dim MaxRows As Long
Dim i As Long
Dim j As Long
Dim k As Long
'Excel environment - speed things up
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'Objects
Set wb = ThisWorkbook
With wb
Set wsData = .Worksheets("Data")
Set wsDataNormalized = .Worksheets("Normal")
End With
'Initialize
wsDataNormalized.UsedRange.ClearContents
MaxColumns = GetLastColumn(ws:=wsData)
MaxRows = GetRows(ws:=wsData)
k = 2
'Convert cross-tab report to normalized data table structure
With wsDataNormalized
For i = 6 To MaxRows 'Begin with first row of (Measures)
For j = 2 To MaxColumns 'Begin with first column of data (Measures)
.Cells(k, 1).Value = wsData.Cells(4, 1).Value 'Organization
.Cells(k, 2).Value = wsData.Cells(2, 1).Value 'Scenario
.Cells(k, 3).Value = wsData.Cells(5, j).Value 'Time
.Cells(k, 4).Value = wsData.Cells(i, 1).Value 'Account
.Cells(k, 5).Value = wsData.Cells(i, j).Value 'Measure
k = k + 1
Next j
Next i
'Add headers
.Range("A1").Value = "Organization"
.Range("B1").Value = "Scenario"
.Range("C1").Value = "Time"
.Range("D1").Value = "Account"
.Range("E1").Value = "Measure"
End With
'Tidy up
'Destroy objects
Set wsDataNormalized = Nothing
Set wsData = Nothing
Set wb = Nothing
'Restore Excel environment
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Perfect :-) Ready to Pivot or analyze with Excel Tables an Structured Reference Formulas.
It’s About Time….
I’m going to add a couple of lines to the Sub() to see how long it takes. I’ll use the GetTickCount function from the Windows kernel32 library :
Option Explicit
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub UnPivotData()
'Purpose : Convert crosstab data to normalized data
'Author : Winston Snyder
'Date : 5/26/2014
'Start timer
Dim t As Long
t = GetTickCount
'Declare variables
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsDataNormalized As Worksheet
Dim MaxColumns As Long
Dim MaxRows As Long
Dim i As Long
Dim j As Long
Dim k As Long
'Excel environment - speed things up
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'Objects
Set wb = ThisWorkbook
With wb
Set wsData = .Worksheets("Data")
Set wsDataNormalized = .Worksheets("Normal")
End With
'Initialize
wsDataNormalized.UsedRange.ClearContents
MaxColumns = GetLastColumn(ws:=wsData)
MaxRows = GetRows(ws:=wsData)
k = 2
'Convert cross-tab report to normalized data table structure
With wsDataNormalized
For i = 6 To MaxRows 'Begin with first row of (Measures)
For j = 2 To MaxColumns 'Begin with first column of data (Measures)
.Cells(k, 1).Value = wsData.Cells(4, 1).Value 'Organization
.Cells(k, 2).Value = wsData.Cells(2, 1).Value 'Scenario
.Cells(k, 3).Value = wsData.Cells(5, j).Value 'Time
.Cells(k, 4).Value = wsData.Cells(i, 1).Value 'Account
.Cells(k, 5).Value = wsData.Cells(i, j).Value 'Measure
k = k + 1
Next j
Next i
'Add headers
.Range("A1").Value = "Organization"
.Range("B1").Value = "Scenario"
.Range("C1").Value = "Time"
.Range("D1").Value = "Account"
.Range("E1").Value = "Measure"
End With
'Tidy up
'Destroy objects
Set wsDataNormalized = Nothing
Set wsData = Nothing
Set wb = Nothing
'Restore Excel environment
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
'Timer
MsgBox GetTickCount - t & " Milliseconds", , " Milliseconds"
End Sub
I ran three trials of the revised code to check the timer. It ran in 62 ms, 78 ms and 62 ms respectively.