Private Sub Worksheet_Activate()
Dim d1 As Object, d2 As Object, P As Range, ncol%, i&, lig&, x$, xlig&, n%
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare
Set P = Sheets("Sheet1").[A1].CurrentRegion
ncol = P.Columns.Count
Application.ScreenUpdating = False
Cells.Delete 'RAZ
For i = 2 To ncol: P(1, i) = P(1, i) & "µ": Next 'µ pour la numérotation
lig = 1
For i = 2 To P.Rows.Count
x = CStr(P(i, 1))
If Not d1.exists(x) Then
d1(x) = lig 'mémorise la ligne
Cells(lig + 1, 1) = x
lig = lig + 2
End If
xlig = d1(x) 'récupère la ligne
d2(x) = d2(x) + 1 'comptage
n = d2(x)
P(1, 2).Resize(, ncol - 1).Copy Cells(xlig, 2).Offset(, (ncol - 1) * (n - 1))
Cells(xlig, 2).Offset(, (ncol - 1) * (n - 1)).Resize(, ncol - 1).Replace "µ", n, xlPart
P(i, 2).Resize(, ncol - 1).Copy Cells(xlig + 1, 2).Offset(, (ncol - 1) * (n - 1))
Next
For i = 9 To UsedRange.Columns.Count Step ncol - 1: Columns(i).AutoFit: Next 'largeurs pour les adresses
P.Rows(1).Replace "µ", "", xlPart 'retire les µ
End Sub