Bonsoir à tous,
Je m'appuie sur la 4 éme colonne pour déterminer les clés.
De ton côté, comment définis-tu ces doublons, cela m'a l'air bien compliqué
Les expressions réguliéres (RegExp) sont très utiles dans ce cas, mais je ne sais pas faire
A tester :
Option Explicit
Sub test()
Dim dico As Object, i As Long, e, n As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets(1).Range("a1").CurrentRegion 'Feuil2
For i = 2 To .Rows.Count
If Not dico.exists(.Cells(i, 4).Value) Then
If i = 2 Then
Set dico(.Cells(i, 4).Value) = Union(.Rows(1), .Rows(i))
Else
Set dico(.Cells(i, 4).Value) = .Rows(i)
End If
Else
Set dico(.Cells(i, 4).Value) = _
Union(dico(.Cells(i, 4).Value), .Rows(i))
End If
Next
End With
'Restitution
With Sheets(2) 'Feuil3
.Cells.Clear
For Each e In dico
n = n + 1
dico(e).Copy
'.Cells(n, 1).PasteSpecial xlPasteColumnWidths
.Cells(n, 1).PasteSpecial xlPasteValues
With .Cells(n, 1).CurrentRegion
With .Offset(.Rows.Count).Resize(1, 9)
If n = 1 Then
.Columns("a:i").Value = _
Array("", "", "", "TOTAL " & e, "=sum(r" & n + 1 & "c:r[-1]c)", "", "", _
"=sum(r" & n + 1 & "c:r[-1]c)", "")
Else
.Columns("a:i").Value = _
Array("", "", "", "TOTAL " & e, "=sum(r" & n & "c:r[-1]c)", "", "", _
"=sum(r" & n & "c:r[-1]c)", "")
End If
End With
n = n + .Rows.Count + 1
End With
Next
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
klin89