Discussion :

[End_Sub]

Bonsoir à tous,

Je requiers votre aide pour avancer sur un problème sur lequel je n'arrive pas à avancer.

Ma problématique est somme toute assez simple mais je bute quand même dessus :

J'ai une base de donnée de quelques colonnes sur plusieurs dizaines de milliers de lignes avec bien sûr des doublons qui trainent dans le lot...
Je pourrais il est vrai passer par la suppression des doublons directement via le bandeau mais je souhaite pouvoir les isoler afin de les traiter.

Ma piste initiale était de constituer deux boucles qui travailleraient l'une dans l'autre.

Pour vous donner une idée de la structure de mon fichier :
- la plage sur lequel je dois faire la comparaison est sur les 5 premières colonnes
- je souhaite avoir dans une colonne n°6 le ou les numéros de lignes sur lesquels se trouvent les autres doublons (ou une référence unique pour chaque doublon).

Je suis ouvert à toutes vos idées !
Merci d'avance pour votre aide

End_Sub

[unparia]

Bonjour
Nécessité (une fois de plus) d'être TRES précis :

- la plage sur lequel je dois faire la comparaison est sur les 5 premières colonnes

et

J'ai une base de donnée de quelques colonnes sur plusieurs dizaines de milliers de lignes avec bien sûr des doublons qui trainent dans le lot...

Lorsque je lis ces deux seules déclarations, j'ignore totalement si les "doublons" sont des données identiques sur la totalité des 5 colonnes concernées d'une ligne ou s'ils sont identifiés par une donnée identique dans l'une (et laquelle ?) de ces colonnes.

[End_Sub]

Bonsoir Unparia

Effectivement- ca prête à confusion.

Les 5 premières colonnes de chaque ligne doivent être considérées comme un bloc - et comparées au reste de la base

Merci pour ton aide
End Sub

[unparia]

Tu n'as dans ce cas d'autre solution que deux lentes boucles imbriquées pour comparer ce qui est comparable.
Avec deux choix, car une simple concaténation des valeurs ne serait qu'une présomption :
- choix 1 : comparaison (une à une, donc) de chaque cellule des 5 colonnes concernées de chacune des lignes
- choix 2 : comparaison de leur concaténation, mais chaque valeur étant alors séparée de l'autre par un séparateur unique (et non risquant d'être présent dans chacune des cellules) !
Commence par cette étape liminaire du travail à faire. On verra ensuite.

maintenant, mais selon ta version Excel (et donc non forcément valable pour toutes versions si fichier à distribuer) :
et puisque les 5 colonnes en cause ne sont pas discontinues : tu peux toujours utiliser (si disponible sur ta version) l'outil Excel de suppression des doublons, appliqué à la sélection de ces 5 colonnes)
MAIS : cela ne te donnera pas les N°s de lignes que tu veux recenser par doublon !

[RyuAutodidacte]

Bonsoir,

Personnellement je choisirai la 2ème option, avec la concaténation (après c'est un choix) mais je l'a trouve plus pratique à gérer

j'avais créer un post sur les doublons dont le code marche aussi bien sur PC que Mac (je suis sur MAC et de temps en temps sur PC) :

- l'une des options de ma macro (Code créer par @Marc-L) est de rassembler les doublons sur une feuil2, les doublons se suivant bien entendu
sur une colonne supp à coté des doublons, les n° de ligne correspondant au doublons; il suffit alors de laisser apparaitre seulement les n° de ligne que l'on veut effacer (les n° lignes que l'on garde sont effacés)
pour mieux voir les doublons dans cette option j'ai créé une macro surlignant les doublons en 1 sur 2 à chaque changement de doublons

- une des autres options et d'avoir le choix, soit d'effacer les derniers doublons, soit d'effacer les 1er doublons (Code créer par @Marc-L)

Le Post complet
au final 1 et 2 (corrections apportées)

