Mettre en évidences des cellules contenant un mot par rapport à une base de données

zored

XLDnaute Nouveau
Bonjour à tous,

Après plusieurs jours de galère je m'oriente vers vous car je n'ai toujours pas réussit à résoudre mon soucis (qui est enfaite plusieurs soucis mais rien de grave).
Voila, je possède une base de donnée avec des clients (une soixantaine) qui doivent respecter une hiérarchie du "point de livraison", c'est à dire que pour que ce "point de livraison" soit valable (pour mes 60 clients) il doit être composé du nom du point de livraison (dans mon exemple ci dessous auchan) + la commune de livraison, pour au final donné un point de livraison appelé "AUCHAN STRASBOURG" si la livraison est à strasbourg.

j'ai un extrait de données d'une période de 1 mois avec énormément de clients (150/200) qui peuvent ou non être présent dans ma base de donnée.
Le but est de mettre en évidence une donnée si celle ci n'est pas conforme, c'est à dire si elle ne respecte pas la façon dont elle doit être écrite, je m'explique:



Nous pouvons repérer ici qu'il s'agit du même point de livraison (ceci est un exemple), mais qu'il y a une anomalie sur la première ligne car AUCHAN n'est pas suivi de STRASBOURG (contrairement à la ligne 2 qui n'est pas en anomalie).
Le client 1 et 2 ne sont pas dans ma base de donnée et ne sont donc pas en anomalie non plus
Ceci est mon objectif final qui serait éventuellement fait "automatiquement" en cliquant sur un bouton qui me compterais ensuite le nombre d'anomalie(s)

J'ai jusqu'à présent procéder à la main mais c'est une réelle (et non négligeable) perte de temps car je suis persuader qu'on peut faire cela de façon automatique. J'ai déjà quelques débuts mais rien de bien concrets mais j'ai pu décomposer mon "objectif" en 3 partie:

1. il existe une anomalie par rapport à ma BDD

2. il n'y a pas d'anomalie par rapport à ma BDD (idéalement comme la 2nd ligne de mon exemple)

3. le point de livraison n'est pas contenue dans ma BDD, même avec la "soustraction" de la "commune de liv" sur le "point de liv" s'il y en a une (j'ai trouvé une macro pour enlever le texte d'une colonne contenue dans une autre colonne, par exemple
Col A----------------------------------- COL B
JANVIER 1998 en vacance--------- JANVIER
FEVRIER 1999 en Europe---------- FEVRIER
FEVRIER 2001 en Australie-------- MARS
etc...

va être transformé en
Col A---------------------- COL B
1998 en vacance-------- JANVIER
1999 en Europe---------- FEVRIER
2001 en Australie-------- MARS


Cette macro permettra de supprimer le texte "JANVIER", "FEVRIER", "MARS"... etc de la colonne où sont contenue toutes les infos (JANVIER 1998 en vacance, etc)
soit de supprimer le texte de la colonne A qui est contenue dans la colonne B (je pense partir de là car cela me semble une bonne idée, mais juste de changer un peu la macro non plus dans le but qu'elle me supprime un texte d'une colonne mais qu'elle m'affiche simplement dans une colonne 3 si le texte de la colonne 2 est bien contenue à la fin de la colonne 1, je sais c'est simple (ironiquement dit)).

Proposition :

1. dans le cas d'anomalie par rapport à ma base de donnée client

Vérification que la colonne "point de liv" contient le texte de la colonne "commune de liv" concernant les clients de ma liste (de 60) "listeclients"

=> par macro (j'ai trouvé sur internet une macro pour soustraire du texte, ce qui permet d'afficher une erreur lorsque ce texte n'est pas présent, en revanche cela ne fonctionne donc uniquement pour les clients de ma base de donnée, voir ci dessus...)

si le texte "commune de liv" (Strasbourg) n'est pas contenue à la fin de mon "point de liv" (Auchan tout seul par exemple), alors écrire "oui" dans la colonne "anomalie" d'à côté

2. Dans le cas où il n'y a pas d'anomalie:

Si le texte "commune de liv" (Strasbourg par ex) est contenue à la fin de la colonne "point de liv" (donc Auchan Strasbourg par exemple ici)
alors ce n'est pas une anomalie et la conformité est bonne


3. point de livraison (client) non contenue dans la base de donnée

si le "point de liv" n'est pas contenue dans ma base de client alors me le mettre le fond en orange, ou en jaune .. etc pour les mettre en évidences, et faciliter le traitement des données


j'aurais donc chercher divers renseignements à savoir :

- modifier la macro pour qu'elle ne m'efface pas les "communes de livraison "de de ma colonne "point de liv" (car c'est ce que cette macro fait) mais qu'elle m'affiche "oui" ou "non" à la place (dans la colonne anomalie), où le cas "conforme" est le cas 2 et le cas "non conforme" est le cas 1.
- compter le nombre d'anomalies par rapport à ma BDD
- tenir compte des données non présentes dans ma BDD (éventuellement de me les trier et de me les mettre soit au début soit à la fin de ma feuille)

la macro disponible ici avec l'exemple que j'ai repris ci dessus au début de mon post (trouvé grâce à google)
http://www.excel-downloads.com/forum/attachments/forum-excel/129330d1254131817-soustraire-donnees-texte-suppressions.zip

Je reste entièrement disponible envers quiconque sera assez charitable pour m'aider... :)

SOURCE (sans aucune volonté de faire de la publicité, si je n'ai pas le droit merci de retirer la source)
http://www.excel-downloads.com/forum/127894-soustraire-donnees-texte.html

Cordialement, et merci d'avance ! (et désolé pour le pavé)

H.
 
Dernière édition:

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

pour faire court:

j'ai enfaite plusieurs choses à faire que je n'arrive pas à réaliser.

Dans un premier temps j'aimerais identifier les points de livraisons corrects (ou non) par rapport à ma BDD de clients (60 environ), de façon automatique
un point de livraison est correct si il contient le nom du point de livraison (du client en gros) et de la commune de destination:

par exemple

point de liv: -----------------------------Commune --------------- Anomalie
AUCHAN STRASBOURG --------------- STRASBOURG--------------- NON

Ici ce point est conforme

point de liv: --------------- Commune --------------- Anomalie
AUCHAN --------------- STRASBOURG --------------- OUI

Ici ce point est NON conforme

et si je possède un client qui n'est pas dans ma base de donnée, alors il ne sera pas en anomalie car il n'existe pas de façon particulière d'écrire le point de livraison.
Je voudrais rendre tout ceci automatique

Je me demandais également si excel arrive à comprendre une faute de frappe et si c'est le cas s'il était possible d'intégrer les fautes de frappes à ma BDD (par exemple une fonction qui prend en compte 95% du texte contenue dans une cellule comme 100% si une lettre diffère) par exemple: AUCHAN est reconnue, AUCAN ne l'ai pas mais aurait il moyen ? (à moins d'énumérer dans ma BDD tous les moyens d'écrire un client (exemple SUPERU, SUPER U, SUPER-U etc)

Merci pour votre retour et merci d'avance pour votre aide

Cordialement
H.
 
Dernière édition:

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Désolé pour le triple poste (je suis vraiment naze en edition, ça bug excusez moi)

voici un exemple de fichier contenant les informations que j'aimerais automatiser (avec des noms fictifs etc)

http://cjoint.com/?0BtpuUDSkNs

Cordialement
 

Jocelyn

XLDnaute Barbatruc
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Bonjour le Forum,
Bonjour zored,

un essai en fichier joint avec cette formule en N4 à étirée vers le bas,

Code:
=SI(ET(NB.SI(BDD!$B$3:$B$5;SUBSTITUE(L4;" "&M4;""));ESTNUM(CHERCHE(M4;L4)));"Non";"Oui")
Cordialement

EDIT Bonjour BOISGONTIER
 

Fichiers joints

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Bonjour le Forum,
Bonjour zored,

un essai en fichier joint avec cette formule en N4 à étirée vers le bas,

Code:
=SI(ET(NB.SI(BDD!$B$3:$B$5;SUBSTITUE(L4;" "&M4;""));ESTNUM(CHERCHE(M4;L4)));"Non";"Oui")
Cordialement

EDIT Bonjour BOISGONTIER

:eek::eek::eek::eek::eek:

ça fonctionne !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

:D:D:D:D:D:D:D:D:D:D:D



je vais tester sur mon fichier :)

EDIT: ça fonctionne !!!!! au top vous m'avez fait gagner un nombre incalculable d'heures ... Merci serait trop insuffisant pour vous témoigner ma gratitude :):)

Au risque d'abuser encore de votre compétence serait il possible de distinguer les "clients" qui ne sont pas dans ma BDD ? Pour faire une étude plus précise...
Si vous voulez bien, sinon c'est déjà très sympathique de votre part

Cordialement
h.
 
Dernière édition:

Paf

XLDnaute Barbatruc
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Bonjour,

Code:
Private Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet, i As Integer, DerLig As Integer
Set WS1 = Worksheets("extrait")
Set WS2 = Worksheets("BDD")

With WS1
DerLig = .Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To DerLig
    If InStr(.Cells(i, 12).Value, .Cells(i, 13).Value) = 0 Then
        .Cells(i, 14).Value = "OUI"
    End If
Next
End With
End Sub
le code ci dessus ne présenterait un intérêt que pour gérer la présence ou non d'un client dans la base, mais
ce cas n'est pas pris en compte pour les raisons suivantes:
Code:
Post #2
et si je possède un client qui n'est pas dans ma base de donnée, alors il ne sera pas en anomalie
Classeur du post #3
Le client 1 et 2 sont dans la BDD, le client 4 ne l'est pas, il est donc en anomalie
A+
 

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Oui je m'étais emmêlé les pinceaux, effectivement j'aimerais compter un client en dehors de ma BDD comme une anomalie, mais j'aimerais le distinguer des anomalies des clients de ma BDD
(j'étais entrain d'éditer mon poste au moment de votre réponse)
Merci pour votre retour :)
 
Dernière édition:

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Merci beaucoup :):)
Pour finaliser mon fichier, serait il possible de "compter" indépendament l'un de l'autre les "oui" rouge (ceux qui ne sont pas dans la BDD) des oui non rouge (ceux dans la BDD) ? de façon à étudier plus précisément et à pouvoir comparer les 2 ... ?
(si vous voulez bien)

je vois la fonction compte mais pour distinguer les 2 "oui" ça me semble un peu difficile :(

Après normalement ça sera ok :cool:
(ce forum est formidable tout comme ceux qui y sont, je suis aller voir sur 2 autres forums et je n'ai pas encore eut de réponse)
 

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Re bonjour

Tout fonctionne, vous êtes formidable.... Merci, vraiment beaucoup
ça fait plaisir de voir qu'il existe des gens comme vous
C'est merveilleux
Si je peux vous aider en quoi que ce soit en retour... n'hésitez pas (mon point fort c'est la musique, la maintenance informatique (hardware software))

Encore merci :):)

