Recherche de toutes les occurrences et récupération d'une valeur sur la ligne

[yoop]

Bonjour tout le monde,
j'ai fait pas mal de recherche, et pas mal d'essais et au final je n'arrive jamais à mon résultat, alors je me permets de poster mon problème par ici.

J'ai une feuille qui contient 3 colonnes, chaque cellule peut contenir de 1 à n références et un identifiant de ligne unique.
Sur une autre feuille j'ai une liste des références, et j'aimerais indiquer à côté de chaque référence tous les identifiants l'utilisant.

Exemple:
Feuille1 :

ColA ColB​

Ligne1 ID1 Ref1

Ref2​

Ligne2 ID2 Ref1
Ligne3 ID3 Ref2

Ref3​

Lign4 ID4 Ref1

Feuille2 :

ColA ColB​

Ligne1 Ref1 ID1;ID2;ID4 <== Cette liste en colonne ColB est ce que j'aimerais obtenir
Ligne2 Ref2 ID1;ID3
Ligne3 Ref3 ID3

Je vous remercie par avance pour votre aide,
Yoop

 

[CISCO]

Bonjour

Un tout petit fichier en pièce jointe serait certainement plus efficace.

@ plus

 

[yoop]

Oui c'est vrai, je viens d'en faire un petit, la Colonne B de la Feuil2 est ce que je cherche à obtenir.

 

[Dugenou]

Bonjour,
Une possibilité avec formule matricielle et colonnes supplémentaires. Seulement si tu n'as pas trop de références par identifiant.

Cordialement

 

[yoop]

Je te remercie, ça à l'air pas mal le résultat, mais compliqué !
J'avais en effet essayé avec ces fonctions, mais n'ayant pas d'expérience quant à leur utilisation j'étais un peu paumé.
Je regarde en détail et je vais tenté de l'adapté à mon fichier final, je reviens vers toi tout à l'heure.
Merci

 

[Dugenou]

=SIERREUR(INDEX(Feuil1!$A$1:$A$11;PETITE.VALEUR(SI(NON(ESTERREUR(TROUVE($A2;Feuil1!$B$2:$B$11)));LIGNE(Feuil1!$B$2:$B$11));NBVAL($F$1:F$1)));"")&SI(NBCAR(G2)>1;";";"")

Valdé en matriciel (ctrl+maj+enter, puis recopié à droite.
Un peu compliqué à cause du non(esterreur()) qui permet de ne pas avoir de résultat #valeur mais seulement des N° de ligne ou FAUX avec le Si(non(esterreur(trouve...));ligne())
Ensuite petite.valeur pour avoir les N° de ligne dans l'ordre
Puis index(Feuil1!$A$1:$A$11) pour avoir la valeur (toujours commencer en ligne 1
le sierreur pour ne rien afficher quand on arrive aux résultats FAUX
et la cerise sur le gateau : gestion des ; selon qu'il y a quelque chose dans la cellule suivante ou pas.
N'hésites pas si besoin de plus d'explications

 

[yoop]

J'ai essayé de décomposer ta formule pour mieux comprendre, mais la plupart des fonctions me retournent une erreur quand je l'utilise seule ou avec une constante... Exemple : NBVAL(...) si je remplace par 2, ça me marque formule incohérente, ou encore si j'utilise TROUVE tout seul j'ai une erreur de formule et l'affichage est #VALEUR!
Alors du coup je veux bien que tu m'expliques un peu plus en détails
{=SIERREUR(INDEX(Feuil1!$A$1:$A$11;PETITE.VALEUR(SI(NON(ESTERREUR(TROUVE($A2;Feuil1!$B$2:$B$11)));LIGNE(Feuil1!$B$2:$B$11));NBVAL($F$1:F$1)));"")&SI(NBCAR(G2)>1;";";"") }

Pourquoi est-ce qu'il y a des accolades de part et d'autre de la formule?
Et sinon, peux-tu me décomposer rapidement la formule avec une toute petite explication, histoire que je puisse apprendre et réutiliser la prochaine fois ces fonctions !
Merci beaucoup

 

[CISCO]

Bonsoir

Une autre façon de faire en pièce jointe, avec un tableau intermédiaire.
@ plus

 

[Dugenou]

Bonjour,
Yoop, je te conseille d'utiliser la proposition de Cisco : plus simple et non matricielle.
Cordialement

 

[CISCO]

Bonjour à tous, bonjour Dugenou

J'ai essayé de ...
{=SIERREUR(INDEX(Feuil1!$A$1:$A$11;PETITE.VALEUR(SI(NON(ESTERREUR(TROUVE($A2;Feuil1!$B$2:$B$11)));LIGNE(Feuil1!$B$2:$B$11));NBVAL($F$1:F$1)));"")&SI(NBCAR(G2)>1;";";"") }

Pourquoi est-ce qu'il y a des accolades de part et d'autre de la formule?

Merci beaucoup

La formule proposée par Dugenou étant matricielle, elle doit être validée avec les trois touches Ctrl+maj+entrer, ce qui fait apparaitre les accolades avant et après.

Si tu veux quelques explications sur les formules matricielles, regarde là par exemple.

@ plus

 

[CISCO]

Bonjour

Bonjour,
Yoop, je te conseille d'utiliser la proposition de Cisco : plus simple et non matricielle.
Cordialement

Oui, mais toujours pas très pratique parce que la concaténation ne fonctionne pas sur une plage (On ne peut pas faire avec CONCATENER(A3: D3), mais il faut faire avec CONCATENER(A3;B3;C3;D3) ou avec A3&B3&C3&D3). S'il y a beaucoup de termes à rajouter bout à bout, c'est lourd.

@ plus

 

[yoop]

Bonjour tout le monde,
en effet la solution de Cisco est plus simple à appréhender ! L'inconvénient est que si on a beaucoup d'ID ça va faire un tableau intermédiaire bien énorme. Dans mon cas j'ai environ 250 ID (pour le moment)...
J'ai lu le lien pour les formules matricielles, et j'ai réussi à faire quelques applications en testant.
Sinon il faudrait que je passe par une macro, mais là je suis moins fan je dois l'avouer.

 

[CISCO]

Rebonjour à tous, bonjour Dugenou

Une autre solution en pièce jointe permettant de travailler avec un tableau intermédiaire moins grand ; toutes les ID sont listées sur la ligne sans cellule vide intercalée entre deux ID. La formule est matricielle, donc à valider avec les trois touches Ctrl+maj+entrer.

Deux restrictions :
* Si la colonne Feuil2!R contient une ID (ou plus), c'est peut être qu'il faut étendre davantage les formules vers la droite (le tableau intermédiaire est trop petit), et modifier la formule dans la colonne E. Au besoin, on peut mettre un test ou une MFC pour contrôler cela automatiquement par rapport au nombre max de point-virgule dans la colonne B de la feuille 1.
* Il ne faut pas qu'une ID puisse en contenir une autre. Exemple : Si on a une ID = A2254 et une autre dénommée A22, je crois qu'il y aura parfois des problèmes.

@ plus