Option Explicit
Sub test()
Dim a, b(), i As Long, n As Long, t As Long, dico As Object
Set dico = CreateObject("Scripting.Dictionary")
a = Sheets("Feuil1").Range("a1").CurrentRegion.Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 1))
n = 1: t = 1: b(1, 1) = "Nom"
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 1)) Then
n = n + 1: dico(a(i, 1)) = n
b(n, 1) = a(i, 1)
End If
If Not .exists(a(i, 2)) Then
t = t + 1: .Item(a(i, 2)) = t
b(1, t) = a(i, 2)
End If
b(dico(a(i, 1)), .Item(a(i, 2))) = a(i, 3)
Next
End With
Application.ScreenUpdating = False
'restitution et mise en forme
With Sheets("Feuil2")
.Cells.Clear
With .Cells(1).Resize(n, t)
.Value = b
.Font.Name = "calibri"
.Font.Size = 10
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
With .Rows(1)
.BorderAround Weight:=xlThin
With .Offset(, 1).Resize(, .Columns.Count - 1)
.Interior.ColorIndex = 36
.Font.Bold = True
End With
End With
With .Columns(1)
With .Offset(1).Resize(.Rows.Count - 1)
.Interior.ColorIndex = 38
End With
End With
.Columns.ColumnWidth = 12
End With
.Activate
End With
Application.ScreenUpdating = True
End Sub