Comptage des doublons

[erics83]

Bonjour,

J'essaye d'utiliser le code de BOISGONTIER "Fonction sansDoublons2col()" qui fonctionne magnifiquement bien....

Une petite question : comment compter le nombre de doublons ? dans l'exemple en ligne "F" comment compter le nombre de fois où "Dupont jean" apparait, je passe par une formule type "sommeprod", mais je pense qu'il y a plus rapide.....(surtout que j'ai un fichier de 10000 lignes et que sommeprod prend un peu de temps.....

En vous remerciant par avance,

 

[job75]

Bonjour laetitia90, heureux de te croiser, salut JB,

Excellent Laeti, tu travailles uniquement ton tableau de base en évitant ainsi les Application.Transpose qui n'acceptent qu'un maximum de 65536 lignes.

J'ai fait une comparaison entre nos 2 méthodes sur un tableau de 60 000 lignes.

En enlevant bien sûr mes Application.Trim mis uniquement pour enlever des espaces superflus.

- avec restitution de 8 valeurs uniques : Laeti => 0,48 s - job => 0,33 s

- avec restitution de 60000 valeurs uniques : Laeti => 1,53 s - job => 1,67 s

En terme de rapidité les 2 méthodes sont donc équivalentes.

A+

 

[erics83]

Merci JB, et spécial merci pour vos tutos qui sont une très grande source d'inspiration .....

Merci Job75 pour l'explication (nos post se sont encore croisés...)

Merci laetitia90, j'ai essayé et adapté votre code, cela fonctionne magnifiquement bien ;-) merci
une petite question (car j'aime bien comprendre la logique du code ), pourquoi (et/ou à quoi sert) :

x = x + 1
    t(x, 1) = t(i, 1): t(x, 2) = t(i, 2): m(t(i, 1) & t(i, 2)) = x

ok on additionne et on met en 3 le total, et sinon on met ce code, mais je ne comprends pas la logique....
Merci pour vos éclaircissements

Je profite de ce post aussi pour savoir où trouver des infos et/ou tutos pour mieux comprendre l'utilisation de

Set m = CreateObject("Scripting.Dictionary")

je vois qu'il existe des codes particuliers (comparemode, exists, etc...) et je n'ai rien trouvé (enfin j'ai surtout été noyé par les recherches...)

Dernière petite question : comme je le disais, j'ai un fichier de 10000 lignes et 50 colonnes, est-il possible de faire des combinaisons ? je m'explique : actuellement j'utilise un TCD permettant de trier/filtrer mes données et de réaliser des comptages, est-il possible de le faire via l'utilisation de "Scripting.Dictionary" ?
j'ai mis en PJ un exemple neutre avec des données Insee lol

En vous remerciant,

 

[job75]

Re,

J'ai testé aussi la fonction SansDoublons2ColNb de JB sur 60 000 lignes :

Sub JB()
t = Timer
[D2:F9].FormulaArray = "=SansDoublons2ColNb(A2:B60001)"
'[D2:F60001].FormulaArray = "=SansDoublons2ColNb(A2:B60001)"
MsgBox Timer - t
End Sub

- avec restitution de 8 valeurs uniques : 0,35 s

- avec restitution de 60000 valeurs uniques : 1,94 s.

A+

 

[job75]

Bonsoir erics83,

x = x + 1
t(x, 1) = t(i, 1): t(x, 2) = t(i, 2): m(t(i, 1) & t(i, 2)) = x

ok on additionne et on met en 3 le total

Laetitia est toujours très occupée, je réponds à sa place.

Ce code s'exécute quand il s'agit d'une nouvelle valeur (clé) du Dictionary.

Dans le tableau restitué une nouvelle ligne est créée et le n° de ligne est stocké dans le Dictionary.

Le comptage (addition) se fait dans l'autre cas en remplissant la colonne 3 de la ligne mémorisée.

A+

 

[erics83]

Merci job75, quand je pensais avoir compris, finalement je m’aperçois que je n'ai rien compris.... ou alors on dit la même chose mais à l'envers (ça m'arrive souvent....) : j'ai compris

If m.Exists(t(i, 1) & t(i, 2)) Then
    t(m(t(i, 1) & t(i, 2)), 3) = t(m(t(i, 1) & t(i, 2)), 3) + 1
    Else
    x = x + 1
    t(x, 1) = t(i, 1): t(x, 2) = t(i, 2): m(t(i, 1) & t(i, 2)) = x

si doublon identifié : If m.Exists(t(i, 1) & t(i, 2)) alors je compte (=j'additionne) sinon je créé nouvel enregistrement :
x = x + 1
t(x, 1) = t(i, 1): t(x, 2) = t(i, 2): m(t(i, 1) & t(i, 2)) = x,
mais c'est cette logique du nouvel enregistrement (qui, c'est clair nous donnera la liste finale) que je ne comprends pas....pourquoi t(x,1)=t(i,1) et m(t(i, 1) & t(i, 2)) = x ? : puisque x=1, il va remplacer t (1,1).....ou alors cela voudrait dire que "j'écrase" au fur et à mesure les enregistrements t( , ) ....?

Je suis assez débutant (enfin surtout autodidacte) vous l'aurez remarqué...mais j'aime bien comprendre.....et si vous avez des tutos (sur Dictionary notamment) suis très intéressé...... et concernant l'utilisation de Dictionary pour reproduire un TCD, possible ?

En vous remerciant pour votre aide,

 

[job75]

Re,

ou alors cela voudrait dire que "j'écrase" au fur et à mesure les enregistrements t( , ) ....?

Tout à fait, Laetitia aurait pu créer un 2ème tableau pour la restitution, mais comme elle est très économe elle n'en utilise qu'un seul.

Bonne fin de soirée.

 

[erics83]

Merci job75, alors finalement, j'avais compris....(à ma manière...) ça me rassure....

et concernant l'utilisation du Dictionary pour "refaire" et/ou "reproduire" un TCD ? (je ferais un nouveau post, car on dévie du sujet de base...) ?

En vous remerciant pour votre aide,

 

[job75]

Re,

Les TCD sont extrêmement rapides, une macro avec le Dictionary n'ira pas plus vite.

Je vous conseille donc de les utiliser si vous obtenez avec eux les résultats désirés.

A+

 

[erics83]

Merci job75,

Le souci avec les tcd, c'est l'actualisation : le fichier (il y a tout pleins de calculs qui facilitent la vie de mes collègues. .) sera utilisé au niveau national, il est composé de 2 feuilles : 1 avec toutes les données du département (dans mon cas, le Var), l'autre feuille, le tcd, avec en ligne le nom des collègues par exemple.
Donc si j'envoie mon fichier à mon "malchanceux" de collègue de Lille le Tcd ne fonctionnera pas... il va copier/coller dans la première feuille ses données, mais le tcd ne "reconnaitra" pas le nom des collègues...(il y a une macro qui tourne pour actualiser le tcd) d'où le fait d'envisager le dictionary..

Merci,
A+

 

[mapomme]

Bonsoir à tous ,

Pour le fun, un essai qui n'utilise ni tableau, ni dictionary. Malheureusement, ne fonctionne qu'à partir d'Excel 2007 (à cause de l'instruction RemoveDuplicates).
Niveau rapidité, je ne sais pas trop car ma bécane est vraiment obsolète (construite en 2007 ). Pour 60 000 lignes avec doublons, la durée d'exécution oscille entre 1,5 et 1,58 sec. Pour 60 000 lignes sans doublons, la durée d'exécution oscille autour de 1,13 sec.

Le code (assez court) :

Sub Doublon_2007Plus()
Dim der, t0
   t0 = Timer
   Application.ScreenUpdating = False
   Columns("a:b").Copy Columns("d")
   der = Cells(Rows.Count, "d").End(xlUp).Row
   Range("d1:f" & der).Sort key1:=[d1], order1:=xlAscending, key2:=[e1], _
                            order2:=xlAscending, Header:=xlYes
   Range("f2:f" & der).FormulaR1C1 = _
   "=IF(AND(RC[-2]=R[1]C[-2],RC[-1]=R[1]C[-1]),R[1]C+1,1)"
   Range("f2:f" & der) = Range("f2:f" & der).Value
   Range("d1:f" & der).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
   MsgBox Format$(Timer - t0, "#,##0.00\ sec.")
   Range("e1").Copy Range("f1"): Range("f1") = "Nbr"
End Sub

 

[erics83]

Merci mapomme,

Très intéressant comme code et logique. ..je ne connaissais pas cette fonction, mais la reutiliserai
En fait dans mon tableau de 50 colonnes je cherche les doublons en comparant la colonne À et S, d'où ma recherche d'utilisation de dictionary

En vous remerciant pour votre aide,

 

[gosselien]

Donc si j'envoie mon fichier à mon "malchanceux" de collègue de Lille le Tcd ne fonctionnera pas... il va copier/coller dans la première feuille ses données, mais le tcd ne "reconnaitra" pas le nom des collègues...(il y a une macro qui tourne pour actualiser le tcd) d'où le fait d'envisager le dictionary..

Merci,
A+

Bonjour,

Pourquoi le tcd ne les reconnaitrait pas ?
Si le collègue a 20 lignes à copier/coller , il insère 20 lignes avant la dernière du tableau (qui est la source du TCD) , il y colle ses données et puis il actualise le tcd...

P.

 

[erics83]

Merci gosselien,

ce que je voulais dire c'est que mon collègue de Lille va écraser les données de la feuil1 en mettant ses données, donc le TCD n'arrivera pas à "lire" les nouvelles infos...et ne sont pas aussi à l'aise avec l'actualisation (d'où les macros que je fais pour leur simplifier la vie (=ils ouvrent le classeur et tout se fait automatiquement : ouverture du fichier où se trouve les données, copie/colle dans mon classeur et exploitation des données)

En vous remerciant,

 

[gosselien]

Merci gosselien,

ce que je voulais dire c'est que mon collègue de Lille va écraser les données de la feuil1 en mettant ses données, donc le TCD n'arrivera pas à "lire" les nouvelles infos...et ne sont pas aussi à l'aise avec l'actualisation (d'où les macros que je fais pour leur simplifier la vie (=ils ouvrent le classeur et tout se fait automatiquement : ouverture du fichier où se trouve les données, copie/colle dans mon classeur et exploitation des données)

En vous remerciant,

Je connais ça, bcp de gens sont mal à l'aise avec les fichiers excel... Ma compagne est comme ça, je dois lui expliquer 10x
Mais un copier/coller dans une zone, si tu leur expliques ça devrait aller non ?
Tu peux mettre ton fichier de 50 colonnes (avec 20/30lignes) pour voir ce que ça donne ?

 

[erics83]

Merci gosselien,

en fait j'ai un dossier avec 2 classeurs : 1 fichier source ("Basedonnées") dont les données changent toutes les semaines (les collègues extraient les données de leur département en format excel et "écrasent" "Basedonnées" toutes les semaines (déjà là, il y en a qui sont en difficultés.... ). et il y a mon fichier ("Suivi"). A l'intérieur de "suivi", il y a mon code qui ouvre "Basedonnées" le colle dans une feuille et qui exploite les données. Le code s'exécute à l'ouverture, donc comme dit, j'essaye de simplifier l'utilisation.
Mon fichier "suivi" intéresse beaucoup de collègues, et c'est là qu'est le hic : il est avec "Basedonnées" de mon département, le TCD ne reconnait que les données de mon département. donc si j'envoie mon dossier à mes collègues, lorsqu'ils vont mettre "basedonnées" de leur département, le TCD ne pourra pas se mettre à jour automatiquement....d'où dictionary...et donc mon autre post....