(Information is Beautiful)

Un-Pivot Excel v2

This works in Excel 2013

Sub ReversePivotTable()
 ' Before running this, make sure you have a summary table with column headers.
 ' The output table will have three columns.
 Dim SummaryTable As Range, OutputRange As Range
 Dim OutRow As Long
 Dim r As Long, c As Long
On Error Resume Next
 Set SummaryTable = ActiveCell.CurrentRegion
 If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
 MsgBox "Select a cell within the summary table.", vbCritical
 Exit Sub
 End If
 SummaryTable.Select
 Sheets("Individual_Flat").Cells.Clear
 Set OutputRange = ActiveWorkbook.Sheets("Individual_Flat").Range("A1")
 ' Convert the range
 OutRow = 2
 Application.ScreenUpdating = False
 OutputRange.Range("A1:C3") = Array("Price_Scale", "Tier", "Values")
 For r = 2 To SummaryTable.Rows.Count
 For c = 2 To SummaryTable.Columns.Count
 OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
 OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
 OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
 OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
 OutRow = OutRow + 1
 Next c
 Next r
 End Sub
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s