EDIT: zut.. je viens de me rendre compte que excel ne tient pas compte des erreurs, par exemple

client 1 ST MICHEL ST-MICHEL-S-ORGE


y aurait il possibilité de faire comprendre qu'il ne s'agit ici pas d'une erreur à excel ? (je pense que c'est vraiment compliqué ..) excusez moi encore de vous en redemander.. Par exemple lorsqu'une localité est séparé par des "-" et que ceux ci ne sont plus présents dans le point de liv.... ça me paraît compliqué :\


EDIT: juste à cause des guillemets 3/4 du temps :eek:
Je pensais faire quelquechose comme une macro pour supprimer les guillemets et mettre des espace à la place, comme le remplacer mais en ciblant seulement une colonne... possible ?

Je vais tester je reviens vers vous tout à l'heure

EDIT 2: bon j'ai tester ça fonctionne !

Merci pour tout :)


Cordialement
H.
 
Dernière édition:

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Bonjour à tous,

Merci pour vos réponses, toutefois je me posais la question (excusez moi) si il était possible de considérer les clients hors de ma BDD comme n'étant pas des non conformités, comme n'étant pas des anomalies .... ?

Je vous remercie d'avance, j'essaie de le faire moi même mais je rame comme si j'étais en pleine galère (désolé jeux de mot pourrie)

