XL 2016 Supprimer les doublons (avec 4 colonnes) et ne garder que la ligne la plus ancienne (colonne A)

[Loïc DUBOIS]

Bonjour à tous,

J'ai besoin de faire une manipulation me permettant de supprimer les doublons selon 4 colonnes et ne garder que la ligne avec la date la plus ancienne.

Je m'explique, j'ai un fichier excel avec environ 21k lignes. Parmis ces lignes se trouve des doublons. Or je veux les supprimer en ne gardant que le doublon le plus ancien. Je caractérise les doublons sur plusieurs colonnes. Un doublon est avéré lorsque la colonne "Année" (B), "Personne" (D), "Fonction" (H), RIC (L).

Pour résumer, pouvez-vous m'aider à supprimer les doublons lorsque les 4 colonnes ci-dessus sont identiques et ne garder uniquement la ligne la plus ancienne (colonne A) ?

Je vous joint un fichier exemple : dans ce fichier j'ai des doublons avec "Amazon" (colonne B, D, H et L identiques mais je souhaite ne garder que la ligne avec la date la plus ancienne (colonne A).

Je vous remercie par avance,

Bien cordialement,

Loïc DUBOIS

 

[sylvanu]

Bonjour Loïc,
Un essai en PJ avec :

   Sub Nettoyage()
    Application.ScreenUpdating = False
    TrParNom
    DL = Range("A1000000").End(xlUp).Row                    ' Dernière ligne
    With ActiveSheet.Range(Cells(2, "ZZ"), Cells(DL, "ZZ")) ' Colonne ZZ utilisée pour les filtrages
        .FormulaLocal = "=SI(NB.SI($C$1:C2;C2)=1;CAR(1);0)"
        .Value = .Value                                     'Supprime les formules
        .EntireRow.Sort .Cells, xlDescending                'Tri pour regrouper et accélérer
        On Error Resume Next                                'Si aucune SpecialCell
        .SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
        .EntireColumn.Delete                                'Supprime la colonne auxiliaire
    End With
    ActiveSheet.UsedRange       ' On redimensionne le curseur horizontal
    ActiveSheet.Columns.AutoFit ' On redimensionne largeur colonnes
    [A1].Select
    MsgBox "Nombre de lignes au début : " & DL - 1 & Chr(10) & _
            "Nombre de lignes au fin       : " & Range("A1000000").End(xlUp).Row - 1
End Sub
Sub TrParNom()
    DL = Range("A1000000").End(xlUp).Row
    Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C" & DL) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & DL) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:ZZ" & DL)
        .Header = xlYes
        .MatchCase = False
        .Apply
    End With
    Range("A1").Select
End Sub

J'utilise la colonne ZZ pour calculer les plus anciens.

 

[vgendron]

Hello
un test par macro
le tri final se fait sur la colonne B==> à modifier selon ta convenance: avec les commentaires tu devrais pouvoir adapter

Sub SupprimerDoublons()
'
' Macro1 Macro
Dim TabData() As Variant

