XL 2021 suppression de doublons

[auloin]

bonjour,

j'aimerais trouver une solution simple pour supprimer des données présentes sur deux feuilles Excel.
j'ai un fichier prospects (plusieurs milliers de lignes)
j'ai un fichier client (également plusieurs milliers de lignes)

l'objectif serait de trouver une solution pour supprimer de la liste des prospects les sociétés présentes dans le fichier clients
aujourd'hui la seule solution que j'utilise est de mettre dans une seule feuille les données avec des couleurs différentes d'utiliser la MFC pour identifier les doublons puis de supprimer manuellement les doublons des prospects ligne par ligne
fastidieux

pouvez-vous m'aider?

merci par avance

 

[Staple1600]

Bonjour

Pourquoi ne pas simplement utiliser la fonctionnalité native d'Excel ?
Données/Supprimer les doublons

Je viens de le faire sur ton fichier
(sur la feuille prospects)
en me basant sur ma colonne ref_iris
=> 5 doublons détectés
Et après suppression, je n'ai plus que 18 lignes de données

 

[auloin]

bonjour

merci pour ton retour rapide
en fait ce sont les sociétés en doublons qu'il me faut supprimer
mais pour ça il me faut coller les 2 fichiers à la suite l'un de l'autre puis de supprimer de cette feuille les données issues du fichier clients pour ne garder que les données du fichier prospects, c'est ce que je fais actuellement mais avec parfois des dizaines de milliers de lignes c'est long !!!
existe il une solution qui supprimerait dans mon exemple les doublons (5) de l'onglet prospects ?

 

[Staple1600]

Re

Bah, il suffit de changer la colonne, non ?

Fais le test sur ton fichier de test
Et on en reparle

 

[sylvanu]

Bonjour Auloin, Staple,
Si j'ai bien compris, on nettoie la base Prospect en supprimant les sociétés présentes dans test.
Un essai en PJ avec :

Sub Nettoie()
    Application.ScreenUpdating = False                  ' On fige l'écran
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Prostect
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' Insertion colonne en A
    f = "=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)"  ' Formule utilisée. Attention ajouter 1 aux colonnes pour compenser nouvelle colonne en A.
    With Range("A2:A" & DL)                             ' Plage où coller la formule en colonne A qui sera triée
        .FormulaLocal = f                               ' Coller formule
        .Value = .Value                                 ' Copie colle valeurs
        ActiveWorkbook.Worksheets("prospects").Sort.SortFields.Clear
        .EntireRow.Sort .Cells, xlDescending            ' Tri pour regrouper et accélérer
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete  ' Suppression des lignes concernées
    End With
    Columns("A:A").Delete Shift:=xlToLeft               'Suppression colonne formules
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

Je me suis basé sur la Ref_iris pour identifier les doublons.
Quelque chose m'échappe : pourquoi la même société à des codes iris différents dans les deux bases ?
( par ex Société16 644450307 et 644450402 )

 

[auloin]

re

en faisant le test
effectivement cela supprime les doublons..mais pas les bons:
dans ton exemple les sociétes 16, 18, 20 ,21 et 23 sont encore présentes
ce que je cherche c'est de supprimer de l'onglet prospects les sociétés présentes sur l'onglet test
et ce n'est pas le cas

cordialement

 

[auloin]

Bonjour Auloin, Staple,
Si j'ai bien compris, on nettoie la base Prospect en supprimant les sociétés présentes dans test.
Un essai en PJ avec :

Sub Nettoie()
    Application.ScreenUpdating = False                  ' On fige l'écran
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Prostect
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' Insertion colonne en A
    f = "=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)"  ' Formule utilisée. Attention ajouter 1 aux colonnes pour compenser nouvelle colonne en A.
    With Range("A2:A" & DL)                             ' Plage où coller la formule en colonne A qui sera triée
        .FormulaLocal = f                               ' Coller formule
        .Value = .Value                                 ' Copie colle valeurs
        ActiveWorkbook.Worksheets("prospects").Sort.SortFields.Clear
        .EntireRow.Sort .Cells, xlDescending            ' Tri pour regrouper et accélérer
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete  ' Suppression des lignes concernées
    End With
    Columns("A:A").Delete Shift:=xlToLeft               'Suppression colonne formules
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

Je me suis basé sur la Ref_iris pour identifier les doublons.
Quelque chose m'échappe : pourquoi la même société à des codes iris différents dans les deux bases ?
( par ex Société16 644450307 et 644450402 )

bonjour Sylvanu

effectivement je n'ai pas fais attention aux zones iris...ni à l’orthographe "sociétés"
ci joint le tableau corrigé

 

[cp4]

Bonjour @auloin , @Staple1600 ,@sylvanu ,

@auloin : Si j'ai bien compris. Je l'ai fait, je le joint.

Bon dimanche.

 

[auloin]

Bonjour @auloin , @Staple1600 ,@sylvanu ,

@auloin : Si j'ai bien compris. Je l'ai fait, je le joint.

Bon dimanche.

bonjour CP4

