Normalise Pivot Table

[meljunk]

Hi all,

I want to normalise a pivot table using VBA.

Currently my sheet looks as follows:

Field1, Field2, Date1, Date2, Date3, Date4
key1, key2, Value1, Value2, Value3, Value4

However, I want to view it as:

Field1, Field2, Date, Value
key1, key2, Date1, Value1
key1, key2, Date2, Value2

So far I have the following VBA code, but it only normalise based on the Field1. I know I need to include another loop but I can't seem to perfect it

Sub Normalise()


Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant


    Set wsOriginal = ThisWorkbook.Worksheets("Forecast14")     
    Set wsNormalized = ThisWorkbook.Worksheets("Sheet1") 
    Set clnHeader = New Collection
    
    wsNormalized.Cells.ClearContents        


    lngColumnCounter = 1
    lngRowCounterOriginal = 1
    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
    
    ' Loop through headers to get header names
    Do Until IsEmpty(rngCurrent.Value)
        clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
        lngColumnCounter = lngColumnCounter + 1
        Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
    Loop
    
    'Reset Row Counter 
    lngRowCounterOriginal = 2
    lngRowCounterNormalized = 1
    lngColumnCounter = 1
    
    'Loop through the entire data set
    Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
    
        Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
        strKey = rngCurrent.Value ' Get the key value from the current cell'
        lngColumnCounter = 1
    
        Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
            Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
                If rngCurrent.Value = "NULL" Then
                    'Skip it'
                Else
                    'Add this item to the normalized sheet'
                    wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
                    wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
                    wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
                    lngRowCounterNormalized = lngRowCounterNormalized + 1
                End If
            
            lngColumnCounter = lngColumnCounter + 1
        Loop
        lngRowCounterOriginal = lngRowCounterOriginal + 1
        lngColumnCounter = 1    'We reset the column counter here because we're on a new row'
    Loop


End Sub

Thanking you in advance

[mancubus]

hi meljunk. seeing the workbook will help us help you. can you post your workbook with sample data, including the "after macro" scenario.

[meljunk]

Sample.jpg

[p45cal]

I suspect this can be done from the source data of the pivot table with a different pivot table design, but that may depend on what that data is.
While a picture is great, an actual workbook would be even better, especially one with sample source data for the pivot table(s).

[snb]

See:

http://dailydoseofexcel.com/archives...#comment-96661