Excel pivot tables are an incredible tool for looking at data. One caveat is the data needs to be entered correctly - discreet rows for each piece of data as opposed to extra columns.
This macro is to help manipulate data in the wrong format. You simply select that static columns, run the macro, and it creates a sheet transposing any extra columns in to new rows (repeating the static columns).
It may require you to make temporary heading columns as the macro does require a header for each column with data.
A tip - you can combine this with the text to fields function to quickly process a variety of data formats.
Enjoy!
This macro is to help manipulate data in the wrong format. You simply select that static columns, run the macro, and it creates a sheet transposing any extra columns in to new rows (repeating the static columns).
It may require you to make temporary heading columns as the macro does require a header for each column with data.
A tip - you can combine this with the text to fields function to quickly process a variety of data formats.
Enjoy!
Sub Make_Pivotable()
'Macro to transpose columns
'Select the static columns, then run the macro
'Enjoy! simon@edgett.bc.ca
ret = MsgBox("Please ensure first row includes a header for EACH column with data. Create column headings such as [data1] and [data2] if required." & vbCrLf & vbCrLf & "Please SELECT the rows that have static data." & vbCrLf & vbCrLf & "It will be assumed that for each header line not selected that the data should be transposed.", vbOKCancel, "Prerequisites")
If ret = vbOK Then
StaticStart = Selection.Column
StaticEnd = Selection.Column + Selection.Columns.Count - 1
VarStart = StaticEnd + 1
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
End With
If (LastCol < VarStart) Then
MsgBox ("An error occurred. There are no variable columns!")
Exit Sub
End If
Dim BaseSheet As Worksheet
Set BaseSheet = ActiveSheet
Dim PivotDataSheet As Worksheet
Set PivotDataSheet = Sheets.Add
'COPY HEADERS
For loop3 = StaticStart To StaticEnd
PivotDataSheet.Cells(1, loop3).Value = BaseSheet.Cells(1, loop3).Value
Next
PivotDataSheet.Cells(1, VarStart).Value = "PivotData"
'COUNT IS TWO AS HEADER ON ROW 1
Count = 2
For loop1 = 2 To LastRow
For loop2 = VarStart To LastCol
If (Not IsEmpty(BaseSheet.Cells(loop1, loop2))) Then
For loop3 = StaticStart To StaticEnd
PivotDataSheet.Cells(Count, loop3).Value = BaseSheet.Cells(loop1, loop3).Value
Next
PivotDataSheet.Cells(Count, VarStart).Value = BaseSheet.Cells(loop1, loop2).Value
Count = Count + 1
End If
Next
Next
End If
' vbok
End Sub