Option Explicit
Sub test()
Dim a, b(), i As Long, n As Long, w(), dico As Object
Set dico = CreateObject("Scripting.Dictionary")
dico.Comparemode = 1
With Sheets("Sheet1").Cells(1).CurrentRegion
a = .Value
ReDim b(1 To UBound(a, 1), 1 To 3)
For i = 1 To UBound(a, 1)
If Not dico.exists(a(i, 1)) Then
n = n + 1: dico.Item(a(i, 1)) = VBA.Array(n, 3)
b(n, 1) = a(i, 1)
b(n, 2) = a(i, 3)
b(n, 3) = a(i, 4)
Else
w = dico.Item(a(i, 1))
w(1) = w(1) + 2
If UBound(b, 2) < w(1) Then
ReDim Preserve b(1 To UBound(b, 1), 1 To UBound(b, 2) + 2)
End If
b(w(0), w(1) - 1) = a(i, 3)
b(w(0), w(1)) = a(i, 4)
dico.Item(a(i, 1)) = w
End If
Next
End With
Set dico = Nothing
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Restitution").Delete
Sheets.Add().Name = "Restitution"
On Error GoTo 0
With Sheets("Restitution").Cells(1).Resize(n, UBound(b, 2))
.CurrentRegion.Clear
.Columns(1).NumberFormat = "@"
.Value = b
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.VerticalAlignment = xlCenter
.Font.Name = "calibri"
.Font.Size = 9
With .Cells(1, 2).Resize(, 2)
.Cells(1, 1).Value = .Cells(1, 1).Value & " 1"
.Cells(1, 2).Value = .Cells(1, 2).Value & " 1"
If UBound(b, 2) > 3 Then
.AutoFill .Resize(, UBound(b, 2) - 1)
End If
End With
With .Rows(1)
.BorderAround Weight:=xlThin
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 37
.RowHeight = 18
.Font.Size = 10
End With
.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub