Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, d As Object, tablo, i&, x$
Set P = Intersect(Range("D3:E" & Rows.Count), UsedRange)
If P Is Nothing Then Exit Sub
Set d = CreateObject("Scripting.Dictionary")
tablo = [A2].CurrentRegion.Resize(, 3) 'matrice, plus rapide
For i = 2 To UBound(tablo)
x = tablo(i, 2)
If x <> "" Then d(x) = d(x) & Chr(1) & tablo(i, 1)
x = tablo(i, 3)
If x <> "" Then d(x) = d(x) & Chr(1) & tablo(i, 1)
Next
tablo = P 'matrice, plus rapide
For i = 1 To UBound(tablo)
x = tablo(i, 1)
If d.exists(x) Then tablo(i, 2) = Mid(d(x), 2) Else tablo(i, 2) = ""
Next
'---restitution---
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
P.Resize(, Columns.Count - P.Column + 1).ClearContents 'RAZ
P = tablo
P.Columns(2).TextToColumns P.Columns(2), xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
Application.EnableEvents = True 'réactive les évènements
End Sub