lister les villes de meme nom a partir d'une base de données

M

matou59

XLDnaute Junior
Bonjour,
A partir de ma base de donnees composee de 6 colonnes comme suit:
A B C D E F
NOM VILLE CODE POSTAL N° DEPARTEMENT NOM DEPARTEMENT CHEF LIEU NOM REGION

CE QUI REPRESENTE ENVIRON 35000 ENREGISTREMENTS.......

JE VOUDRAIS LISTER TOUS LES ENREGISTREMENTS DONT LES NOMS DE VILLE SONT IDENTIQUES

ma base de donnees se trouve dans une feuille d'un classeur appelée VILLES
Je veux lister les enregistrements identiques dans une 2e feuille du meme classeur appelée RESULTAT

Faire un filtre sur la base de données est beaucup trop long....35000 enregistrements c'est pas rien....

merci d'avance
 
Trier par date Trier par votes
D

david84

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Bonjour à tous,
un essai (que Michel et Job ou un autre n'hésite pas à me corriger si besoin) :
Code: 
Sub Doublon_Villes()
Dim a As Range, DerLigneVilles As Long, DerLigneResultat As Long, test As String, cel As Range
Application.ScreenUpdating = False
Sheets("resultat").Range("A:F").Delete
    With Sheets("villes")
        DerLigneVilles = .Range("A" & (.Rows.Count)).End(xlUp).Row
    Set a = .Range("A1:A" & DerLigneVilles)
    End With
    With Sheets("resultat")
        DerLigneResultat = .Range("A" & (.Rows.Count)).End(xlUp).Row
    End With

    For Each cel In a
    test = Application.CountIf(a, cel)
    If test > 1 Then
        cel.EntireRow.Copy Destination:=Range("A" & DerLigneResultat & ":" & "F" & _
        DerLigneResultat)
        DerLigneResultat = DerLigneResultat + 1
    End If
    Next cel

    With Sheets("resultat")
        .Range("A1:F" & DerLigneResultat).Sort Key1:=.[A1], Order1:=xlAscending, Header:=xlYes 'tri sur les noms
    End With
Application.ScreenUpdating = True
End Sub
A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re matou59,

matou59 à dit:
Pas grave le plus gros est fait...merci encore
Cliquez pour agrandir...

Si c'est grave, car je pense que vous avez modifié ma macro...

Vous avez probablement remplacé 65536 par 40 (la limite du tableau de la 1ère feuille).

Il faut donc conserver 65536, ou au pire le remplacer par 42 !

Nota : 65536 ne ralentit pas du tout la macro, qui est quasiment instantanée.

Edit : salut david84

A+
 
Dernière édition:
Vote positif 0 Vote négatif
BOISGONTIER

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Re : lister les villes de meme nom a partir d'une base de données

Bonjour,

0,92 seconde pour 15.000 lignes et 300 doublons

Code: 
Sub jb()
  Set f1 = Sheets("villes")
  Set f2 = Sheets("resultat")
  Set champ = f1.Range("A3:A" & f1.[A65000].End(xlUp).Row)
  Set mondico = CreateObject("Scripting.Dictionary")
  f2.[A1:F100].ClearContents
  For Each c In champ
    mondico.Item(c.Value) = mondico.Item(c.Value) + 1
  Next c
  ligne = 1
  For Each c In champ
    If mondico.Item(c.Value) > 1 Then
      c.Resize(, 6).Copy f2.Cells(ligne, 1)
      ligne = ligne + 1
    End If
  Next c
  f2.[A1].CurrentRegion.Sort Key1:=f2.[A1], Order1:=xlAscending, Header:=xlNo
End Sub

Méthode rapide

0,25 seconde pour 15.000 lignes et 300 doublons

Code: 
Sub jb()
  Set f1 = Sheets("villes")
  Set f2 = Sheets("resultat")
  Set champ = f1.Range("A3:F" & f1.[F65000].End(xlUp).Row)
  a = champ.Value
  Set mondico = CreateObject("Scripting.Dictionary")
  f2.[A1:F100].ClearContents
  For i = 1 To UBound(a)
    mondico.Item(a(i, 1)) = mondico.Item(a(i, 1)) + 1
  Next i
  ligne = 1
  Dim c()
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If mondico.Item(a(i, 1)) > 1 Then
      For k = 1 To UBound(a, 2): c(ligne, k) = a(i, k): Next k
      ligne = ligne + 1
    End If
  Next i
  f2.[A1].Resize(mondico.Count, UBound(a, 2)) = c
  f2.[A1].CurrentRegion.Sort Key1:=f2.[A1], Order1:=xlAscending, Header:=xlNo
End Sub

http://boisgontierjacques.free.fr/fichiers/Cellules/ListeVillesEnDoublon.xls

JB
 
Dernière édition:
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re, Bonjour David, Jacques



J'ai pas testé tous vos codes mais normalement le code de Jacques avec le Scripting.Dictionary, c'est redoutable :).

Le mien, c'était juste un code de débutant, qui fonctionnera avec une petite base de données de 1000 à 2000 lignes :eek:.
 
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re

Sinon, il ya beaucoup plus simple. Il suffit de copier vers le bas cette formule en G3:

Code: 
=NB.SI(A:A;A3)

Puis de filtrer la colonne G sur les valeurs supérieures à 1 et de copier les données filtrées vers une autre feuille.


Ainsi, on a même plus besoin de réfléchir :).
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Bonjour Michel, le fil,

