PDA

View Full Version : Normalise Pivot Table



meljunk
02-12-2014, 04:54 AM
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
02-12-2014, 05:12 AM
hi meljunk. seeing the workbook will help us help you. can you post your workbook with sample data, including the "after macro" scenario.

meljunk
02-12-2014, 06:00 AM
11262

p45cal
02-12-2014, 10:12 AM
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
02-12-2014, 01:39 PM
See:

http://dailydoseofexcel.com/archives/2013/11/19/unpivot-via-sql/#comment-96661