Comparaison de liste de nombres sur Excel par rapport a un PDF

[Drazuhl]

Bonjour,

Je travaille dans l'export et afin de m'assurer qu'il n'y a pas d'elements toxiques dans les produits que nous sous traitons, nous demandons a nos fournisseurs de nous envoyer la liste de tous les elements qui sont utilises pour chaque produit.

Cette Bill of Material contient le nom de chacun des elements et son CAS number (son numero de code international). Cette liste m'est fournie dans un document Excel.

Pour le moment lorsque je recois cette liste (qui peut contenir plusieurs centaines de CAS number), je dois la comparer "a la main" avec deux autres fichiers: La liste des materiaux dits SVHC (substance of very high concern) que j'ai sur Excel (mais avec parfois plusieurs CAS number dans une seule case...) et une autre liste, bien plus longue de materiaux dont certains sont des CMR (Cancerigene, Mutagene ou mauvais pour la Reproduction) et qui est sur PDF...

Pour le moment, je copie dans un premier temps la colonne des CAS number que je dois verifier, je la colle dans une nouvelle colonne et je la trie de A a Z afin de pouvoir eliminer les doublons (parceque le meme element peut etre utilise dans plusieurs parties du produit). Cela m'evite deja de devoir verifier plusieurs fois le meme element...

Mais a partir de la, la seule solution que j'ai trouve consiste a prendre chaque element, Ctrl C, Alt Tab dans le document Excel des SVHC, Ctrl F, Ctrl V et Enter. Si je ne trouve rien, j'Alt Tab a nouveau pour atteindre le document PDF et je Ctrl F a nouveau...

Sachant qu'il faut repeter presque une centaine de fois l'operation, c'est tres fastidieux...

Pour rajouter des joyeusetes, dans le fichier PDF des CMR, certains elements y existent, mais ne sont pas des CMR, simplement des produits irritants, ou inflammable,... qui ne sont pas interdits. Et certains elements sont presents 2 fois...

Est-ce que quelqu'un saurait m'expliquer comment je pourrais ameliorer mon process? Est-ce que je pourrais faire une formule qui me surlignerais automatiquement d'une certaine couleur tous les elements donc le CAS number apparait dans l'un ou l'autre des fichiers?

Ou faire un Ctrl F multiple (qui me trouverait tous les elements de la colonne d'un coup)?

Si au moins je pouvais sauter tous les elements pour lesquels je suis sur qu'ils n'apparaissent dans aucun des deux fichiers, je gagnerais beaucoup de temps. En general toute cette recherche aboutit a l'absence de produits interdits (et tant mieux!), je trouve tout au plus des traces de produits irritants ou polluants (dans des quantites infimes et legales).

C'est sans doute un peu flou et si vous avez des questions, n'hesitez pas.

Merci d'avance,

Matthieu

 

[st007]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

J'y suis,
export du pdf, 14800 lignes, 2,5 Mo ou 1,9 Mo zippé.
Forcément par mail adresse par MP ?

 

[CISCO]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Bonsoir

Ou par le cloud (Dropbox, ou autre) ?

@ plus

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

St007 est trop fort =)

Il m'a d'abord envoye par mail le meme type de fichier que precedemment mais sur toute la liste.

Mais il a fait encore mieux, il a reussi a exporter proprement les 2 colonnes qui m'interessent!

J'ai cru un moment qu'il manquait un tableau mais au final j'ai vu qu'il etait dans un autre onglet

En plus il est maintenant super leger, on peut meme le mettre sur le forum. Donc pour ceux que ca interesse je le met en PJ.

Maintenant je vais juste retravailler les quelques cases qui ont un [1], [2] ou [3] (ex 97-64-3 [1]) a la main, ca devrait pas trop me prendre de temps avec ctrl f all.

Ensuite j'incluerais la liste des CMR et je vais avoir un bon fichier bien propre pour faire mes comparaisons.

Je verrais ensuite si je peux l'ameliorer avec quelques formules (si par exemple la liste des categories de dangers pour chaque element avait ete dans une seule cellule, ca aurait ete interessant d'afficher le contenu de cette cellule directement a cote des elements collores par la formule countif() pour pouvoir les verifier d'un coup d'oeuil. Mais ce n'est pas le cas tout le temps).

Je vous tiendrais au courant.

Merci encore!

 

[CISCO]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Bonjour à tous, bonjour Drazuhl, st007

Et bien, ça avance dans le bon sens.

Dans ma proposition précédente, j'avai mis en évidence les éléments avec une mise en forme conditionnelle, mais on doit pouvoir faire plus pratique, par exemple en listant les produits contenant ces éléments "litigieux". Ainsi, tu n'auras plus à les rechercher. A toi de nous donner un petit fichier exemple, ayant une forme te convenant, pour que nous puissions te proposer quelques formules faisant cela.

@ plus

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Hello,

J'ai passe la matine a clean le fichier (les fameux [1], [2],... mais aussi des nombres qui se transformaient tout seul en date puis carement en nombre (par exemple 8909-04-02 devenait 02/04/8909 et si j'essayait de changer la mise en forme de la colonne ils devenaient quelque chose comme 29098398... Heureusement ca creeait une erreur et en utilisant le correcteur d'erreur j'ai pu tout retrouver et corriger a la main...).

