Transpose dynamic values using Excel VBA in a loop

I think this function will work based on the sample data in your question. This assumes a few things ("Count" is the first column in the raw data, no blank "Char" fields, ect.), so I can help adjust it more specifically to your exact scenario if needed. We could also probably eliminate a few lines if everything is in one workbook and you don't need/want to set workbook and worksheet objects as variables.

Public Sub Transpose()

Dim Row1 As Long
Dim Column1 As Long
Dim Row2 As Long
Dim CurrentWB As Workbook
Dim CurrentWS As Worksheet
Dim TransposeWB As Workbook
Dim TransposeWS As Worksheet
Dim CurrentClass As String

'Set workbook(s) and worksheets as variables just to make it easier to reference them
'Replace necessary workbooks and worksheets with what holds your actual data
Set CurrentWB = ActiveWorkbook
Set CurrentWS = CurrentWB.Worksheets("Sheet1")
Set TransposeWB = ActiveWorkbook
Set TransposeWS = CurrentWB.Worksheets("Sheet2")

'Set the starting row for the worksheet in which the transposed data will go
Row2 = 1

'Adjust as needed for the row in which your raw data starts and ends
For Row1 = 1 To 1000

    'Store the current class
    CurrentClass = CurrentWS.Cells(Row1, 2)

    'Assuming first "Char" column is 3
    Column1 = 3

    While CurrentWS.Cells(Row1, Column1) <> ""

        TransposeWS.Cells(Row2, 1) = CurrentClass
        TransposeWS.Cells(Row2, 2) = CurrentWS.Cells(Row1, Column1)

        'Move to the next "Char" column in untransposed sheet
        Column1 = Column1 + 1

        'Move to the next row in transposed sheet
        Row2 = Row2 + 1
    Wend

Next Row1

End Sub