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!

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