cdlt
H.
 

Jocelyn

XLDnaute Barbatruc
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

re,

Une autre version avec 3 résultat possible en colonne N

Oui
Non
Non mais entrer le client dans la base

Voila a toi de voir
 

Fichiers joints

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Je crois que je vais créer un autel où je pourrais prier ta bonté, Jocelyn

Ça fonctionne, une fois de plus

merci encore :D:D
 

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Merci encore pour l'aide,

J'avais encore une petite demande: j'aimerais compter le nombre de type d'anomalie
En effet, j'ai dinstingué 9 type d'anomalie


J'ai également le nombre de fois où l'anomalie apparaît:


J'aimerais pouvoir compter le nombre de fois qu'apparaissent au total un certain type d'anomalie

pour prendre les exemples ci dessus, l'anomalie de type 7 "données en trop" se répètent au total 5 fois
J'ai utiliser Nb.si qui me compte seulement le nombre de 7 dans mon extraction, donc pour lui je n'ai ici 2 anomalies de type 7
Comment faire comprendre à excel de :
me compter le nombre de fois que l'anomalie apparaît x la récurrence de cette anomalie ? (pour arriver à 5 anomalies de type "données en trop") :confused::confused:

Dans le but final de sortir un joli graphique où je pourrais mettre une synthèse de toutes mes extractions avec un graphique croisé dynamique (ça j'arrive à faire au moins :rolleyes:)

Merci beaucoup (comparé à ce que j'avais besoin comme aide tout à l'heure ça me semble plus facile)

Je pensais à quelque chose du style somme de recherchev (valeur cherché par exemple 7, colonne 1 où il m'afficherait donc 3+2 ? je vais tester après manger je vous tiens au courant)
:)
 
Dernière édition:

Jocelyn

XLDnaute Barbatruc
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

re,

si l'on considère que le "répétition de l'anomalie" est sur une plage A2:A10 et que "Type d'anomalies" est en B2:B10 tu peux essayer une formule du genre :

Code:
=SOMME.SI(B2:B10;7;A2:A10)
a toi de définir les plage par rapport a l'endroit ou se trouve tes données

Voila testes et dis nous
 

zored

XLDnaute Nouveau
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

ok ça fonctionne pour ce qui est de compter :)