J'ai aussi importe et clean la list des SVHC il y en avait beaucoup moins.

J'ai utilise ton idee de mise en forme conditionelle et c'est deja vraiment pratique, je copie colle la colonne des CAS number du fichier a verifier et paf ca me mets en couleur automatiquement (une couleur pour les CMR, une pour les SVHC) les produits qui posent probleme.

Il n'y a en general quasiment aucun SVHC (tant mieux vu que ce sont des produits toxiques...) dans les listes a verifier. D'autre part, un produit qui passe au rouge (couleur de la colonne SVHC) est FORCEMENT problematique.

Alors que pour les CMR, il y a quand meme plus de produits qui passent au Orange (couleur de la liste du PDF) et parmis eux, seulement quelques uns sont problematiques (c'est en verifiant si c'est des CMR ou pas que je peux le savoir).

Il faut aussi noter qu'il y a pas mal de doublons, vu que certains elements sont utilises dans plusieurs pieces des produits.

Vous pouvez voir sur le fichier ci-dessous que sur 163 elements a verifier sur mon exemple representatif de BOM, je n'en ai que 31 qui sont colores et une fois les doublons retires il n'en restent plus que 17! Donc deja je divise le travail de verification par 10!

Dans le fichier que je vous link, j'ai deja trie par couleur et par nombre, mais pas encore enleve les doublons.

Evidemment dans un monde parfait, Excel pourrait m'indiquer a cote de chaque case concernee quel type de danger les elements en orange representent. Voir trie directement les CMR et les autres.

Mais comme vous pouvez voir dans le fichier, l'export depuis le pdf risque de rendre cela impossible. Il y a des fois plusieurs CAS numbers pour la meme liste de risque, parfois plusieurs risques par cellule, parfois plusieurs cellules de risque par Cas number. Bref c'est melange et meme pour la verification a la main, je vais utiliser le PDF plutot que la colonne B parcequ'il y a trop de risques de melange.

Mais c'est deja top!

A la limite je pourrais encore ameliorer le tout si lorsque je copie colle ma colonne de CAS number, Excel colorie en orange et en rouge et qu'une autre fonction, importe toutes les cases rouges et oranges ailleur dans la feuille ou dans une nouvelle feuille, enleve les doublons automatiquement et conserve le rouge pour les SVHC. Ensuite je n'aurai plus qu'a verifier la dizaine de cellule restante et j'indiquerais avec du rouge ou du vert si ce sont des CMR ou non.

Si vous avez d'autres idees je suis bien sur preneur

Merci merci!

 

[st007]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Tu peux trouver tout les saut de ligne dans les cellules par rechercher et "Ctrl"+"Enter" comme critère

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Ok merci. Bon je pense que maintenant c'est deja regle.

J'ai demande a des collegues de m'envoyer des fichier a verifier et ca carbure bien

J'ai ameliore le fichier en changeant le formatage automatique pour que les SVHC colorie la case en rouge mais que ceux de la liste du PDF colorient le TEXTE en orange. Comme ca si j'ai un SVHC qui est aussi un CMR je peux le voir (au final certains SVHC sont acceptables en faible quantites alors que les CMR ne le sont pas).

Si je pouvais faire quelque chose pour automatiquement extraire les cases concernees (et en enlevant les doublons) ca pourrait etre sympa.

