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

[zored]

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)
Lien supprimé

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)
https://www.excel-downloads.com/threads/soustraire-donnees-texte.127894/

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

H.

 

[zored]

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

 

[zored]

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")

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 )

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)

 

[Jocelyn]

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 :

=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]

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"



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

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é )

=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.

 

[Jocelyn]

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

 

[zored]

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

Bonjour,

Super c'est exactement ça !

Du coup, ma colonne anomalie V2 est inutile je suppose ...?
Je vais appliquer ces 2 formules sur mon extraction, merci beaucoup je vous tiens au courant

EDIT1:

Je me demandais s'il était possible de rajouter une condition à la première formule (BDD1): rajouter la condition "si client 1 = client 1 + PREMIER mot de ma commune alors ce n'est pas une anomalie"

Comme l'exemple ci dessous, où il n'y a pas la place pour saisir entièrement la commune de destination (cela apparaît très souvent dans mon extraction) excusez moi de ne l'avoir remarqué que maintenant...

CLIENT 1 NOGENT NOGENT-S-SEINE

ce qui suit ci dessous ne fonctionnera qu'une fois que la condition ci dessus aura pu être rajouté

EDIT 2:

Je vais maintenant faire une automatisation de copie des lignes contenant "oui" en anomalie, pour automatiser la distinction des anomalies (que excel me copie la ligne qui contient "oui" dans une de 2 colonnes). Après je n'aurais plus qu'à codifier les erreurs, vu que le nombre de fois que l'anomalie se répète est compter automatiquement, et que le fichier me compte de manière également automatique le nombre d'anomalie PAR type d'anomalie

En bref, c'est génial..... merci encore beaucoup je vous tiens au courant lorsque ça fonctionne (ça je devrais pouvoir le faire moi même, je vois comment créer la macro pour insérer une ligne. Je sais juste pas comment mettre la condition (que si tel ou tel colonne contient "oui" alors il me copie la ligne entière dans un autre onglet "anomalie") Je vais faire quelques tests et je reviens vers vous

