Trying to Create a list from a table

This updated page also has the VBA solution that arrives at the same solution. In this case VBA creates an output sheet. This update contains both solutions.

Option Explicit

Sub CONVERTROWSTOCOL2()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 
Set mr = Sheet2              'this is the name of the source sheet
Set ms = Sheets("Output")     'this is the name of the destiny sheet
col = 2
'End set the data
    With ms
     .UsedRange.ClearContents
     .Range("A1:C1").Value = Array("Name", "Class", "value")
    End With
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    With mr
          rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
          For i = 2 To rsht1
                Do While .Cells(1, col).Value <> ""
                rsht2 = rsht2 + 1
                ms.Range("A" & rsht2).Value = .Range("A" & i).Value
                ms.Range("B" & rsht2).Value = .Cells(1, col).Value
                ms.Range("C" & rsht2).Value = .Cells(i, col).Value
                col = col + 1
            Loop
            col = 2
        Next
        
    End With
    
  With ms
    .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    .Columns("A:C").EntireColumn.AutoFit
    End With
    
End Sub

@Maniacb

When offering coded solutions, either M Code or VBA, please post the relevant code to the thread for completeness. Thanks.

Unfortunately. PQ solutions are not appropriate for Excel for Mac users as it’s not supported In that version.

Not sure if you were able to leverage the vba code above, so I included a formula solution to come up with the same list. The formula are as follow

indy.png
at E90
=IFERROR(INDEX($A$2:$A$87,AGGREGATE(15,6,(ROW($A$2:$A$87)-ROW($A$2)+1)/($B$2:$Y$87<>""),ROWS(E$90:E90))),"")
at F90
=IFERROR(INDEX($B$1:$Y$1,AGGREGATE(15,6,(COLUMN($B$1:$Y$1)-COLUMN($B$1)+1)/($B$2:$Y$87<>"")/($A$2:$A$87=E90),COUNTIFS(E$90:E90,E90))),"")
at G90
=XLOOKUP(E90,A$1:$A$87,XLOOKUP(F90,$A$1:$Y$1,($A$1:$Y$87)))

and copy down about 2000 lines