Formule pour rechercher plusieurs résultats
Bonjour,
Tout d'abord, je vous en remercie d' essayer de trouver une solution à mon problème....:-)
Vous trouverez ci-joint 3 tableaux :
- Un vous montrant le résultat final
- Un tableau de travail où je souhaite avoir la formule
- Le dernier où se trouve les données à récupérer
Ces trois tableaux sont à l’origine très volumineux (beaucoup de ligne/colonne), je les ai anonymises et laissé 3 exemples uniquement et aussi pour réduire leur taille qui ne passait pas pour le Forum.
Comme vous pourrez le constater, je dois trouver pour X fournisseurs les N° CEP ou DMF.
Idéalement une formule qui prendrait :
le fournisseur avec son adresse complète (prise en colonne D du tableau travail) + Espace + N°CEP ou DMF + ; + aller à la ligne pour mettre le fournisseur suivant et ainsi de suite.(voir le tab de résultat)
Dans le tableau de travail, je me suis dit que faire deux colonnes avec 2 recherches serait plus simple mais si vous arrivez à additionner les formules dans une seule case je suis preneuse.
Par contre, je pense qu’il faudrait peut-être avant toute recherche X modifier la clé de recherche via une formule (Modifier les / en – et ajouter FR espace pour les NL) ? Ici vous n'avez que 3 exemples mais le tableaux d'origine a une centaine de ligne.
Je ne sais pas si une telle formule existe ou soit possible à créer mais j’avoue que cela dépasse mes compétences.
Dans tous les cas merci par avance pour aide et si vous réussissez j’avoue ça serait formidable je n’aurais pas à faire ces 1600 lignes à la main
Bien cordialement,
Ludivine
Bonjour Ludivine,
Il faudrait quelques explications complémentaires ou confirmations concernant les articulations entre les 3 tableaux. Je comprends qu'il y a les données de base (tableau-avec-cep-et-dmf-red.xlsx), le résultat attendu (tableau-resultat-final.xlsx) et entre, le tableau de travail.
Pour le tableau de travail :
- Peux-tu confirmer que les colonnes en bleu sont pré alimentées et que la colonne "C" [Clé de recherche colonne G] (à quoi fait référence colonne G ?) est à utiliser après transformation comme clé de recherche dans le classeur des données de base, colonne "Application Code" ?
- Les transformations (+ "FR " devant "NL..." et "/" => "-") indiquées dans le message sont elles exhaustives ?
- sinon il faut donner toutes les règles de transformation.
Pour le résultat final :
- Peux-tu confirmer que seule la colonne "Site(s) de fabrication + CEP+DMF" est à alimentée à partir du tableau de travail en utilisant le "Code CIS" comme clé de recherche ?
Dernier point, je le tente au cas où
Cdlt,
Cylfo
Bonjour Cylfo,
Tout d'abord merci beaucoup de ton message pour essayer de m'aider sur cette formule qui me semble impossible pour moi et excuse moi du délai de ma réponse alors que toi tu as été répide ;-).
Tu trouveras ci-dessous les réponses a tes questions :
- Peux-tu confirmer que les colonnes en bleu sont pré alimentées et que la colonne "C" [Clé de recherche colonne G] (à quoi fait référence colonne G ?) est à utiliser après transformation comme clé de recherche dans le classeur des données de base, colonne "Application Code" ?
=> oui les colonnes en bleu sont mis pré alimentées.
=> La colonne G c'est juste la colonne G car j'ai tout anonymizer c'est un tableau pro alors je fait attention. Oui c'est bien ça il faut la transfomer pour que la recherche via la colonne "Application Code" du tableau des données se fasse correctement : t'as tout compris :-)
- Les transformations (+ "FR " devant "NL..." et "/" => "-") indiquées dans le message sont elles exhaustives ?
- sinon il faut donner toutes les règles de transformation. => il peut y avoir aussi 00/H/0111-0112/001, des "," 00-0570,00-0571, des "-" 026544027 - 026544039, NL-H-5060-001-MR
- Bref il y a beaucoup de combinaison possible mais les fréquentes sont celles que vous avez : DE-H-4524-001-MR et FR NL50862. De plus, ça c'est du luxe au pire si la formule ne modifie pas bien cette colonne je le ferai à la mains se sera toujours moins pire que le reste avec la grosse formule de crecherche via le code CIS. Et puis pour les autres combinaisons je pourrais peut-etre me debrouiller en modifiant la formule que vous m'aurez faite pour l'adapter aux autres combinaisons....
- Peux-tu confirmer que seule la colonne "Site(s) de fabrication + CEP+DMF" est à alimentée à partir du tableau de travail en utilisant le "Code CIS" comme clé de recherche ? => oui je confirme là encore tu as tout compris
Pour finir on a EXCEL 365.
Merci encore pour ton aide j'espère que tu vas réussir
A bientôt
Ludivine
Bonjour Ludivine 27
Pour finir on a EXCEL 365.
Merci de changer la version dans votre profil SVP
A+
Bonjour Ludivine,
Voir en pièce jointe. J'ai regroupé les 3 fichiers en 1 seul. Il est possible de les séparer mais vu la relative complexité, je pense qu'il serait préférable de voir ce fichier comme une étape intermédiaire où :
- Onglet "Extraction" : tu collerais les données de l'extraction (ne pas écraser les colonnes dont le titre est sur fond orangé/marron foncé, elles contiennent des formules)
- Onglet "Tableau de travail" : tu initialises les colonnes [A] à [C] et tu peux saisir une valeur de clé dans "Clé de recherche manuelle" si la clé calculée "Clé de recherche" n'est pas correcte et que tu peux pas modifier la formule pour intégrer une nouvelle règle de conversion.
- Onglet "Résultat" : contient le résultat attendu (du moins c'est l'objectif
)
Je te laisse regarder, il y a quelques écarts avec l'attendu mais sauf erreur, ce sera difficile de faire mieux faute de règles plus précises.
Si le résultat te convient, je te détaillerai les étapes de la transformation mais, là aussi, je te laisse en prendre connaissance. Attention, j'ai modifié tous les tableaux en tableaux structurés.
Cdlt,
Cylfo
Bonjour Cyflo,
Merci beaucoup, cela a du te demander énormément de temps....
Pour les 3 onglets dans un je comprends c'est plus partique. J'espère que je pourrais le faire car ces 3 Excels sont très volumineux.
Onglet extraction : du coup je vais devoir reprendre/recréer/ajouter toutes ces colonnes oranges dans mon tableau.
Peux-tu m'expliquer ce que tu voulais faire/trouver/cherche pour chaque colonne ?
Exemple que je crois avoir compris ;
Colonne K => pour trouver le site de prod
Colonne J : je ne vois pas en quoi elle te serre ??
Colonne L => pour trouver le code CEP
Colonne M et N => trouver la version et version max => attention dans le résultat final on doit pas mettre le N° de version on peut donc peut-être supprimer ces deux colonnes ???
Colonne 0 => CEP avec version max
Colonne P : je ne vois pas en quoi elle te serre ??
Colonne Q : je ne vois pas à quoi elle serre car tu as la colonne 0 ?
Colonne R :=> pour trouver le code DMF
Colonne S : je ne vois pas en quoi elle te serre ??
Colonne T : je ne vois pas à quoi elle serre car tu as la colonne R ?
Colonne U : la formule m'a l'ai hyper complexe pourquoi n'avoir pas fait simplement = D7 + R7 et D7+Q7???
De plus, pour U45 tu trouves R0-CEP 2018-120-Rev 00 alors qe le CEP c'est R1-CEP 2004-124-Rev 05 ???
Du coup je me demande si l'année dernière je n'ai pas fais une erreur et qu'au lieu de mettre 1 site de prod Granule j'aurais du en mettre deux avec les deux CEP différent si tu vois ce que je veux dire ?
Onglet de travail :
Qu'est ce que tu entends par "Initialiser" desoléee je veux être sure de bien comprendre ?
Colonne D : tu essaies d'uniformer le code pour le trouver dans la colonne D de l'extraction c'est ça ?
Colonne E : tu la mets à la mano si la formule ne marche pas c'est ça?
Colonne F : tu vas chercher le resultat trouvé dans l'onglet extrac mais pourquoi ne pas avoir mis simplement = U44+";"+U45+";"+U46+";"+U47+";"+U48+";"+U48+";"+U49+";"+U50+";"+U51+";" ????
Colonne G : idem pourquoi ne pas avoir mis =U7+";"+U8+";"+U9 ?
Colonne H : idem via une formule tu as additionné les colonnes F et G
Onglet résultat :
Il est obligatoire d'avoir un point virgule apres chaque info, peux-tu les ajouter dans l'onglet Extraction ou Tableau de travail désolée c'est une obligation ?
Euticals S.p.A Viale Milano 86/88, 26900 Lodi ITALIE ASMF No.97-018 ;
Excella GmbH & Co. KG Nürnberger Strasse 12, 90537 Feucht, ALLEMAGNE CEP 2002-055 ;
Archimica S.p.A. Viale Milano 86, Lodi, LO, 26900, Italie ;
De plus, peux-tu enlever les / après les pays et avant les CEP ?
Et comme je l'ai indiqué plus haut tu peux enlever les version des CEP et DMF ?
Voilà j'espère que l'on pourra enlever et ajouter ces deux points car ton résultat à l'air pas mal ;-))))
Merci encore car tu es le seul à m'avoir répondu
A bientôt
Cordialement
Ludivine
Bonjour Ludivine,
Dans le fichier joint, j'ai répondu à tes remarques et questions dans l'onglet "Explications". J'ai également quelques questions (avant dernière colonne) et je relivrerai une nouvelle version après réception de tes retours (à intégrer si tu le veux bien dans la dernière colonne).
Cdlt,
Cylfo
Bonjour Cyflo,
Bonne Année 2024 !!!
Merci beaucoup pour toutes tes explications tu trouveras dans l'Excel mon retour.
Bien cordialement,
Ludivine
Bonjour Ludivine,
J'ai apporté les modifications mais sans supprimer les colonnes liées aux n° de version CEP car au final elles sont nécessaires.
Je te laisse vérifier que les résultats sont conformes aux l'attendus et si c'est le cas, concernant l'actualisation des classeurs en production, il me faut quelques infos complémentaires :
- Le classeur contenant l'extraction : n'est il pas écrasé à chaque extraction réalisée à partir du logiciel ?
- Si oui, ajouter les colonnes ne servira à rien et il faut trouver une autre solution, PowerQuery (intégré à Excel) pourrait être une bonne piste, il permettrait de rapatrier les données de l'extraction dans le classeur.
- Comment est généré le classeur contenant l'extraction : un nouveau classeur à chaque nouvelle extraction ? extraction sauvegardée automatiquement lors de l'exportation des données ? extraction générée toujours dans un même dossier ? etc.
- Le classeur de travail : comment est-il géré ? il n'y a qu'un seul classeur de travail ou il en existe différentes versions ?
- Le classeur résultat : est il créé à un moment précis ou bien actualisé au fil du temps ?
Une piste pourrait être d'adapter le classeur que je t'ai fourni en mettant à jour quasi automatiquement (menu "Données" / "Actualiser tout") et à l'aide de PowerQuery les données des onglets "Extraction" et "Tableau de travail" à partir des classeurs sources de l'extraction et du classeur de travail mis à jour par vos collègues. Les données des onglets "Extraction" et "Tableau de travail" pourraient également être limitées aux seules données nécessaires à l'obtention du résultat.
Une autre piste, serait de mettre à jour les données à l'aide d'une macro. La macro, te permettrait de sélectionner, via une boîte de dialogue, les classeurs d'extraction et de travail et de rapatrier les données dans le classeur "résultat".
Peux-tu me transmettre les 3 classeurs de production mais en ne laissant que la ligne de titre et en supprimant toutes les données ? Quelles que soit l'option qui sera retenue, je n'ai pas besoin des données.
Cdlt,
Cylfo
Bonjour Cyflo,
Oui c'est bon merci
Extraction :
On fait une seule et unique extarction en début de chaque année car ce travail d'état des lieux est a faire pour les ans.
Donc non pas ecrasé, une extraction pour chaque année
On range cette extraction dans un même dossier mais dans un sous-dossier différent, un sous dossier par annéé.
Travail :
Comme pour l'extraction, il est généré une seule fois en début d'année c'est une copie d'un tableau existant qui est mis à jour quaotidiennement.*
Il n'y a qu'un seul fichier de travail. Une fois tous les modifs apportées et les données importées on l'enregistre on fait une copie et sur cette copie on supprime les colonnes blanbles et le tableau de travail devient notre tableau résultat.
Je ne connais ni n'utilise Power Query c'est avec Excel 365 ou faut l'acheter ?
Je t'envoie les fichiers et si tu as besoin ou veux on peut faire un teams lorsque tu voudras ajouter les données qu'on fasse cela ensemble pour voir si tout est ok???
Je te donne égelement mon tel si besoin : edit modo : num téléphone supprimé
Merci merci encore pour tout
Bonne jorunée,
A bientot
Ludivine
Bonjour Ludivine,
J'ai effectué un rapprochement des en-têtes de colonnes entre les fichiers transmis initialement et ceux que tu as transmis le 17/01 ci-dessous et ... ça ne correspond pas vraiment
Je te joins un fichier dans lequel tu as le résultat du rapprochement. Dans le 1erbloc "Fichiers transmis initialement", les lignes en rouge correspondent aux colonnes utilisées dans les formules et qui n'ont pas de correspondance dans les derniers fichiers transmis qui sont listés dans le 2ème bloc à droite.
Pour que je puisse poursuivre, il faut que tu renseignes les colonnes à utiliser dans la colonne "Champ Dx à utiliser" et si c'est ok, la ligne passera au vert.
Cdlt,
Cylfo
Bonjour,
Oups je crois que je suis allée trop vite et que je ne t'ai pas donner le bon tableau comme ils ont supprimé mon message à cause du numero de tel ben je sais plus ce que je t'ai envoyé...
Bref je te renvoie le tableau de travail "Tableau officiel B1" où il ne manque plus que les code CEP et/ou DMF.
Ce tableau pour rappel deviendra le tableau de résultat final une fois les colonnes en blanc supprimée.
Je te renvoie aussi le tableau avec les codes CEP/DMF pour ajout dans le tableau de tavail.
As-tu vraiment besoin que toutes les cases matches ? car comme c'est un tableau très tres lourd ben j'ai du supprimer des colonnes donc normal si quand tu vas comparer ce tabelau "20240104-Registration With Packset & PDS Manufacturing Information" avec celui envoyé à la base ben ce dernier est plus complet.
Tiens moi au courant
Merci encore
Ludivine
Bonjour Ludivine,
Je n'ai pas besoin que toutes les cases matches mais uniquement celles qui ont un "X" dans la colonne "Utilisé dans les formules". Il y a 3 colonnes qui ne matchent pas et qu'il faut que tu me complètes (la 1ère, je pense qu'il manque juste un "/" entre DMF et CEP).
Cdlt,
Cylfo
Bonjour,
Super et j'ai tout compléter.
Effectivement, le 1er c'est , le 2eme CIS
| PDS DMF/CEP Owne |
| CIS |
Pour le troisième il y a un petit souci, normalement il y est dans le tableau original mais ma chef qui a prepare le tableau l'a enlevé malheureusement.
C'est une colonne effectivement à enlever pour le tableau résultat final mais une fois fait toutes les formules/recherches...elle a un peu enticipé.
Du coup, je l'ai remis je te renvoie le tableau normalement tu as tout :-)
Merci et à bientôt
Ludivine
Bonjour Ludivine,
Tu me dis "normalement tu as tout", ah
Cdlt,
Cylfo
Re,
Désolée, c'est la colonne I du tableau "tableau officiel B1"
| Reference AMM |
| DE/H/4343/001/DC |
Cordialement,
Ludivine
Bonjour Ludivine,
J'ai adapté le fichier que je t'avais communiqué avec les nouvelles définitions des fichiers que tu as transmis mais je ne peux pas complètement le tester car les données de test des fichiers ne sont pas cohérentes.
Je te laisse tester en suivant le mode d'emploi qui figure sur le 1er onglet, me dire si cela fonctionne ou pas et me répondre sur la suite (point 5 du du mod'op).
Cdlt,
Cylfo