En attendant PowerQ, voici une solution hyper rapide sur une grosse BdD
Sub der()
Dim tbl, dico As Object, dicostatut As Object
tbl = [tableau1].Value
Set dico = CreateObject("Scripting.Dictionary")
Set dicostatut = CreateObject("Scripting.Dictionary")
For a = LBound(tbl) To UBound(tbl)
If dico.Exists(tbl(a, 1) & "|" & tbl(a, 2)) Then
If dico(tbl(a, 1) & "|" & tbl(a, 2)) < tbl(a, 3) Then
dico(tbl(a, 1) & "|" & tbl(a, 2)) = tbl(a, 3)
dicostatut(tbl(a, 1) & "|" & tbl(a, 2)) = tbl(a, 4)
End If
Else
dico(tbl(a, 1) & "|" & tbl(a, 2)) = tbl(a, 3)
dicostatut(tbl(a, 1) & "|" & tbl(a, 2)) = tbl(a, 4)
End If
Next a
' début du résultat
ligne = 17: colonne = 7
For Each cle In dico
Cells(ligne, colonne) = Split(cle, "|")(0)
Cells(ligne, colonne + 1) = Split(cle, "|")(1)
Cells(ligne, colonne + 2) = dico(cle)
Cells(ligne, colonne + 3) = dicostatut(cle)
ligne = ligne + 1
Next
End Sub