Tu as raison, mais le calcul de la formule =NB.SI(A:A;A3) devrait prendre du temps sur 35000 lignes, à voir.

Remarque que c'est bien un filtrage (filtre élaboré) que fait ma macro, sur un tableau préalablement trié.

Et la formule du critère ne prend pas de temps :

=ET($A3<>$A2;$A3<>$A4)

A+
 
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re Job

Tu as raison, mais le calcul de la formule =NB.SI(A:A;A3) devrait prendre du temps sur 35000 lignes, à voir.
Cliquez pour agrandir...

Intuitivement, c'est ce que je ferais si je n'y connaissais rien en VBA (et même en m'y connaissant un peu :(, car la je suis sur de bien comprendre ce que je fais, or avec toutes ces macros, même les miennes, je ne suis pas toujours sur à 100% :eek:).

Mais j'ai l'impression, qu'on peut attendre un peu et que le nb.si ne doit pas prendre trop de temps même sur 35000 lignes (bon c'est vrai que j'ai pas testé. Je vais le faire si je retrouve un fichier avec toutes les communes de france, voire du monde ou de l'univers;)).
 
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re

Je m'abstiendrais avec les communes du monde, vu qu'avec les communes de France je trouve plus de 4700 doublons.

Il vaut mieux insérer la formule NB.SI puis la copier en valeur avant de faire le tri, sinon, sur XL2007, c'est une galère :confused:, les processeurs se mettent en calcul, et c'est très long :(.

Sinon, à l'occasion, il faudra que je compare toutes ses macros :rolleyes:.
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re Michel,

MJ13 à dit:
Mais j'ai l'impression, qu'on peut attendre un peu et que le nb.si ne doit pas prendre trop de temps même sur 35000 lignes (bon c'est vrai que j'ai pas testé(...)
Cliquez pour agrandir...

Alors teste ces 2 fichiers zippés sur cijoint.fr où j'ai recopié le tableau jusqu'à la ligne 36000 :

Cijoint.fr - Service gratuit de dépôt de fichiers

Sur le tiens, prépare les touches Ctrl+Alt+Suppr :D

A+
 
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re


Job: Bon, j'ai pas chronomètré, mais sur XL2007, c'était un peu plus long avce NB.SI par rapport à ta solution, mais pas tant ça :eek:.

Bon, c'est sur que cela ne fait que mettre la formule NB.SI ;).

Donc avec beaucoup de lignes on privilégiera la version macro :rolleyes:.

Jacques: Merci aussi, c'est vrai que la, c'est la tornade VBA, il faut vraiment que je m'y mette au Scripting.Dictionary :).
 
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

Re,

MJ13 à dit:
Job: Bon, j'ai pas chronomètré, mais sur XL2007, c'était un peu plus long avce NB.SI par rapport à ta solution, mais pas tant ça :eek:.
Cliquez pour agrandir...

Les 2 fichiers du post #25 chez moi :

TEST VILLES MJ13 (formule NB.SI) => 232 s sur Excel 2003 => 135 s sur portable avec Excel 2010

TEST VILLES JOB => 0,95 s sur Excel 2003 => 0,8 s sur Excel 2010.

A+
 
Vote positif 0 Vote négatif
MJ13

MJ13

XLDnaute Barbatruc
Re : lister les villes de meme nom a partir d'une base de données

re

Chez moi, avec PC Pentium E2180 2Ghz et 2 Go avec Win XP et XL2007.

Les 2 fichiers du post #25 :

TEST VILLES MJ13 (formule NB.SI) => 95 secondes sur Excel 2007

TEST VILLES JOB => 26 s sur Excel 2007.

Tu vois, quelque fois on a des différences entre les versions ;).
 
Vote positif 0 Vote négatif

Discussions similaires

M
  • Question
Microsoft 365 VBA copier des données d'un classeur à un autre
Réponses
4
Affichages
199
sylvanu
sylvanu
H
  • Résolu(e)
Microsoft 365 Remplir un tableau récap à partir d'une base de données, avec 2 critères
Réponses
7
Affichages
600
dysorthographie
dysorthographie
C
  • Résolu(e)
Microsoft 365 Duplication à partir d'une base de données du contenu d'un onglet dans d'autres onglets
Réponses
2
Affichages
491
Céline49
C
K
  • Résolu(e)
Microsoft 365 Ouvrir un autre classeur excel (VBA) pour utiliser les données de celui dans le classeur actif
Réponses
5
Affichages
853
Dranreb
Dranreb
A
  • Question
Organigramme à partir d'une base de données.
Réponses
7
Affichages
992
Efgé
Efgé
C
  • Question
XL 2021 Etat de sortie sur Word a partir d'une base Excel
Réponses
11
Affichages
598
cherco
C
T
  • Résolu(e)
Microsoft 365 Transformer les données d'une colonne en fonction de 2 autres colonnes
Réponses
5
Affichages
495
Tidjyphenom
T
A
  • Résolu(e)
XL 2019 Créer une base de données à partir de plusieurs fichiers excel source
Réponses
7
Affichages
773
adrien786
A
W
  • Question
XL 2019 Peupler une listbox à partir de plusieurs feuilles ayant un nom spécifique.
Réponses
7
Affichages
737
WG95
W
Dadi147
  • Résolu(e)
XL 2016 Récupérer les données de nom et de date VBA
2 3
Réponses
35
Affichages
2 K
Oneida
Oneida

Membres actuellement en ligne

15 membres et 105 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 323
Messages
2 087 299
Membres
103 512
dernier inscrit
sisi235