UndauntedFinal

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 :

Crosstab

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….

CrosstabMarkup

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 :

  1. Organization – Some division, region, district, store in the company under analysis
  2. Scenario – Budget, Forecast, Actual and various versions of the aforementioned
  3. Time – I’m showing periods in the screen shot, could be any measure of time
  4. Accounts – The name and or number of the account
  5. Measure – What we are really interested in : Amounts, statistics, ratios, etc…

How Would You Like That….

Here is the desired output :

CrosstabOutput

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

CrosstabOutputNormal

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.

Tidy Up

    UnPivot

      UnPivot Shootout – DDoE
      Reshape Data

    Essbase

      Hyperion Essbase
      Hyperion Essbase Spreadsheet Add-In

    kernel32.dll

      kernel32.dll
      kernel32 documentation
    ,