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
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
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 ?
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 :
VB:
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 )
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
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 :
VB:
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 )
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
@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 ?
Code:
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
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,
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 :
VB:
=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
Pendant que tu faisais dans le pas à pas, je faisais dans le MsgBox
Code:
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.)
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.