Try
Sub Test()
Dim lr As Long
With ActiveSheet
lr = .Cells(Rows.Count, 1).End(xlUp).Row
ConvertData .Range("A1:D" & lr), .Range("H1")
End With
End Sub
Public Sub ConvertData(ByVal sourceRange As Range, ByVal targetCell As Range)
Const NAME_COL As Long = 2, MONTH_COL As Long = 3
Dim vName, vMonth, outputRange As Range, dicName As Object, dicMonth As Object, i As Long
Set dicName = CreateObject("Scripting.Dictionary")
Set dicMonth = CreateObject("Scripting.Dictionary")
For i = 2 To sourceRange.Rows.Count
If Not dicName.Exists(sourceRange(i, NAME_COL).Value) Then
dicName.Add sourceRange(i, NAME_COL).Value, dicName.Count + 1
End If
If Not dicMonth.Exists(sourceRange(i, MONTH_COL).Value) Then
dicMonth.Add sourceRange(i, MONTH_COL).Value, dicMonth.Count + 1
End If
Next i
Set outputRange = targetCell.Resize(dicName.Count + 1, dicMonth.Count + 2)
outputRange.Cells(1, 1).Value = "S"
outputRange.Cells(1, 2).Value = "Name"
For Each vMonth In dicMonth.Keys
outputRange.Cells(1, dicMonth(vMonth) + 2).Value = vMonth
Next vMonth
For Each vName In dicName.Keys
outputRange.Cells(dicName(vName) + 1, 1).Value = dicName(vName)
outputRange.Cells(dicName(vName) + 1, 2).Value = vName
For Each vMonth In dicMonth.Keys
For i = 2 To sourceRange.Rows.Count
If sourceRange(i, NAME_COL).Value = vName And sourceRange(i, MONTH_COL).Value = vMonth Then
outputRange.Cells(dicName(vName) + 1, dicMonth(vMonth) + 2).Value = sourceRange(i, 4).Value
Exit For
End If
Next i
Next vMonth
Next vName
End Sub