Salut,
Colles et testes ce code dans un module standard:
Sub Princ()
Dim I&, PLage As Range, T, Temp, Tablo()
Set PLage = Range([B2], [C65536].End(xlUp)) ' à adapter
T = RecupDoublons(PLage.Value, 1)
If IsArray(T) Then
ReDim Tablo(1 To UBound(T) + 1, 1 To 3)
For I = LBound(T) To UBound(T)
Temp = InverseTab(Equiv2(T(I), PLage.Value, 1))
Tablo(I + 1, 1) = T(I)
Tablo(I + 1, 2) = UBound(Temp) + 1 'Nbre de réfs
Tablo(I + 1, 3) = Somme(Temp, 1)
Next I
[C2].Resize(UBound(Tablo), UBound(Tablo, 2)) = Tablo 'à adapter
End If
End Sub
Function RecupDoublons(T, ColT As Byte) 'Zon
Dim I&, J&, Tablo As New Collection, Temp()
For I = LBound(T, 1) To UBound(T, 1)
On Error Resume Next
Tablo.Add T(I, ColT), CStr(T(I, ColT))
If Err = 0 Then
ReDim Preserve Temp(J)
Temp(J) = T(I, ColT)
J = J + 1
End If
Next I
RecupDoublons = Temp
End Function
Function Equiv2(ByVal RechS$, T, Col1 As Byte) 'Zon
Dim I&, J&, K&, Tablo, Temp()
For I = LBound(T) To UBound(T)
If T(I, Col1) = RechS Then
ReDim Preserve Temp(UBound(T, 2) - 1, J)
For K = 0 To UBound(T, 2) - 1
Temp(K, J) = T(I, K + 1)
Next K
J = J + 1
End If
Next I
Equiv2 = Temp
End Function
Function InverseTab(T, Optional Base As Byte = 0) 'Zon
Dim Temp(), I&, J&
ReDim Temp(Base To UBound(T, 2), Base To UBound(T))
For I = LBound(T, 2) To UBound(T, 2)
For J = LBound(T) To UBound(T)
Temp(I, J) = T(J, I)
Next J
Next I
InverseTab = Temp
End Function
Function Somme(T, ColS As Byte) 'Zon
Dim I&
For I = LBound(T) To UBound(T)
Somme = Somme + T(I, ColS)
Next I
End Function
A+++