With ActiveSheet

    LastLine = .UsedRange.Rows.Count 'dernière ligne non vide de la colonne A
    'LastCol = .UsedRange.Columns.Count
    Set ZoneATrier = .UsedRange 'toute la base de donnée avec ligne d'entete
    
    .Sort.SortFields.Clear 'on supprime tout tri eventuel
    .Sort.SortFields.Add Key:=Range("B2:B" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on trie sur la colonne B
    .Sort.SortFields.Add Key:=Range("D2:D" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on trie sur la colonne D
    .Sort.SortFields.Add Key:=Range("H2:H" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on trie sur la colonne H
    .Sort.SortFields.Add Key:=Range("L2:L" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on trie sur la colonne L
    .Sort.SortFields.Add Key:=Range("A2:A" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'on trie sur la colonne A
    With .Sort 'on applique le tri
        .SetRange ZoneATrier
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
     
    TabData = .UsedRange.Value 'on met tout dans un tablo vba
    For i = LBound(TabData, 1) + 1 To UBound(TabData, 1) - 1 'pour chaque ligne (hors entete)
        If TabData(i + 1, 2) = TabData(i, 2) And TabData(i + 1, 3) = TabData(i, 3) And TabData(i + 1, 8) = TabData(i, 8) And TabData(i + 1, 12) = TabData(i, 12) Then 'si on a un doublon
            For j = LBound(TabData, 2) To UBound(TabData, 2) 'on efface la ligne (le tri étant aussi sur la colonne A, celle qu'on efface est forcément postérieure
                TabData(i + 1, j) = ""
            Next j
        End If
    Next i
    .UsedRange.Clear 'on efface la feuille
    .Range("A1").Resize(UBound(TabData, 1), UBound(TabData, 2)) = TabData 'on colle le tableau
    .Sort.SortFields.Clear 'on reapplique un tri sur la colonne B ==> les lignes vides se retrouvent en bas
    .Sort.SortFields.Add Key:=Range("B2:B" & LastLine), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     With .Sort
        .SetRange ZoneATrier
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

 

[Loïc DUBOIS]

Merci beaucoup pour vos réponses.

Je vais tester tout ça et je reviens vers vous.

Loïc

 

[sylvanu]

Re Loïc, Vgendron,
@Loïc, doit on garder toutes les lignes les plus anciennes ou simplement une seule si elles sont toutes identiques ?
Par ex Aberdeen 11 lignes sont strictement identiques ( à la date la plus ancienne 26/01/2018 ), je n'en ai gardé qu'une seule.

 

[Loïc DUBOIS]

Sylvanu,

En faite je souhaite ne garder qu'une seule ligne. (Ex : Si colonna A, B, C et D sont identiques sur 3 lignes alors je ne garde que la ligne avec la date la plus ancienne).

Je te remet un autre fichier avec pour exemple Amazon.
Il s'avère qu'il y a 7 lignes identiques (colonne B, D, H et L) (feuille 1). Après manipulation il faut qu'il reste uniquement une ligne parmis ces 7 lignes et celle qui doit rester est la plus ancienne (colonne A) (feulle 2).

Voila j'espère avoir été plus clair.

Merci d'avance,

Loïc

 

[djidji59430]

Bonjour à tous,

Dans le tableau, on trie les dates, puis supprimer les doublons en suivant les critéres.
Pourquoi faire simple, si ........

Crdlmt

 

[Amilo]

Bonjour à tous,

Voici une proposition Power query avec le fichier du message #6

P.S : il y a des colonnes sans titre ou répétitions de nom ("Fonction")

Cordialement

 

[sylvanu]

Re,

Je te remet un autre fichier avec pour exemple Amazon.

Donc ma PJ fonctionne. Vous l'avez testé ?

 

[Loïc DUBOIS]

Re,

Donc ma PJ fonctionne. Vous l'avez testé ?

Re,

Merci pour le temps que vous m'accordez. En revanche je vous avoue que je n'ai pas l'impression que cela fonctionne. En effet, lorsque je clique sur le bouton, j'ai 20 lignes qui se supprime dont tous les doublons (par exemple les doublons avec la société Amazon disparaissent tous, il ne reste pas la ligne avec la date la plus ancienne).

 

[Loïc DUBOIS]

Bonjour à tous,

Voici une proposition Power query avec le fichier du message #6

P.S : il y a des colonnes sans titre ou répétitions de nom ("Fonction")

Cordialement

Merci beaucoup, pour le temps que vous m'accordez.

En revanche, pouvez-vous m'expliquer comment cela fonctionne car lorsque j'ouvre le fichier je vois que le fichier est maintenant un tableau "bleu" et que le résultat est un tableau "vert" mais comment puis-je appliquer votre solution sur tout un fichier avec 21k lignes dont 10k sociétés distinctes ?

Merci d'avance.

Loïc

 

[sylvanu]

Avec la PJ #2, j'obtiens ça :

Il y a bien Amazon de 2004.

 

[Loïc DUBOIS]

Avec la PJ #2, j'obtiens ça :
Regarde la pièce jointe 1141716
Il y a bien Amazon de 2004.

Oui, je suis d'accord avec vous, le Amazon de 2004 avec Mme Myrtle Potter est présent, cependant le Amazon de 2019 avec Indrz Nooyi n'est plus présent.

En faite, je ne souhaite pas supprimer toutes les lignes avec Amazon et ne garder que la plus ancienne. Mais je souhaite supprimer les lignes identiques (l'identique se fait sur 4 colonnes, il faut que ce soit la même société, la même personne, la même fonction et le même RIC et si c'est 4 colonnes sont identiques sur plusieurs lignes alors nous supprimons les doublons et ne gardons qu'une seule ligne pour garder la plus ancienne) (je m'excuse si je n'ai pas été clair au début).

Merci d'avance,

Loïc

 

[vgendron]

as tu essayé ma proposition?

 

[Loïc DUBOIS]

as tu essayé ma proposition?

Oui je viens de tester.
Cela fonctionne mais pas totalement. Par exemple, après application du code, certaines lignes se sont supprimées cependant il en reste toujours à supprimer (voir photo). J'avoue que je ne sais pas pourquoi alors que les colonnes dans le code sont les bonnes.