Je pense qu'après avoir fait la concaténation les codes dans les liens données sont utilisables il faudra faire quelques modifs

Voilà

Edit : Sachant qu'à ce moment là j'avais le niveau d'un bon débutant, il serait intéressant de lire le post en entier car pour ma part je le trouve très enrichissant (si le coeur vous en dit)

Coucou Jacques

[unparia]

Bonjour ryuautodidacte
C'est très astucieux.
Il faut bien entendu alors ajouter sur la feuille de départ une colonne reprenant les N°s de ligne avant d'en faire une copie sur la feuil2, puis ;
- traiter cette feuil2 en la triant
- utiliser une boucle de parcours et écrire dans une autre additionnelle les N°s des lignes des doublons (séparées par un séparateur ";" par exemple)
- lire ensuite les deux colonnes (disons X et Y) et porter en colonne de Feuil1, pour chaque ligne de N° lu en colonne X, la valeur correspondante de la colonne Y
- effacer la feuil2
Rigolo, mais fonctionnel

[patricktoulon]

re
Bonsoir comme visiblement le contexte des doublons n'est pas vraiment très clair
mais que j'ai entendu parler de concaténation je supposé que ca concerne plusieurs colonnes dans une ligne a comparer avec les autre lignes sur les mêmes colonnes

tout du moins c'est ce que j'ai compris apres qu'elles soit contigues ou pas cela se gère dans le join de l'array

le principe c'est de boucler sur toute les lignes et concatener les colonnes voulues pour les comparer a un dicionnaire
si il n'esiste pas on le met avec le numero de ligne
et si il existe déjà on ajoute le numero de ligne qu'il contiens déjà

une 2d boucle sur le tableau en concaténant les colonnes voulues
et a chaque ligne mettre la valeur de l'item de dico correspondant a la clé(chaine)

somme toute rien de bien complexe

on se rtrouve a la fin avec la colonne 6("F") ou dans chaque ligne on a les numéro de ligne de la concaténation des colonnes prises en compte
si il y a 1 seul chiffre il est tout seul si il y plusieurs chiffre séparés par "|" alors la concaténation 'est un doublons

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub test()
'on prend les 5 colonne dans une variable tableau
tableau = Range("A1:f" & Cells(Rows.Count, 1).End(xlUp).Row)
Set d = CreateObject("scripting.dictionary")
For i = LBound(tableau) To UBound(tableau)
chaine = Join(Array(tableau(i, 1), tableau(i, 2), tableau(i, 3), tableau(i, 4), tableau(i, 5)), "-")
If Not d.exists(chaine) Then
d(chaine) = i
Else
d(chaine) = d(chaine) & "|" & i
End If
Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
chaine = Join(Array(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5)), "-")
Cells(i, 6) = d(chaine)
Next
End Sub

demo en image

[RyuAutodidacte]

Salut Patrick,

Comment fais tu pour choisir les doublon à supprimer sur tel ou tel lignes ??

Par exemple comment déterminer/procéder si l'on veut supprimer soit la ligne 11 soit la ligne 14 (vu sur ton gif) ??

Edit :
Re Jacques,

J'avais imaginé les procéder pour qu'il permettent de gérer un maximum de cas pour n'importe quel type de travail

[patricktoulon]

re
tu es sérieux la????
c'est simple comme on dit les derniers racleront les fond des plats

dans la boucle tu garde les premiers doublons et c'est tout en utilisant un nouveau dico

tu me surprend des fois

c'est pourtant ton dada ca les tableaux

allez je t en donne une

regarde bien la colonne 6 ("F")
et si je te dis keys/items et items/keys ca te parle
regarde bien bien bien

[RyuAutodidacte]

cette partie là n'était pas explicite surtout pour le demandeur

[patricktoulon]

re
et puis entre nous quelle est l'importance de supprimer par exemple la 11 ou la 14 puisqu'il faut en suprimer un

