Supprimer certaines lignes ou les cellules contiennent un string variable

Version imprimable

15/07/2019, 17h12
Lordvoldman

1 pièce(s) jointe(s)

Supprimer certaines lignes ou les cellules contiennent un string variable

Bonjour tout le monde,

J'aimerai pouvoir supprimer des lignes ou une cellule contient autre chose que ce que j'ai dans une liste, dans une autre feuille.
Pièce jointe 491186

Dans l'exemple, la macro irait voir ma liste cellule par cellule pour voir si j'ai quelque chose que j'ai dans ma liste de référence, si je l'ai je garde si je ne l'ai pas je supprime. ( En réalité les deux collonnes sont dans deux feuilles différentes)

J'ai essayé le vlookup mais ça ne marche pas : si je fais "Faux" je ne trouve rien car ça ne match pas exactement, il y a des petites différences des mots en plus ou en mois. Si je fais "Vrai" j'ai tout et n'importe quoi.

Merci de votre aide!
15/07/2019, 19h13
marot_r
Bonjour.

Pour déterminer si une ligne doit être supprimée ou pas, tu peux peut-être utiliser un comptage à la place du RechercheV.

Exemple :

Code:

=SI(NB.SI.ENS(C:C;A1 & "*")<>0;"À garder";"À Supprimer")

Va compter tous les textes de ta liste de référence (C:C) qui commencent par le contenu de A1.
Ceci fonctionne si :
1. Le texte de la liste de référence sont égaux ou plus long que ceux cherchés.
2. Tu n'as pas d'ambiguïté sur les critères.
  Ex : Pas dans ta liste de référence "Peugeot 205", "Peugeot 404" et dans tes données "Peugeot" tout court car Excel va en trouver 2 alors qu'il ne devrait pas trouver de correspondance.
A+
15/07/2019, 19h24
Lordvoldman

1 pièce(s) jointe(s)

Merci de votre réponse!
J'avais essayé cette solution mais ça ne marche pas .. Je vous mets un exemple concret de ce que ça me donne : Pièce jointe 491217

ça me donne toujours le même résultat alors que pour "British airways" par exemple, je devrai avoir supprimer et pour "LUFTHANSA TECHNIQUE AG" je devrais avoir à garder..

J'ai aussi essayé de coder en VBA mais ça me semble infaisable.. la liste de référence fait en réalité 1000 lignes et je ne pourrais pas mettre tout ces critères dans un IF, si c'était faisable..
15/07/2019, 20h10
marot_r

Bonjour.

En effet cela ne peut pas marcher comme je l'avais indiqué :

Citation:

Le texte de la liste de référence sont égaux ou plus long que ceux cherchés.

Pour le faire en VBA on va inverser le critère du comptage. Je te reviens dans un nouveau post.

A+

Re-bonjour.

À mettre dans un module.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public Function ASupprimer(prmCelluleDonnees as range, prmListeRef as range)
    'Retourne True si la prmCeluleDonnees contient un des textes de la liste de référence
   dim result as boolean:result=false
 
   dim cellule as range
 
   for each cellule in prmListeRef.cells
 
       if trim(cellule.value)="" then
           exit for 'Sort si on a trouvé une cellule vide
       end if
 
       if ucase(prmCelluleDonnees.value) like "*" & ucase(cellule.value) & "*" then
            result=true 'On a trouvé quelque chose qui ressemble à ce qu'on cherche.
            exit for
       end if
 
   next cellulle
 
   ASupprimer=result
end function

Pour valider que cela fonctionne comme prévu avant de s'en servir en vrai.

Code:

1
2
3
4
 
private sub Test_ASupprimer()
    Debug.print ASupprimer(activesheet.range("C1"), activesheet.columns("A"))
end sub

Si tu obtiens bien ce que tu veux après, cela s'utilise comme une formule Excel.

Code:

=ASupprimer(C1; A$:A$)

Note si tu veux seulement que cela commence par ce qui est dans A1 (pas que cela contienne A1 quelle que soit la position), il faut mettre :

Code:

if ucase(prmCelluleDonnees.value) like ucase(cellule.value) & "*" then

A+

16/07/2019, 08h43
halaster08

1 pièce(s) jointe(s)

Bonjour,

Une proposition via filtre avancé:
Filtrer les lignes indésirables puis les supprimer puis défiltrer
exemple:
Pièce jointe 491276

Note que pour être sûr que la suppression de ligne ne modifie pas la liste il vaut mieux la mettre sur une autre feuille

Si besoin le tuto sur les filtres avancés: https://philippetulliez.developpez.c...dvancedfilter/

Ce que je te propose peut se faire simplement avec ou sans vba
16/07/2019, 15h43
Lordvoldman

Merci à vous deux pour votre aide.

