Une fonction (VBA) qui repère s'il y a un ou des doublons

[Magic_Doctor]

Bonsoir,

Je suis à la recherche d'une fonction qui, comme il est stipulé dans le titre du fil, recherche, dans une plage de cellule, s'il y a ou pas un ou des doublons. Supposons que nous nommions cette fonctions "Doublons" :

Sub Function Doublons (plage As Range) As Boolean

	blablabla

End Function

La fonction renvoyant uniquement "True" ou "False" suivant qu'il y ait ou pas un ou des doublons.

Merci pour toute aide.

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Re,

Les solutions que j'ai proposées ne tenaient pas compte de la casse.

Cette solution (4) la prend maintenant en compte, avec cette formule matricielle en D2 :

=SIGNE(SOMME((Plage<>"")*EXACT(Plage;TRANSPOSE(Plage)))-NBVAL(Plage))

transcrite en VBA :

Function Doublons(plage As Range) As Byte
Dim a$
a = plage.Address
Doublons = Sgn(Evaluate("SUM((" & a & "<>"""")*EXACT(" & a & ",TRANSPOSE(" & a & ")))-COUNTA(" & a & ")"))
End Function

A+

 

[Magic_Doctor]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour ROGER & job & le forum,

Merci ROGER pour tes explications qui certainement me serviront.
Bravo job pour la concision. Il est vrai, qu'en VBA, on oublie parfois les fonctions que nous propose Excel.

Bonne journée à todos.

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Re,

Pour finir, ce fichier (5) fait la synthèse des fichiers (3) et (4).

Il y a une liste de validation en C1.

A+

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour Magic_Doctor, le forum,

Par curiosité j'ai testé les durées d'exécution des formules Excel sur Win 7 - Excel 2010.

1) Fichier (3), la casse n'est pas prise en compte :

- plage de 1000 lignes => 0,14 s
- plage de 2000 lignes => 0,58 s
- plage de 5000 lignes => 3,4 s
- plage de 10000 lignes => 13,5 s

2) Fichier (4), la casse est prise en compte :

- plage de 1000 lignes (matrice 1000 x 1000) => 0,18 s
- plage de 2000 lignes (matrice 2000 x 2000) => 0,56 s
- plage de 5000 lignes (matrice 5000 x 5000) => ressources Excel insuffisantes

Curieusement les fonctions VBA (avec Evaluate) prennent 2 fois plus de temps.

A+

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Re,

Le manque de ressources Excel est comparable à celui-ci :

Sub test()
Dim t() As Boolean
ReDim t(2000, 2000) 't(5000, 5000)
ThisWorkbook.Names.Add "tab", t
End Sub

La matrice du nom "tab" ne se crée pas pour 5000 x 5000.

A+

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Re,

Pour contourner le manque de ressources du fichier (4), j'ai découpé la plage en 10 morceaux.

Sur 10000 lignes la nouvelle formule s'exécute en 15,6 secondes.

Fichier joint.

Edit : l'ouverture du fichier prend aussi 15 secondes...

A+

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Re,

En conclusion des tests précédents nous pouvons dire que :

- la formule Excel avec SOMMEPROD/NB.SI est à la rigueur acceptable pour la recherche de doublons sans la casse

- la formule matricielle ne l'est pas du tout avec la casse sur de grands tableaux

- donc dans tous les cas il vaut mieux utiliser des fonctions VBA.

Voyez le fichier (6) avec les 2 fonctions identiques DoublonsSansCasse et DoublonsAvecCasse.

Elles doivent être dans des modules différents car la 1ère nécessite Option Compare Text.

Sur 10000 lignes chacune de ces fonctions s'exécute chez moi en 4,5 secondes.

A+

 

[ROGER2327]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour à tous, bonjour job75.

Vos fonctions du dernier fichier (Doublons(6).xls) s'exécutent chez moi en 3,4s.

On peut aller un chouïa plus vite avec :​

Function Roger3(Plage As Range, Optional Casse As Boolean) As Boolean
'Nécessite l'ajout de la bibliothèque Microsoft Scripting Runtime au projet.
'Ignore les cellules vides.
Dim i&, v(), Dic As New Dictionary
    Dic.CompareMode = -Not Casse
    v = Plage.Value
    On Error GoTo E
    For i = 1 To UBound(v)
        If Not IsEmpty(v(i, 1)) Then Dic.Add CStr(v(i, 1)), True
    Next i
Exit Function
E:  Roger3 = True
End Function

ou :​

Function RogerV3(Plage As Range, Optional Casse As Boolean) As Boolean
'Nécessite l'ajout de la bibliothèque Microsoft Scripting Runtime au projet.
'N'ignore pas les cellules vides.
Dim i&, v(), Dic As New Dictionary
    Dic.CompareMode = -Not Casse
    v = Plage.Value
    On Error GoTo E
    For i = 1 To UBound(v)
        Dic.Add CStr(v(i, 1)), True
    Next i
Exit Function
E:  RogerV3 = True
End Function

Mode d'emploi :

=Roger3(Plage;Booléen)

où Booléen est
VRAI (ou 1) pour tenir compte de la casse ;
FAUX (ou 0 ou omis) pour n'en pas tenir compte.

Sur votre exemple de données dans Doublons(6).xls : 0,026 s avec Roger3.
(0,008 s avec RogerV3.)​

Bonne nuit.


ROGER2327
#6791

Samedi 21 Phalle 140 (Erbrand, polytechnicien - fête Suprême Tierce)
14 Fructidor An CCXXI, 0,5228h - noix
2013-W35-6T01:15:17Z

 

[mapomme]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour Magic_Doctor, ROGER2327, job75, à tous,

Juste pour le fun:

Deux fonctions (avec ou sans prise en compte de la casse) qui n'utilisent pas de Dictionary (pour les réfractaires à cet objet 😱 ) et qui se limitent à une plage d'une seule colonne.

Un fichier de TEST qui permet de choisir les options désirées (cellules C1 à C3) et d'afficher les temps d'exécution:

• Avec ou sans prise en compte de la casse
• Taille de la liste: de 1000 à 300 000 (de 1000 à 50 000 pour le .xls)
• La position d'un doublon (sans aucun doublon, doublon en tête de liste, au milieu, à la fin, etc.)
• Utilise les fonctions de job75, ROGER2327 et mapomme.

Mode d'emploi: sélectionner les options (cellules C1 à C3) puis cliquer sur le bouton noir.

Résultat:
La fonction de ROGER2327 est très rapide. Dans quelques rare cas, la fonction MAP semble aller un tout petit peu plus vite (grande liste -> ex: >=200 000 éléments, distinction de la casse et doublon plutôt en fin de liste)

L'inconvénient des fonctions sans dictionary est la nécessité de les dédoubler pour bénéficier de l'option Compare.

Donc la fonction de ROGER2327 par sa rapidité, sa concision, sa capacité de gérer les plages multiples semble s'imposer tout naturellement.

En espérant ne pas m'être planté...

nota: on ne tient pas compte des cellules vides

 

[ROGER2327]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour à tous.

Je n'ai pas encore pris le temps de décortiquer les dernières contributions, mais il me semble qu'on peut contourner Option Compare ....

Par exemple unifier DoublonsJOBAvecCasse et DoublonsJOBSansCasse en écrivant :​

Function DoublonsJOB(plage As Range, Optional Casse As Boolean) As Byte
Dim t, ub&, x, i&, j&
t = plage 'matrice, plus rapide
ub = UBound(t)
For i = 1 To ub - 1
  x = t(i, 1)
  If x <> "" Then
    For j = i + 1 To ub
      If StrComp(t(j, 1), x, -Not Casse) = 0 Then DoublonsJOB = True: Exit Function
    Next
  End If
Next
End Function

=DoublonsJOBAvecCasse(Plage) devient =DoublonsJOB(Plage;VRAI) (ou =DoublonsJOB(Plage;1))

=DoublonsJOBSansCasse(Plage) devient =DoublonsJOB(Plage;FAUX) (ou =DoublonsJOB(Plage;0) ou simplement =DoublonsJOB(Plage)).

A priori, cela devrait fonctionner aussi bien avec​

Option Compare Binary

qu'avec​

Option Compare Text

À vérifier ! Et reste à voir ce que cela donne en vitesse d'exécution... (Je n'ai pas eu le temps de tester, j'espère ne pas me gourer...)​

Bon dimanche.


ROGER2327
#6792

Dimanche 22 Phalle 140 (Dragonne, pyrophage - fête Suprême Seconde)
15 Fructidor An CCXXI, 0,8427h - truite
2013-W35-7T02:01:21Z

 

[Magic_Doctor]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonsoir à tous,

Je me suis amusé à faire une synthèse, dans une même fonction, des 2 solutions de ROGER dans le post #23.
Sur la PJ c'est très clair. Ma foi, peut-être utile pour un pélerin qui passerait par là...

Function Doublons(Plage As Range, Optional CelVides As Boolean, Optional Casse As Boolean) As Boolean
'Vérifie s'il y a ou pas un ou des doublons (avec certaines conditions) dans une plage de cellules
'- Plage : plage de cellules où l'on recherche des doublons
'- CelVides : CelVides = False (ou omis) --> ignore les cellules vides
'             CelVides = True --> n'ignore pas les cellules vides
'- Casse : Casse = False (ou omis) --> ignore la casse : 2 items en majuscule ou minuscule seront considérés comme identiques (ex : a = A)
'          Casse = True --> n'ignore pas la casse : 2 items en majuscule ou minuscule seront considérés comme différents (ex : a <> A)
'ROGER2327
    Dim i&, v(), Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = -Not Casse
    v = Plage.Value
    On Error GoTo E
    For i = 1 To UBound(v)
        If CelVides = True Then
            Dic.Add CStr(v(i, 1)), True
        Else
            If Not IsEmpty(v(i, 1)) Then Dic.Add CStr(v(i, 1)), True
        End If
    Next i
    Exit Function
E:  Doublons = True
End Function

En espérant ne pas avoir fait de pataquès...

 

[Magic_Doctor]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Rebonsoir,

Je n'avais pas vu le dernier post de ROGER. J'ai incorporé sa nouvelle fonction dans mon classeur. Elle semble très bien marcher. En revanche, je ne saisis pas pourquoi cette fonction a le "suffixe" "Byte". Dans mon classeur je l'ai mise en "Boolean".

Buen domingo para todos.

 

[mapomme]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour à tous,

J'ai tenté d'inclure le STRCOMP dans les fonctions de job75 et mapomme. Le résultat est un accroissement sensible des temps d'exécution (toujours si je ne me suis pas planté). Donc la fonction de ROGER2327 reste, selon moi, la meilleure.

Voir versions v2 avec strcomp.

 

[job75]

Re : Une fonction (VBA) qui repère s'il y a un ou des doublons

Bonjour Magic_Doctor, Roger, mapomme,

Je savais bien que Dictionary est incontournable pour la recherche des doublons.

Mais je ne connaissais pas sa propriété CompareMode.

Pas plus que la fonction StrComp d'ailleurs, merci Roger de nous les avoir fait découvrir.

Noter que StrComp rend ma macro très lente car elle est recalculée à chaque test.

A+