tout d'abord merci pour ton retour
néanmoins je ne comprends pas la feuille de résultat
ma recherche est de nettoyer l'onglet "prospects" (dans la colonne B) des données communes avec l'onglet "test"
c'est à dire supprimer les lignes comportant : societe 16, societe 17...........societe 23

cordialement,

 

[Staple1600]

Re,

@sylvanu
Petite question
(un truc m'échappe)
Pourquoi avec cette version de ton code, je n'obtiens pas le même résultat que ta macro intiale ?

Sub Nettoie_test()
Application.ScreenUpdating = False
 DL = [A65000].End(xlUp).Row ' On fige l'écran
Columns("A:A").Insert Shift:=xlToRight
    'With Range("A2", Cells(Rows.Count, 1).End(xlUp))
    With Range("A2:A" & DL) ' Plage où coller la formule en colonne A qui sera triée
        .FormulaLocal = "=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)"
        ActiveWorkbook.Worksheets("prospects").Sort.SortFields.Clear
        .EntireRow.Sort .Cells, xlDescending            ' Tri pour regrouper et accélérer
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete  ' Suppression des lignes concernées
    End With
    Columns("A:A").Delete Shift:=xlToLeft               'Suppression colonne formules
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

 

[sylvanu]

dans ton exemple les sociétes 16, 18, 20 ,21 et 23 sont encore présentes

Je pense que vous n'avez pas lu mon post jusqu'au bout :

Je me suis basé sur la Ref_iris pour identifier les doublons.
Quelque chose m'échappe : pourquoi la même société à des codes iris différents dans les deux bases ?
( par ex Société16 644450307 et 644450402 )

Je me base sur le Code Iris pour détecter les doublons, or dans votre fichier :

Prospect		Test
644450307	societé 16	644450402	societe 16
643480101	societé 18	644450204	societe 18
643760000	societé 21	642690000	societe 21
644050000	societé 23	644050000	societe 23

Pour Société 23 les deux codes Iris ne sont pas les mêmes, l'un est un nombre, l'autre une chaine.
Je rectifierais.

Si l'approcha n'est pas la bonne, et que malencontreusement ma boule de cristal est en panne, soyez plus précis sur ce que vous appelez un doublon. Pour moi le code Iris permettait de s'affranchir des fautes d'orthographes dans l'intitulé du nom des entreprises, en particulier les accentuations.

@Staple, je regarde.

 

[sylvanu]

@Staple,
C'est ce que j'avais fait au début.
Malencontreusement les formules ne s'adaptent pas.
Par ex pour la 1ere ligne Société6 644450205, on pose la formule :

=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)

après tri descendant, cette même formule se retrouve bien face à Société 6 mais est toujours indexé sur B2, ce qui est évidemment faux.
C'est pour ça que j'ai fait un copier coller valeurs pour figer les résultats.
( on le voit bien en faisant du pas à pas )

 

[sylvanu]

effectivement je n'ai pas fais attention aux zones iris...ni à l’orthographe "sociétés"
ci joint le tableau corrigé

Et où est le tableau corrigé ?

 

[Staple1600]

Re

@sylvanu
Pendant que tu faisais dans le pas à pas, je faisais dans le MsgBox

Sub test_suite_A()
 DL = [A65000].End(xlUp).Row ' On fige l'écran
Columns("A:A").Insert Shift:=xlToRight
Range("A2:A" & DL).FormulaLocal = "=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)"
'Worksheets("prospects").Sort.SortFields.Clear
'Range("A2:A" & DL).EntireRow.Sort Range("A2:A" & DL).Cells, xlDescending             ' Tri pour regrouper et accélérer
MsgBox Range("A2:A" & DL).SpecialCells(xlCellTypeFormulas, 2).Address  ' Suppression des lignes concernées
End Sub
Sub test_suite_B()
 DL = [A65000].End(xlUp).Row ' On fige l'écran
Columns("A:A").Insert Shift:=xlToRight
Range("A2:A" & DL).FormulaLocal = "=SI(NB.SI(test!A:A;prospects!B2)>0;CAR(1);0)"
Range("A2:A" & DL) = Range("A2:A" & DL).Value
'Worksheets("prospects").Sort.SortFields.Clear
'Range("A2:A" & DL).EntireRow.Sort Range("A2:A" & DL).Cells, xlDescending             ' Tri pour regrouper et accélérer
MsgBox Range("A2:A" & DL).SpecialCells(xlCellTypeConstants, 2).Address  ' Suppression des lignes concernées
End Sub

Et je me suis rendu que c'est le tri qui "foutait le souk"

Question : Est-ce que le tri est obligatoire ?
(ou c'est comme j'ai pu le lire dans d'autres fils, juste pour une question de rapidité d'exécution du code.)

 

[sylvanu]

juste pour une question de rapidité d'exécution du code.

Oui car Auloin dit :

plusieurs milliers de lignes

Cependant j'ai fait l'essai sur 5000 lignes sur mon vieux 2007, avec tri 0.235s, sans tri 0.521s.
Donc pas vraiment rédhibitoire sur une action ponctuelle.
Mais bon c'est toujours bon à prendre.