Résolu Aide recherche chaine de caractères dans matrice et renvoi No. ligne

Chincha

XLDnaute Nouveau
Bonjour,

J'essaie sans succès depuis hier à écrire une fonction à priori toute simple, mais je bute sur les limitations des fonctions Excel non-matricielles.

Il s'agit de rechercher une chaîne de caractères dans une matrice et renvoyer son numéro de ligne.
Pour simplifier, j'ai 2 feuilles de valeurs.
Une feuille 1 contenant 4 colonnes de valeurs :
  • A: Code interne,
  • B: Reférence Fournisseur 1,
  • C: Reférence Fournisseur 2,
  • D: Reférence Fournisseur 3.
Une feuille 2 contenant 1 colonnes de valeurs, et 2 colonnes de contrôle/recherche :
  • A: Référence Fournisseur
  • B: Match = vérifie si la Référence en colonne A est présente dans une des colonnes BCD Feuil1 (OUI/NON).
  • C: Code = renvoie le Code interne correspondant de la colonne A feuille 1 si la Ref. est trouvée dans une des colonnes B, C ou D en feuille 1.

Je joins un exemple en PJ pour mieux visualiser.
Merci d'avance pour m'aider à avancer un peu !
 

Fichiers joints

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour Chincha,
On peut faire une recherche multiple des Ref sur les trois colonnes.
Un essai pour voir en Feuil2 C2:
VB:
=SIERREUR(INDEX(Feuil1!A:A;SIERREUR(EQUIV(A2;Feuil1!B:B;0);SIERREUR(EQUIV(A2;Feuil1!C:C;0);SIERREUR(EQUIV(A2;Feuil1!D:D;0);""))));"Non")
La colonne Match ne sert à rien, si le code n'est trouvé nulle part le Sierreur renvoie Non.
 

Pounet95

XLDnaute Occasionnel
Bonjour Sylvanu, Chincha
Je ne sais pas si tu ouvres les classeurs en PJ ( moi je le fais au risque de ....) mais , volontairement ou pas, il y a en feuil1 plusieurs références pour une même recherche de la feuil 2.
Chincha, quelle est la bonne ?
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour le fil,
Pounet, j'ouvre les PJ. effectivement dans ce fichier il y a des erreurs :
1.jpg
Ensuite à chacun de prendre le risque ou non de continuer.
 

Staple1600

XLDnaute Barbatruc
Bonjour le fil, Pounet95, Chincha, sylvanu

J'ai compris la même chose que sylvanu.
Mais ma formule étant plus alambiquée, je préfère la sienne
Et laisse donc la mienne dans sa cellule ;)
 

Pounet95

XLDnaute Occasionnel
Binjour Staple1600
Moi j'étais parti dans un "monde" que j'ai peu pratiqué du temps où je travaillais, à savoir les INDEX, EQUIV, INDIRECT, etc....
Sinon je parlais de ça, ci-dessous
Capture.PNG
 

Chincha

XLDnaute Nouveau
Bonjour Chincha,
On peut faire une recherche multiple des Ref sur les trois colonnes.
Un essai pour voir en Feuil2 C2:
VB:
=SIERREUR(INDEX(Feuil1!A:A;SIERREUR(EQUIV(A2;Feuil1!B:B;0);SIERREUR(EQUIV(A2;Feuil1!C:C;0);SIERREUR(EQUIV(A2;Feuil1!D:D;0);""))));"Non")
La colonne Match ne sert à rien, si le code n'est trouvé nulle part le Sierreur renvoie Non.
Merci Sylvanu, en fait le problème est beaucoup plus complexe car il s'agit de nombreux fichiers à traiter avec des centaines/milliers de lignes. J'ai volontairement réduit un des fichiers au minimum en supprimant toutes les colonnes/lignes inutiles, d'ou peut-être des erreurs résiduelles du fichier source.
La recherche fonctionne effectivement dans cet exemple et je te remercie pour la piste, mais pas dans tous les cas rencontrés sur mes fichiers ou il faut adapter la fonction au cas par cas, c'est pourquoi j'ai besoin vraiment d'une recherche matricielle et non pas multiple colonne par colonne.
La colonne Match était juste pour avoir un contrôle séparé, non essentiel effectivement, car je ne voulais pas que des OUI/NON apparaissent dans cette colonne.
 

Chincha

XLDnaute Nouveau
Bonjour Sylvanu, Chincha
Je ne sais pas si tu ouvres les classeurs en PJ ( moi je le fais au risque de ....) mais , volontairement ou pas, il y a en feuil1 plusieurs références pour une même recherche de la feuil 2.
Chincha, quelle est la bonne ?
Oui c'est normal, comme expliqué, j'ai omis toutes les infos inutiles pour ne pas compliquer la demande d'aide. Un article peut avoir plusieurs fournisseurs, je veux juste remonter le Code correspondant de la feuille 1 Colonne A s'il y a présence de la référence cherchée.
 

Chincha

