Option Explicit
Sub fusion()
'fusion des doublons colonnes J et D par rapport à la colonne H
Dim a, i As Long, j As Long, n As Long, dico As Object, w
With Sheets("Feuil1").Range("A1").CurrentRegion
a = Application.Index(.Value, Evaluate("row(1:" & _
.Rows.Count & ")"), Array(8, 10, 4))
End With
n = 1
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
n = n + 1
a(n, 1) = a(i, 1)
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
For j = 2 To UBound(a, 2)
a(n, j) = a(i, j)
If a(i, j) <> "" Then dico(a(i, j)) = Empty
Next
.Item(a(i, 1)) = VBA.Array(n, dico)
Else
w = .Item(a(i, 1))
For j = 2 To UBound(a, 2)
If a(i, j) <> "" And Not w(1).exists(a(i, j)) Then
a(w(0), j) = a(w(0), j) & ", " & a(i, j)
w(1)(a(i, j)) = Empty
End If
Next
.Item(a(i, 1)) = w
End If
Next
End With
Application.ScreenUpdating = False
With Sheets("Feuil2").Cells(1)
.CurrentRegion.Clear
With .Resize(n, UBound(a, 2))
.Value = a
.Font.Name = "calibri"
.Font.Size = 10
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlInsideVertical).Weight = xlThin
.BorderAround Weight:=xlThin
With .Rows(1)
.Font.Size = 11
.Interior.ColorIndex = 38
.BorderAround Weight:=xlThin
End With
.Columns.AutoFit
End With
.Parent.Select
End With
Application.ScreenUpdating = True
End Sub