En revanche en appliquant à mon extraction votre formule pour mettre en évidence lorsque le client n'est pas dans ma BDD (j'ai mis comme texte, "Non, mais client absent de la BDD" ) cela fonctionne de manière aléatoire je vais tenter de voir pourquoi

EDIT 4: je repars d'une base plus saine je pense que j'ai dû me planter quelque part lorsque j'ai adapter la formule.

EDIT 5: enfaite cette formule est infaillible je ne l'avais pas vu sous le bon angle tout de suite, c'est plutôt bluffant merci encore

Si d'aventure que vous auriez encore un peu de temps, je me demandais s'il était possible de faire une même formule mais avec une base de donnée de client fixe. Je pensais partir sur le résultat de votre formule:

Si le résultat de la formule, pour un client X est:

Oui:

=> alors:
=> vérifier que la cellule "Point de liv" ne contient pas UNIQUEMENT une cellule présente dans une Base De Donnée2
SI
=> VRAI:
=> "Non" (vu que le point de liv sera contenue dans la BDD2)
SINON
=> "Oui"

Non:
on laisse le non


Du coup je me demandais où passais le "Non, mais non présent dans la BDD"

:confused::confused::confused:

EDIT6: Ok enfaite il faut l'enlever du coup. J'aimerais intégrer cette formule à votre formule:
=SI((RECHERCHEV(L3;BDD2;1;FAUX)=L3);"non";"oui")
Actuellement cela fonctionne, en gros si mon Point de liv(L3) est = à une des cellules de ma BDD2 alors il m'indique "non" (ce n'est pas une anomalie), sinon il m'indique "oui" (c'est donc une anomalie). en gros si mon point de liv est contenue dans ma base de donné de point de livraison fixe (BDD2), alors ce n'est pas une anomalie, sinon c'en est une. Le problème est que si je note quelque chose d'autre qui n'est pas dans ma BDD2, il me met un #N/A :(

Je cherche la solution & en attendant je me demandais comment l'intégrer à votre formule, ou alors éventuellement mettre une condition comme décrite dans mon EDIT5 ci dessus x)

Edit 7: Je vous joint le fichier test et des capture d'écrans si une âme charitable passerait dans les environs :p

Ma base de donnée

Mon extraction


Et les formules dans les 2 colonnes anomalies qui vérifient sur mes 2 BDD (merci encore à Jocelyn)

=SI(ET(NB.SI(client;SUBSTITUE(L4;" "&M4;""))=0;ESTNUM(CHERCHE(M4;L4)));"Non, client absent de la BDD";SI(ET(ESTNUM(CHERCHE(M4;L4));NB.SI(client;SUBSTITUE(L4;" "&M4;""))>0);"Non";"Oui"))

et

(ça c'est la mienne bien dégueulasse qui ne marche qu'à moitié :p)

=SI(OU(ET((RECHERCHEV(L5;BDD!$D$2:$D$652;1;FAUX)=L5);N5="Oui");N5="Non, client absent de la BDD");"Non";"Oui")

Cordialement,
H.
 

Fichiers joints

Dernière édition:

Jocelyn

XLDnaute Barbatruc
Re : Mettre en évidences des cellules contenant un mot par rapport à une base de donn

Bonjour,

Hum hum, cette fois vraiment pas sur d'avoir compris la demande,

Alors dans le fichier la réponse "Non, mais non présent dans la BDD" a disparu la formule répond tout simplement oui si le client existe dans la base de donnée 2 qu'il y ait ou non la valeur de la colonne M avec lui

le tout dans une seule formule

Voila regardes et dis nous
 

Fichiers joints


Haut Bas