Est ce que ca serait possible de faire quelque chose pour automatiser l'equivalent d'un Ctrl F All (par exemple en cliquant sur la cellule ca m'affiche toutes les cellules avec le meme nombre)?

 

[CISCO]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Bonjour

Cf. une possibilité en pièce jointe pour supprimer les doublons. Comme il s'agit d'une formule matricielle, il faut la valider avec Ctrl+ maj+entrée.

@ plus

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Hello,

Desole pour le temps de reponse j'ai ete un peu occupe.

Vous allez rire, mais au final, j'ai fini par trouver le fichier excel du PDF sur un site europeen... J'etais persuade que mes collegues, qui galerent avec ces BOM depuis des annees avaient deja verifie donc je n'avais pas vraiment cherche...

Les tables 3.1 et 3.2 sont accessibles ici: Ce lien n'existe plus

La bonne nouvelle c'est qu'au niveau formattage c'est bien plus propre que l'extraction bourrine depuis le PDF, la moins bonne c'est que tout le travail de nettoyage de la liste (enlever les [1],...) est a refaire et ca m'avait pris plusieurs heures...

Avant de m'attaquer a ca, je voudrais etre sur que ca vaudra le coup.

Par exemple lorsque j'ai une ligne comme ca:

[A1] beryllium [B1] 7440-41-7 [C1] "Carc. 1B Acute Tox. 2 * Acute Tox. 3 * STOT RE 1 Eye Irrit. 2 STOT SE 3 Skin Irrit. 2 Skin Sens. 1"


(Il n'y a ici qu'une ligne, avec dans la cellule [C1] tout un paquet d'information).

J'ai plusieurs options. Ma fonction countif() qui aura trouve ce CAS number mettra en orange ma cellule concernee dans la liste en cours de verification.

Ce que j'aimerais c'est que si dans la cellule [C1] il y a parmi les Acute Tox. et autres, une ou plusieurs informations specifiques (comme Carc.2 ou Muta.1B), Excel soit capable de le voir et me mette ma cellule de la liste a verifier d'une couleur speciale.
En gros ca serait un countif() qui serait lui meme sous condition (si la cellule des CAS number contient un nombre identique a celui de la cellule a verifier ET que la cellule contient le mot Carc. 1 OU le mot Repro. 2A OU ,.... ). Mais le probleme c'est que je ne sais pas faire pour trouver une partie des informations de la cellule, seulement trouver une cellule au contenu identique.

C'est tres peu clair et j'espere que vous arrivez a suivre...

Si cela n'est pas possible, alors est ce que ca serait possible lorsque Excel trouve un CAS number identique dans la liste, qu'il affiche dans la cellule adjacente une copie de la cellule de la 3eme colonne? Comme ca je pourrais verifier d'un coup d'oeuil.

Je n'ose meme pas me relire de peur de ne plus comprendre ma question ><' Si vous avez besoin de plus d'explication je comprendrais...

Merci beaucoup,

Matthieu

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Bon deja j'ai trouve une reponse moi meme, on peut retrouver une cellule en fonction d'une partie de texte qu'elle contient.

Dans le fichier d'exemple que j'ai attache, j'arrive a collorier une cellule si elle contient quelque chose comme Carc.1 et je peux mettre plusieurs conditions donc je vais pouvoir couvrir tous les types de CMR.

Depuis que vous m'avez montre le formatage conditionnel je suis devenu fan ^^

Comme vous pouvez le voir, j'utilise toujours mon =countif() pour colorer les cellules de la colonne a check si ce CAS number apparait dans ma liste de reference (et inversement meme si sur 16000 lignes, ca ne me sert pour le moment pas a grand chose de colorer les cellules de la colonne de reference).

Maintenant ce qui serait magique ca serait de reussir a faire un formatage conditionel pour pouvoir automatiquement (grace au fichier excel propre, alors qu'avant ca aurait ete impossible) par exemple mettre en rouge gras les Cas numbers qui sont non seulement dans la liste, mais plus specifiquement qui sont des CMR.

En gros si je pouvais reussir a faire comprendre a Excel la chose suivante:

Si une cellule de la colonne a check se retrouve en Orange, va chercher dans la colonne de reference les differentes apparitions de ce CAS Number et si la cellule adjacente est coloree (parcequ'elle contient le mot Carc.1 OU Muta.1A OU Muta.1B, OU,...) alors change le formatage de la premiere cellule.

Apres si on veut mettre une cerise sur le gateau, on peut imaginer que ca importerait dans la cellule adjacente a celle de la colonne a check, la cellule adjacente a celle de la colonne de reference (pour qu'on puisse voir directement quel type de CMR c'est). Mais c'est pas la priorite.

Encore une fois, je dois pas etre tres clair...

Merci,

Matthieu

 

[Drazuhl]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Hello,

Je ne sais pas si des gens liront ce post, mais je voulais juste annoncer que j'ai reussi a regler mes problemes!

Grace a la liste excel officielle que j'ai retravaille, j'ai pu facilement mettre en place mon formattage conditionel.

Ensuite, avec une formule du type:

=IF(ISNA(MATCH(I2,C:C,0)),"",INDIRECT("d"&MATCH(I2,C:C,0)))

J'ai pu faire apparaitre dans la colonne adjacente, les informations correspondants au CAS number verifie.

Donc au final, apres avoir cache les colonnes avec les donnees (apparement on ne peut pas faire de formatage conditionnel a partir d'une autre page), il me reste 3 colonnes:

-1 dans laquelle il faut coller les CAS numbers a verifier. Les cellules s'auto formattent de differente facon si le CAS number est un SVHC ou si il fait parti de la liste incluant les CMR.

-1 dans laquelle la formule IF(ISNA(MATCH....) va aller chercher le nom de l'element qui correspond au CAS number si il apparait dans la liste incluant les CMR.

-1 dans laquelle le meme type de formule va aller chercher le type de risque associe a cet element.

Donc ca donne un fichier tres simple, et il suffit d'un copier coller pour obtenir visuellement toutes les informations!

Si ca interesse quelqu'un de voir ce fichier, envoyez moi un PM, il est un peu trop gros pour le forum (500Ko).

Merci en tout cas pour votre aide, ca va changer la vie de plusieurs de mes collegues!

Matthieu

 

[st007]

Re : Comparaison de liste de nombres sur Excel par rapport a un PDF

Hello,
Les tables 3.1 et 3.2 sont accessibles ici: Ce lien n'existe plus

La bonne nouvelle c'est qu'au niveau formattage c'est bien plus propre que l'extraction bourrine depuis le PDF, la moins bonne c'est que tout le travail de nettoyage de la liste (enlever les [1],...) est a refaire et ca m'avait pris plusieurs heures...

Merci beaucoup,

Matthieu

merci pour le bourrin

lol