XL 2016 Détecter des doublons dans une colonne de 400.000 lignes

[Markoko]

Bonjour à tous,

je cherche une solution pour détecter les doublons dans une colonne de 400.000 lignes. Avec la formule ci : =SI(NB.SI($A$2:A10;A10)>1;"Doublon";" "). ça se plante

voici un exemple de résultat que j'aimerai avoir.

Merci pour votre précieuse aide

 

[Lolote83]

Bonjour MARLOKO,
Si l'utilisation des formules fait "planter" ton fichier, peut être faut-il passer par une macro VBA.
Mais vu le nombre de ligne, je ne suis pas sur que cela ne plantera pas le fichier. C'est simplement une autre piste.
Voir fichier joint
@+ Lolote83

 

[sylvanu]

Bonjour Markoko, et bienvenu sur XLD,
Votre formule est correcte, mais appliquée à 400 000 lignes, sur mon PC ça rame énormément et XL me dit que mes ressources sont trop faibles.
Ce n'est pas la formule qui plante ... mais votre PC. 🙂
Peut être une piste en VBA en PJ avec :

Option Explicit: Option Base 1
Sub Doublons()
    Dim DL, i, N, Tablo
    Application.ScreenUpdating = False: N = 1
    DL = Range("A1000000").End(xlUp).Row
    ReDim Tablo(DL)
    [B:B].ClearContents
    For i = 2 To DL
        If Application.CountIf(Range("A2:A" & i), Cells(i, "A")) > 1 Then Tablo(i) = "Doublon"
        N = N + 1
        If N = 100 Then
            Application.StatusBar = "Progression : " & i
            N = 0
        End If
    Next i
    Range("B1").Resize(UBound(Tablo), 1).Value = Application.Transpose(Tablo)
    Application.StatusBar = ""
End Sub

N ne sert à rien si ce n'est à montrer la progression dans le statusbar.
Mais même là ça rame, mais ne plante pas.
PS : Pensez à accepter les macros.

 

[eriiic]

Bonjour,

'ça plante ' ne veut pas dire grand chose.
Surtout pour une formule qui, si elle est valide, ne peut que le rester même en la recopiant sur des milliers de lignes.
Dans ce cas il faut donner des détails
eric

 

[pierrejean]

Bonjour à tous
Un essai

 

[eriiic]

Re,

une autre formule beaucoup plus légère qui donne le résultat en moins de 10s sur mon PC de 14 ans d'age.
En B2 :

=SI(EQUIV(A2;$A$1:$A$500000;0)=LIGNE();"";"Doublon")

eric

 

[Markoko]

Re,

une autre formule beaucoup plus légère qui donne le résultat en moins de 10s sur mon PC de 14 ans d'age.
En B2 :

=SI(EQUIV(A2;$A$1:$A$500000;0)=LIGNE();"";"Doublon")

eric

Merci Eriiic ça marche super bien et en moins de 7 secondes.

Re,

une autre formule beaucoup plus légère qui donne le résultat en moins de 10s sur mon PC de 14 ans d'age.
En B2 :

=SI(EQUIV(A2;$A$1:$A$500000;0)=LIGNE();"";"Doublon")

eric

 

[patricktoulon]

bonjour
identifier les doublons
en voilà une belle histoire
oui mais de quelle façon
formule non matricielle (à étirer vers le bas)
1° pointer les doubles triples etc ...
=REPT("doublon";NB.SI($A$2:$A$100;A16)>1)

ou

2° pointer seulement les suivants
=REPT("doublon";NB.SI($A$1:$A2;A2)>1)

démo

 

[patricktoulon]

re
la formule d'eriiic me donne tout les items comme doublons

 

[eriiic]

Bonjour,

??? Essaie encore 🙂
Si tu analyses la formule, tu vois que le 1er item ne peut retourner que ""
eric

 

[Markoko]

bonjour
identifier les doublons
en voilà une belle histoire
oui mais de quelle façon
formule non matricielle (à étirer vers le bas)
1° pointer les doubles triples etc ...
=REPT("doublon";NB.SI($A$2:$A$100;A16)>1)

ou

2° pointer seulement les suivants
=REPT("doublon";NB.SI($A$1:$A2;A2)>1)

démo
Regarde la pièce jointe 1130628

Merci Patricktoulon. Ta proposition avec les valeurs en triple aurait été idéal pour moi mais ca tourne sans cesse avec mes 389388 lignes

 

[Markoko]

re
la formule d'eriiic me donne tout les items comme doublons
Regarde la pièce jointe 1130629

Avec mes données la formule de Eriiic marche très bien si on fait un copier coller de la formule dans les autres cellules. Par contre sur ton image, je m'aperçois que tu l'as utilisé sous de formule matricielle. Peut être que le soucis vient de là.

 

[Markoko]

Bonjour,

??? Essaie encore 🙂
Si tu analyses la formule, tu vois que le 1er item ne peut retourner que ""
eric

Eriiic, ta formule marche bien avec mes données. Comment je fais si je veux avoir toutes les valeurs. Un peu du genre valeurs triples comme dans le cas de Patricktoulon

 

[eriiic]

Elle est conçue pour être efficiente à différencier la 1ère occurence des autres et rien de plus.
Seule la 1ère occurence n'est pas marquée, elle marque donc aussi les triplets etc.
S'il te faut les compter, tu dois repartir sur des solutions plus gourmandes sur les grandes plages ou par macro.
eric

 

[patricktoulon]

re
à tout hasard
les vrai uniques

Recherche & référence - UniqueSOLO

re me revoilà avec la petite UniqueSOLO dans la ligné de mes deux précédentes c'est une fonction de tri de doublons mais cette fois ci elle ne récupère que les one shot c'est à dire uniquement ceux qui apparaissent qu'une seule fois et pareil que pour la UNIQUEx on tri par la colonne 1 ou 2 en...

www.excel-downloads.com

les doubles,triples,etc

Recherche & référence - UNIQUEx

Bonjour a tous dans la lignée de DicoCountOrder ne l'ayant pas parce que je suis 2013 je vous propose aujourd'hui la petite( UNIQUEx) qui fait exactement ce que fait la fonction UNIQUE de 2016 et + elle travaille sur 2 ou 1 colonne dans la récupération et peut filtrer par la colonne 1 ou 2...

www.excel-downloads.com

et la DicoCountOrder

Recherche & référence - DicoCountOrder

Bonjour à tous je vous propose aujourd'hui ma fonction perso "DicoCountOrder" elle consiste à récupérer une colonne sans doublons mais en comptabilisant les doublons dans un tableau 2 colonnes(chaîne/nombres d’occurrence ) elle fonctionne en formule matricielle(à valider par CTRL+MAJ+ENTER...

www.excel-downloads.com

peut être q'une fonction perso en formule ira plus vite (à tester)