a moins que ces cellules comportent des nom ou date triés ca n'a aucune importance

[RyuAutodidacte]

il pourrait y avoir des données supplémentaires dans les colonnes suivantes qui n'ont pas besoin d'être pris en compte pour déterminer les doublons
mais dont les données peuvent être différentes et dans ce cas là il faut choisir quelles données garder d'où le choix de tel ou tel ligne

[patricktoulon]

et donc dans son organisation il aurait laisser la colonne 6 pour le job ????

pour l'instant il a un moteur pour connaitre les doublons en concaténation de X colonnes contiguës ou pas ,reste a lui de savoir quoi en faire il ne sert a rien de spéculer sur le reste

j'ajouterais pour finir que si il avais eu la volonté de chercher il aurait trouvé ceci par exemple
http://www.developpez.net/forums/d13...bles-tableaux/
ce qui fait exactement ce q'u il veut
peut être a rafraichir j'ai fait ca y a 3 ans faut pas m'engueuler hein!!!

[unparia]

Re-moi, Ryu,

je te fais le début de ce que j'ai dit plus haut

Une feuille tremplin nommée tremplin et ce code depuis la feuille des données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim derlig As Long, t
Worksheets("tremplin").Cells.ClearContents
derlig = Range("A" & Rows.Count).End(xlUp).Row
t = Range("A1:E" & derlig)
For i = 1 To UBound(t, 1)
  For j = 2 To UBound(t, 2)
    t(i, 1) = t(i, 1) & Chr(1) & t(i, j)
  Next
Next
ReDim Preserve t(1 To UBound(t, 1), 1 To 3)
For i = 1 To UBound(t, 1)
  t(i, 2) = i
Next
Worksheets("tremplin").Range("A1:B" & derlig).Value = t

regarde le feuille tremplin, maintenant
Relis maintenant ce que j'ai dit plus haut. Trie tremplin, puis boucle sur colonne A (tant que doublon) et remplis chaque ligne correspondante de C.
Et tu connais la suite (me relire).
Je te laisse faire.
A demain (sommeil)