Pour le code que vous m'avez fournis marot_r, j'essaie de débuger mais j'ai toujours la même erreur "référence de variable de contrôle incorrecte dans next"
Y a t il un probème ?
16/07/2019, 15h56
Lordvoldman

Je viens de remplacer le Next cellule par Next et ça ne bloque plus.. Sauf que la fonction ne marche comme il faut.

Elle ne trouve pas de match entre BRITISH AIRWAYS (ma liste) et British Airways (liste référente) .. par exemple.
16/07/2019, 16h05
marot_r

Bonjour.

Peux-tu poster ton code actuel ?

A+

Code:

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
Public Function ASupprimer(prmCelluleDonnees As Range, prmListeRef As Range)
    'Retourne True si la prmCeluleDonnees contient un des textes de la liste de référence
   Dim result As Boolean: result = False
 
   Dim cellule As Range
 
   For Each cellule In prmListeRef.Cells
 
       If Trim(cellule.Value) = "" Then
           Exit For 'Sort si on a trouvé une cellule vide
       End If
 
       If UCase(prmCelluleDonnees.Value) Like "*" & UCase(cellule.Value) & "*" Then
            result = True 'On a trouvé quelque chose qui ressemble à ce qu'on cherche.
            Exit For
       End If
 
   Next
 
   ASupprimer = result
End Function
 
Private Sub Test_ASupprimer()
    Debug.Print ASupprimer(ActiveSheet.Range("H2"), ActiveSheet.Columns("E"))
End Sub

Le voici. Je l'utilise sur deux colonnes directement dans mon fichier excel en tant que formule. Sauf que comme dit précédemment des fois il se perd sur certains exemple comme :
BRITISH AIRWAYS = FAUX alors que j'ai bien British Airways dans ma liste réf.

16/07/2019, 16h41
Lordvoldman

1 pièce(s) jointe(s)

Bon, j'ai supprimé l'espace qui était devant "British Airways" dans ma liste référente et ça a apparement marché juste après.. Je ne pense pas que ce soit le problème car pour les autres ça fonctionne toujours pas.
Je vous mets le fichier au cas ou vous avez 2 minutes pour regarder..

Merci,Pièce jointe 491416
16/07/2019, 16h48
marot_r

Bonjour essaye :

Code:

If Trim(UCase(prmCelluleDonnees.Value)) Like "*" & Trim(UCase(cellule.Value)) & "*" Then

A+
16/07/2019, 17h02
Lordvoldman

Rebonjour,

J'avais essayé de tout remettre proprement avec SUPPRESPACE et maintenant avec le code que vous avez modifié mais rien ne marche, aucune correspondance entre "BOC AVIATION (CAYMAN) LTD" et "BOC Aviation Limited" (liste réf) par exemple..
16/07/2019, 17h23
marot_r

Pour ton dernier exemple c'est normal.

Ce que disent les * c'est "N'importe quels caractères" mais il faut quand même que tous les caractères soient en commun.

Quand on regarde "BOC AVIATION (CAYMAN) LTD" et "BOC Aviation Limited" on voit immédiatement que ce n'est pas égal.
Pour un ordinateur 2 choses sont égales si elles ont exactement les mêmes caractères à la même place, sinon elles sont différentes.
Donc si tu veux que BOC AVIATION (CAYMAN) LTD sorte, il faut que tu mettes dans ta liste de référence BOC AVIATION.
Donc là "BOC AVIATION (CAYMAN) LTD" va ressembler à "*BOC Aviation*" et cela va être vrai.
Plus généralement il faut que dans la liste de référence tu ai la partie "commune" entre ce qui est dans tes données et ce que tu cherches.

Attention on peut parfois ramasser plus qu'on pense, si par exemple, tu testes "*Cola*" tu vas ramasser "Coca Cola", "Pepsi Cola" mais aussi "Chocolat" ou "Scolaire".

Donc il faut à la fois être suffisamment générique pour trouver ce qu'on cherche et pas trop pour ne pas trop en trouver.

A+
16/07/2019, 17h31
marot_r

Mon post précédent suppose que tu veux rester simple.

Si tu veux quelque chose de plus évolué tu peux faire un traitement sur tes données pour par exemple remplacer tous les "LTD" par "Limited" ou/et effacer qui est entre parenthèse, supprimer tous les blancs, tirets et autre délimiteur.

Tu peux faire cela par code VBA (au moment de la comparaison) ou préalablement par formules ou code.

Tu peux aussi envisager une liste de synonymes.

Valeur Synonyme

BOC AVIATION (CAYMAN) LTD BOC Aviation Limited

BOC AVIATION LTD BOC Aviation Limited

BOC BOC Aviation Limited

Comme cela tu peux "remplacer" la valeur par son synonyme et après tester juste le synonyme.

A+

Valeur	Synonyme
BOC AVIATION (CAYMAN) LTD	BOC Aviation Limited
BOC AVIATION LTD	BOC Aviation Limited
BOC	BOC Aviation Limited