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.