XLDnaute Nouveau
Re,
me faisait penser que vous recherchiez une formule non matricielle. ;)
Ne serait il pas plus simple de passer par une fonction personnelle ?
Non justement, j'ai essayé de bricoler avec les EQUIV, RECHERCHEV etc., mais il faudrait idéalement une fonction VBA oui.
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
Une PJ avec une fonction perso :
VB:
Option Base 1
Function RechercheCode(NRef, ListeRef As Range, Matrice As Range)
On Error GoTo Fin
Dim Lig As Integer, Col As Integer, IndexLig As Byte
Lig = Cells(Matrice.Rows.Count, 1).End(xlUp).Row
Col = Matrice.Columns.Count
IndexLig = 0
For i = 1 To Col
    Set Plage = Range(Matrice.Cells(1, i), Matrice.Cells(Lig, i))
    If Not IsError(Application.Match(NRef, Plage, 0)) Then
        IndexLig = Application.Match(NRef, Plage, 0)
    End If
Next i
If IndexLig = 0 Then
    RechercheCode = "Non"
Else
    RechercheCode = ListeRef.Cells(IndexLig, 1)
End If
Fin:
End Function
Syntaxe :
Code:
=RechercheCode(Ref à checher;Colonne des Code;Matrice de recherche)

ex :
=RechercheCode(A2;Feuil1!A:A;Feuil1!B:D)
 

Fichiers joints

Chincha

XLDnaute Nouveau
Merci beaucoup Sylvanu, elle fonctionne en effet avec l'exemple mais pas avec le fichier "réel", j'ai que des "Non" renvoyés. J'essaye de débugger pour comprendre quel est le souci.
 

Chincha

XLDnaute Nouveau
Il semble que ce soit les titres des colonnes (la première ligne) qui posent problème ?

La fonction est:
  • OK lorsque je sélectionne un Range de cellules pour ListRef (Colonne des Codes) et Matrice (matrice de recherche) : voir capture 1, le code "03.07.01.01" renvoyé est correct.
2.jpg
  • Pas OK lorsque je sélectionne les colonnes entières pour ListRef (Colonne des Codes) et Matrice (matrice de recherche) : voir capture 2, la fonction renvoie "NON" alors que l'on voit bien la valeur 576147 est présente en 2e position dans le 3e argument Matrice.
1.jpg
J'ai éliminé tout ce qui pouvait parasiter en rangée 1 (filtrage, commentaires, cellules fusionnées, formats de cellules etc.), mais rien n'y fait.
Il faut préciser aussi que les donnés en colonne M:M sont le résultat d'une autre fonction matricielle, et non pas des chaines de caractères "bruts".
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Exact. Je l'ai reproduit.
Comme le besoin était une recherche sur plusieurs colonnes, je n'ai pas testé sur une seule.:(
Je vais regarder comme solutionner.
dans votre exemple, remplacez Arbolog!M:M par Arbolog!M:O, ça devrait marcher.


Ci dessus la réponse Non est correcte car la valeur n'est pas dans la première colonne. Si la valeur est présente, ça marche correctement. Voir PJ.
J'ai testé en pas à pas, je ne vois pas ce qui pourrait ne pas marcher
 

Fichiers joints

Dernière édition:

Chincha

XLDnaute Nouveau
Exact. Je l'ai reproduit.
Comme le besoin était une recherche sur plusieurs colonnes, je n'ai pas testé sur une seule.:(
Je vais regarder comme solutionner.
dans votre exemple, remplacez Arbolog!M:M par Arbolog!M:O, ça devrait marcher.


Ci dessus la réponse Non est correcte car la valeur n'est pas dans la première colonne. Si la valeur est présente, ça marche correctement. Voir PJ.
J'ai testé en pas à pas, je ne vois pas ce qui pourrait ne pas marcher
Non, le problème est toujours identique ;). Dans les 2 cas cela devrait renvoyer le code 03.07.01.01.
Le but est de contrôler si la valeur 576147 est présente dans une des colonnes de la feuille 1, si oui renvoyer le Code correspondant 03.07.01.01.
Je ne vois qu'une différence entre les 2 captures, j'ai dans le premier cas la valeur "576147" en argument et la valeur \"576147" (antislash devant) dans le 2e cas ce qui semble être un problème de format cellule ou Variable (Texte vs. Nombre ou Standard) ???
 

Chincha

XLDnaute Nouveau
Merci beaucoup Sylvanu, elle fonctionne en effet dans l'exemple, mais pas complètement dans le fichier réel. Il s'agit bien d'un problème de format cellule, la fonction ne trouve les références qui ont un format numérique (ce qui est le cas pour la valeur 576147), mais fonctionne pour les valeurs alphanumériques.
Cela fonctionne aléatoirement lorsque je force le format de toutes les cellules au format Texte au lieu de standard, à voir si c'est un souci de rafraichissement ou calcul auto des fonctions.
Je vais résoudre le problème, merci beaucoup pour l'aide en tout cas, c'est juste un détail à régler :):):)
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Dans la V5, je transforme le nombre en chaîne, peut être que cela peut aider.
VB:
Application.Match(CStr("*" & NRef & "*"), Plage, 0)
Ce n'est vrai que si c'est la valeur cherchée mais pas dans la matrice de recherche.
 

Fichiers joints

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas