Sub Ventiler()
Dim d As Object, tablo, resu(), n&, i&, x$, lig&, dest As Range
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
With Sheets("Feuil1") 'à adapter
tablo = .[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 2)
resu(1, 1) = "Code": resu(1, 2) = "Contact 1" 'titres
n = 1
For i = 2 To UBound(tablo)
x = tablo(i, 1)
If d.exists(x) Then
lig = d(x)
resu(lig, 2) = resu(lig, 2) & Chr(1) & tablo(i, 2) 'concaténation
Else
n = n + 1
d(x) = n 'mémorise le n° de ligne
resu(n, 1) = x
resu(n, 2) = tablo(i, 2)
End If
Next
'---restitution---
Application.ScreenUpdating = False
If .FilterMode Then .ShowAllData 'si la feuille est filtrée
Set dest = .[D1] '1ère cellule de destination, à adapter
dest.EntireColumn.Resize(, .Columns.Count - dest.Column + 1).ClearContents 'RAZ
dest(1, 3).Resize(, .Columns.Count - dest.Column - 1).Delete xlToLeft 'supprime les titres
dest.Resize(n, 2) = resu
dest(1, 2).Resize(n).TextToColumns dest(1, 2), xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
i = dest.CurrentRegion.Columns.Count
If i > 2 Then dest(1, 2).AutoFill dest(1, 2).Resize(, i - 1)
With .UsedRange: End With 'actualise la barre de défilement horizontale
End With
End Sub