Ah ... Je vois que chacun a fait comme moi : au dodo et c'est sage avec le froid qu'il fait
Bon. Je regarde la météo (m'étonnerait qu'elle me permette d'aller pêcher), prends mon petit déjeuner, me retrousse les manches et continue donc ce truc par tremplin
A plus tard

EDIT :
Voilà donc (bâclé mais bon...) avec la méthode tremplin, qui devrait être très rapide :
Une feuille nommée source avec les données à traiter et une feuille nommée tremplin

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Dim DL As Long, c As Range, t, sc As Worksheet, col_denonce As String, i As Long, j As Long
Set sc = Worksheets("source")
col_denonce = "H" ' ===>>> dire dans quelle colonne afficher les "dénonciations"
DL = sc.Range("A" & Rows.Count).End(xlUp).Row
sc.Range(col_denonce & 1 & ":" & col_denonce & DL).ClearContents
t = sc.Range("A1:E" & DL)
For i = 1 To UBound(t, 1)
  For j = 2 To UBound(t, 2)
    t(i, 1) = t(i, 1) & chr(1) & t(i, j)
  Next
Next
ReDim Preserve t(1 To UBound(t, 1), 1 To 3)
For i = 1 To UBound(t, 1)
  t(i, 2) = i
Next
With Worksheets("tremplin")
  .Cells.ClearContents
  .Columns(3).Font.Color = vbRed: .Columns(3).Font.Bold = True
  .Range("A1:B" & DL) = t
  .Range("A1:B" & DL).Sort key1:=.Range("A1:B" & DL), _
   order1:=xlAscending, Header:=xlNo
   For i = 1 To DL + 1
     Set c = .Range("A" & i)
     If c.Value = c.Offset(1, 0).Value Then
        c.Offset(1, 2).Value = c.Offset(0, 1).Value
        If c.Offset(0, 2).Value <> "" Then c.Offset(1, 2).Value = c.Offset(0, 2).Value
     End If
   Next
   .Range("A1:C" & DL).Sort key1:=.Range("B1:C" & DL), _
   order1:=xlAscending, Header:=xlNo
  .Range("C1:C" & DL).Copy Destination:=sc.Range(col_denonce & 1)
End With

J'ai finalement choisi d'afficher en face des doublons uniquement le N° de ligne "non-doublon" (de "référence", donc) correspondant. Pourquoi ? parce-que lors de mes essais, je me suis aperçu de ce que si le nombre de doublons d'un même "article" était important, cela occupait énormément de place en colonne de "dénonciation" et n'allait pas dans le sens d'une bonne "lisibilité", bien au contraire. Cela devenait quelquefois même très gênant.
PS : ceux qui suivront ce mécanisme découvriront qu'il suffit de très peu pour, tout en le gardant, se passer d'un tremplin feuille et lui substituer un tremplin matrice.

[End_Sub]

re
Bonsoir comme visiblement le contexte des doublons n'est pas vraiment très clair
mais que j'ai entendu parler de concaténation je supposé que ca concerne plusieurs colonnes dans une ligne a comparer avec les autre lignes sur les mêmes colonnes

tout du moins c'est ce que j'ai compris apres qu'elles soit contigues ou pas cela se gère dans le join de l'array

le principe c'est de boucler sur toute les lignes et concatener les colonnes voulues pour les comparer a un dicionnaire
si il n'esiste pas on le met avec le numero de ligne
et si il existe déjà on ajoute le numero de ligne qu'il contiens déjà

une 2d boucle sur le tableau en concaténant les colonnes voulues
et a chaque ligne mettre la valeur de l'item de dico correspondant a la clé(chaine)

somme toute rien de bien complexe

on se rtrouve a la fin avec la colonne 6("F") ou dans chaque ligne on a les numéro de ligne de la concaténation des colonnes prises en compte
si il y a 1 seul chiffre il est tout seul si il y plusieurs chiffre séparés par "|" alors la concaténation 'est un doublons

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub test()
'on prend les 5 colonne dans une variable tableau
tableau = Range("A1:f" & Cells(Rows.Count, 1).End(xlUp).Row)
Set d = CreateObject("scripting.dictionary")
For i = LBound(tableau) To UBound(tableau)
chaine = Join(Array(tableau(i, 1), tableau(i, 2), tableau(i, 3), tableau(i, 4), tableau(i, 5)), "-")
If Not d.exists(chaine) Then
d(chaine) = i
Else
d(chaine) = d(chaine) & "|" & i
End If
Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
chaine = Join(Array(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5)), "-")
Cells(i, 6) = d(chaine)
Next
End Sub

Bonjour,

Merci à tous pour votre aide sur ce sujet.
J'ai intégré le code de patricktoulon, et cela me permet parfaitement bien d'isoler mes doublons pour les rectifier.
Un grand merci à vous pour ce code.

J'ai de la chance de n'avoir d'avoir plutôt de nombreux doublons qui ne se répètent qu'une fois - mais je vais aussi regarder votre code Unparia au cas ou !

Bon après-midi

[patricktoulon]