Ca, ça fonctionne (supprimer les espaces a fonctionner grâce à a macro que j'ai trouver sur le net ):

EDIT 3:
Je me demandais également si =SUPPRESPACE pouvait être adapté pour me supprimer uniquement les espace à la fin de cellule (d'une plage de donnée), pour ma BDD2 où j'ai extrait tous les clients s'écrivant d'une manière unique, le problème c'est que ces clients (s'écrivant de manière unique) on parfois un espace (au début ou à la fin) j'aimerais le supprimer. Mais le problème est qu'il y a des espace souvent entre les mots de ce point de liv:
par exemple

[ CLIENT 1 ] j'aimerais qu'il soit écrit en [CLIENT 1]
j'ai dont utiliser SUPRESPACE mais cela me met [CLIENT1]

en revanche cette macro fonctionne: (super lent mais fonctionne):

Sub SupprimeEspace()
For Each c In [A1:B20]
c.Value = Application.Trim(c.Value)
Next
End Sub

EDIT 4: J'ai rajouter 2 colonnes pour identifier si l'anomalie en est bien une:

Anomalie bdd1?

votre formule: =SI(OU(ET(NB.SI(client;SUBSTITUE(L3;" "&M3;""))=0;NB.SI(client1;SUBSTITUE(L3;" "&M3;""))>0);ET(ESTNUM(CHERCHE(M3;L3));NB.SI(client;SUBSTITUE(L3;" "&M3;""))>0));"Non";"Oui")

Anomalie présent dans BDD2? (je remets cette fonction simplement pour dire qu'il ne s'agit pas d'une anomalie si mont point de liv n'est pas contenue dans ma BDD2:
=SI(NB.SI(client1;$L3)=0;"non";"oui")

Anomalie FINAL (est ce réellement une anomalie finalement?)
=SI((ET(($N3="oui");($O3="non")));"oui";"non")

Voila voila, qu'en pensez vous ... ?


Cordialement
H.

 

[zored]

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

Bonjour,

J'essaie de me poser la question comment définir une anomalie... je commence à me perdre un peu je vais essayer de schématiser la chose dans l'autre sens

1.
Un point de livraison peut être une ["base fixe" + "commune de destination"] (BDD1 contient les clients "base fixe1" )
CLIENT 1 ORLEAN à ORLEAN

BDD1 contient CLIENT 1:
Ce n'est pas une anomalie si CLIENT 1 est écrit CLIENT 1 ORLEAN ;
c'est une anomalie si CLIENT 1 ne contient pas ORLEAN (ou une quelconque commune de livraison, NOGENT pour l'exemple de mon précédent post si on arrive à mettre cela en place)

BDD1 ne contient pas CLIENT 1:

Alors passer à l'étape suivante:

2.
Un point de livraison peut être une "base fixe" seul (BDD2 contient les clients "base fixe2" qui s'écrivent sans la commune)
CLIENT 1 à ORLEAN

SI BDD2 contient CLIENT 1:
alors ce n'est pas une anomalie

SI BDD2 ne contient pas CLIENT 1:
alors c'est une saisie ponctuel et ce n'est pas une anomalie

Au final un point de liv est une anomalie si:
=> si elle est contenue dans BDD1 mais ne s'écrit pas ["base fixe" + "commune de destination"]
=> si elle est contenue dans BDD2 mais ne s'écrit pas sans rien à côté :
CLIENT 1 P/C BOB à TOULOUSE où CLIENT 1 n'est pas dans BDD1 mais dans BDD2
est une anomalie

Pour tous le reste il y a masterca.. euh il n'y a pas d'anomalie

j'espère que vous me comprendrez

EDIT: je suis entrain d'essayer différentes combinaisons de formule (je crois avoir compris la votre, j'essaie de l'appliquer avec les quelques conditions ci dessus en plus)



Ce schéma est fait à la main mais résume mon objectif

je vais essayer différentes combinaisons de formules en partant de la votre, je crois avoir compris comment celle ci fonctionne à parti pour ce passage :

SUBSTITUE(L4;" "&M4;""))=0
C'est donc pour vérifier que la cellule point de liv (L4) contient bien la commune de destination traduit de la façon:
SI L4 - (espace)&M4 donne 0 (rien) etc
bref j'essaie de bricoler quelque chose mais ...




Bien à vous & merci beaucoup pour tout

Cordialement
H.

 

[zored]

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

Bonjour le forum,

j'ai esquiver une partie de la difficulté en ajoutant le client, s'il est répété par le même orthographe, dans la BDD2.

EDIT2: je cherche à utiliser une fonction pour remplacer tous les accents (majuscules ou minuscules) par des caractères sans

je suis tombé sur ce sujet:
https://www.excel-downloads.com/threads/convertir-minuscules-en-majuscules-sans-accent.22153/
mais cela ne fonctionne que pour les minuscule, hors par exemple ENTREPÔT devrait être remplacer par ENTREPOT mais cela ne fonctionne pas (vu que le Ô est en majuscule)

EDIT 3: toujours le même problème (exemple ci dessous ou ci dessus avec NOGENT) par rapport au nom de la ville et BDD1, il faudrait pouvoir comparer à 3 choses le client de BDD1:
client 1 + commune 1 à commune 1 = ça marche
ex: AUCHAN STRASBOURG à AUCHAN
client 1 + commune 1 à la commune 1 = ça marche pas (anomalie car manque le "la")
ex: CLIENT 1 NOGENT à NOGENT S SEINE
autre exemple : CLIENT 2 BELLERIVE à BELLERIVE SUR ALLIER

Je continue de galérer pour le reste, ça avance mais doucement ^^ merci pour tout déjà

Cordialement
H.

 

[zored]

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

Bonjour le forum, Bonjour Jocelyn si vous êtes encore dans les parages,

Voici une première ébauche de l'exemple que j'essaie de concrétiser, j'y suis presque mais je comprends pas pourquoi cela ne fonctionne pas, je pense que cela doit être une histoire de case non texte ou quelque chose comme ça. Je vous joint le fichier d'aventure que vous ayez le temps d'y jeter un coup d'oeil, j'ai essayer de modifier votre formule pour qu'elle prenne en compte les conditions que j'ai rajouter dans des colonnes (que je masquerais par la suite)

Comme vous voyez cela fonctionne pour l'avant dernière ligne j'arrive à comparer au premier mot contenue dans ma cellule commune de destination (je suis assez content j'ai enfin compris votre formule)

En revanche sur ma dernière condition (vérifié en dernière ligne du tableau) celui ci apparaît en anomalie, alors qu'il ne devrait pas (voir fichier si vous voulez bien)

EDIT2: ça fonctionne presque complètement j'ai réussit à faire marcher sur la même formule cette vérification:

CLIENT 1 NOGENT à NOGENT S SEINE n'est pas une anomalie = OK ça fonctionne
par contre
CLIENT 1 LA CHAPELLE à LA CHAPELLE n'est pas une anomalie mais ça ne fonctionne pas

Bien à vous
H.

 

[zored]

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

Petit up si quelqu'un a une idée de comment résoudre mon problème

 

[Jocelyn]

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

Bonjour zored,

un nouvel essai

Cordialement

 

[zored]

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

Bonjour,

Merci beaucoup !!!!!
je teste et je vous tiens au courant

Cordialement,
H.

 

[zored]

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

Bonjour Jocelyn,

Merveilleux ! c'est exactement ce que je souhaitais faire, mais j'avais divisé sur 2 formules pour identifier l'anomalie, cela ne fonctionnait même pas. En une seule formule vous faites tout fonctionner.
Splendide, merci beaucoup (encore une fois)

Je vais chercher à voir comment vous avez fait, maintenant que j'ai compris votre formule

EDIT: pouvez vous m'expliquer la plage " G23:G25 " svp ? je ne comprends pas, c'est pour comparer à une plage vide .. ?



Pour toute votre aide,à moins que vous ayez un iphone jailbreak ou que vous voulez apprendre une partition au piano, je ne saurais trop comment vous remercier.

Encore une fois un grand bravo à tout le monde, spécialement pour vous jocelyn

Je vous souhaite une excellente journée

Cordialement
H.

 

[zored]

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

Bonjour, j'espère que vous allez bien

Je reviens vers vous pour prendre des nouvelles, si vous pouviez m'expliquer ce que je n'ai pas compris dans mon poste précédent (tout fonctionne, je cherche juste à comprendre )

je vous remercie d'avance

bien cordialement
H.