Private Sub Worksheet_Change(ByVal Target As Range)
Dim source As Range, dest As Range, t, d As Object, i&, a, b
Set source = [A2] 'à adapter
Set dest = [A22] 'à adapter
Set source = Intersect(Target, source.CurrentRegion, Me.UsedRange)
If source Is Nothing Then Exit Sub
t = source.CurrentRegion 'matrice, plus rapide
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(t)
d(t(i, 1)) = d(t(i, 1)) + t(i, 2)
Next
Application.ScreenUpdating = False
dest.Resize(Rows.Count - dest.Row + 1, 2).ClearContents 'RAZ
If d.Count Then
'---transposition---
a = d.keys: b = d.items
ReDim t(UBound(a), 1)
For i = 0 To UBound(a)
t(i, 0) = a(i)
t(i, 1) = b(i)
Next
'---restitution et tri alphabétique---
dest.Resize(i, 2) = t
dest.Resize(i, 2).Sort dest, xlAscending, Header:=xlNo
End If
End Sub