bonjour fin de macro car c'est le pseudo que tu a choisi
avec mon code tu le triples et quadruple et plus (doublons sont repérés aussi je n'ai fait que par deux pour l'exemple
mais mon code repère les autre aussi tant que tu en a en fait

[RyuAutodidacte]

Bonjour,

j'arrive quand tout est ok mais bon j'étais trop fatigué
alors voilà une autre approche dont j'avais parlé (rassembler seulement les doublons sur une autre feuille (ici en Feuil2)) -
ça permet aussi d'éviter de prendre en compte automatiquement les colonnes suivantes (non concaténées) mais seront a affichées dans le résultat (et aussi on ne touche pas à sa feuille source au risque de mauvaise manip):

Possibilité de choisir le caractère de concaténation, possibilité de mettre le nombre de ligne pour l'en-tête et possibilité de choisir le nombre de colonnes pour les doublons en lignes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Const Cct = "_": Const Entete = 3: Const ResCol = 5 'Gestion : Caractère de concaténation des col , Entete, nb de col à concaténer

ce qui donne (C'est une modification du code de Mar-L) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Sub DoublonsConcatene()
Const Cct = "_": Const Entete = 3: Const ResCol = 5 'Gestion : Caractère de concaténation des col , Entete, nb de col à concaténer
Dim Rg As Range, VA(), i&, Coll As New Collection, R&, K$, S$, Db, V, L&
 
    Set Rg = Feuil1.UsedRange.Rows
    VA = Rg.Columns(1).Resize(, ResCol).Value
    For i = 1 + Entete To UBound(VA)
        For j = 2 To UBound(VA, 2)
            VA(i, 1) = VA(i, 1) & Cct & VA(i, j)
        Next
    Next
        ReDim Preserve VA(1 To UBound(VA), 1 To 1)
 
        On Error Resume Next
    For R = 1 + Entete To UBound(VA)
        K = CStr(VA(R, 1))
            Coll.Add R, K
        If Err.Number = 457 Then
            S = Coll(K) & Cct & R: Coll.Remove K: Coll.Add S, K, 1
            Err.Clear
        End If
    Next
        On Error GoTo 0
        Feuil2.UsedRange.Clear
Application.ScreenUpdating = False
    For Each Db In Coll
        If IsNumeric(Db) Then
            Exit For
        Else
            For Each V In Split(Db, Cct)
                L = L + 1
                Feuil2.Cells(L, 1).Resize(, Rg.Columns.Count).Value = Rg(V).Value
                Feuil2.Cells(L, Rg.Columns.Count + 1).Resize(, 2).Value = Array(V, Mid(Rg(V).Cells(1).Value, 4))
            Next
        End If
    Next
Application.ScreenUpdating = True
    Set cRow = Nothing:  Set Rg = Nothing
    If L Then
        With Feuil2.UsedRange.Columns
            '.Sort .Cells(1), xlAscending, Header:=xlNo 'Mettre le code adequat pour le tri (ici sur la col 1)
            Application.Goto .Cells(1), True
        End With
    Else
        MsgBox "Aucun doublons trouvés", , "Recherches des doublons et tri": Exit Sub
    End If
 
        Inter_Coul_Doublons 'macro permetant de différencié les doublons surtout quand ils sont nombreux
 
End Sub

Sub pour distinguer plus facilement les doublons (macro lancer à la fin de la sub ci-dessus) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sub Inter_Coul_Doublons()
Dim Der_ligne As Long, cpt As Integer, Inve As Boolean, T!
 
 With Feuil2
Application.ScreenUpdating = False
Der_ligne = .Range("A" & Rows.Count).End(xlUp).Row
 
cpt = 1
 
Inve = True
 
For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
    If .Range("A" & i) <> .Range("A" & i + 1) Then
        cpt = 1
        If Inve = True And cpt = 1 Then
            Inve = False
        .Range("A" & i).Resize(cpt, Cells(1, Columns.Count).End(xlToLeft).Column).Interior.ColorIndex = xlNone
        Else
            Inve = True
            .Range("A" & i).Resize(cpt, Cells(1, Columns.Count).End(xlToLeft).Column).Interior.ColorIndex = 6
        End If
    Else
        Do
        cpt = cpt + 1
        i = i + 1
        Loop Until .Range("A" & i) <> .Range("A" & i + 1)
        If Inve = True And cpt >= 1 Then
            Inve = False
            .Range("A" & i - cpt + 1).Resize(cpt, Cells(1, Columns.Count).End(xlToLeft).Column).Interior.ColorIndex = xlNone
        Else
            Inve = True
            .Range("A" & i - cpt + 1).Resize(cpt, Cells(1, Columns.Count).End(xlToLeft).Column).Interior.ColorIndex = 6
        End If
    End If
    cpt = 1
Next
 
Application.ScreenUpdating = True
End With
End Sub

au début :


le résultat (permet d'avoir une vision direct, surtout quand on a des milliers de lignes) :


Pour supprimer les doublons (code de Mar-L) - (effacer les n° de lignes que l'on gardes pour supprimer le doublons voulus (ici en feuil2))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub ChoixSuppDoublonsV3a()
        Dim C&, L&, S$, T!, V, VA
        T = Timer
    With Feuil2.Cells(1).CurrentRegion.Columns
        If .Count <= Feuil1.UsedRange.Columns.Count Then Beep: Exit Sub
        Application.ScreenUpdating = False
        With .Item(.Count):  VA = .Value:  End With
    End With
    With Feuil1.UsedRange.Columns
            C = .Count + 1
            .Item(C).Value = 0
        For Each V In VA
            If IsNumeric(V) And V > "" Then L = L + 1: .Cells(V, C).Value = 1
        Next
        If L Then
            .Resize(, C).Sort .Cells(1, C), xlAscending, Header:=xlYes
            .Parent.Rows(.Rows.Count - L + 1).Resize(L).Delete
        End If
            .Item(C).Delete
            Application.Goto .Cells(1), True
    End With
        Application.ScreenUpdating = True
        If L > 1 Then S = "s"
    MsgBox L & " ligne" & S & " supprimée" & S & " en " & Format(Timer - T, "0.000s"), , "   ChoixSuppDoublonsV3a"
End Sub

Bien entendu le code est Multi-plateforme PC/MAC

PS : Jacques et Patrick je regarde vos codes

[unparia]

Mon mot pour tendre à conclure, en ce qui me concerne, les différentes interventions :
1) il ne m'a pas été possible de comparer les vitesses d'exécution des méthodes proposées, avec celle utilisant l'objet Scripting Dictionnary. Et pour cause ! il n'est ni invocable ni utilisable sur ma machine (parmi d'autres)
2) ce n'est pas le nombre de lignes (que l'on voit) écrites d'un code, qui compte, mais l'occupation en résultant en mémoire (entre autres), notamment en cas de chargement "invisible" mais bien présent de fonctions d'une librairie (en l'occurrence : scrrun.dll pour utiliser l'objet scripting dictionary)
3) l'utilisation d'une collection me parait dans ces conditions plus sage que celle d'un dictionnaire

Le véritable aspect de tout cela, maintenant :
- peu importe la méthode utilisée si le classeur en cours n'est destiné ni à être partagé, ni à être déployé
- il en va très différemment dans le cas contraire car, entre autres, la librairie scrrun.dll est "muselée" sur certaines machines (dont la mienne), carrément inexistante sur d'autres

Ce sont là les bases (indépendance et portabilité ou non) de la seule réflexion acceptable à mon sens. Comprendront ceux qui le veulent. Ne comprendront (ne voudront pas comprendre) pas les autres et ce sera là l'affaire de chacun.

Amitiés à tous

[patricktoulon]

re

Bonsoir
vous du moins lourd plus rapide ???

en toute logique un simple tableau et la fonction Math devrait faire l'affaire et rien d'autre
redimer un tableau vide idem au range en terme de ligne sur 2 colonne et dan un seule boucle un match sur la colonne 1 et la concaténation mettre les index de ligne en colonne 2(ajout si match inscription si non match )
le principe du dico Scripting sans Scripting
voila

[RyuAutodidacte]

Re Jacques,
Astucieux le principe sans collection et Dictionnaire avec les 2 tris (1 pour les doublons, l'autre pour remettre les données dans l'ordre de départ)

Ryu