XL 2013 Extraire donnée en fonction d'un mot

[Xorys]

Bonjour,

Cela fait quelques jours que je bloque sur une fonction Excel.

Ce que je souhaiterais faire :
J'ai dans une feuille "Data", une colonne "Etat" avec des informations.
Dans une feuille "Config", je souhaite récupérer toutes les lignes de la colonnes "Etat" de la feuille "Data" dont le mot "OFF" apparaît sans avoir de doublon.

J'utilise cette commande : =INDEX(Tableau1[Etat];PETITE.VALEUR(SI(FREQUENCE(SI(Tableau1[Etat]<>"";EQUIV(Tableau1[Etat];Tableau1[Etat];0)); LIGNE(Tableau1[Etat])-LIGNE('Data'!D2)+1);LIGNE(Tableau1[Etat])-LIGNE('Data'!D2)+1);LIGNES(A$1:A10)))

Çà me récupère effectivement toutes les informations dans la feuille "Data" colonne "Etat" sans les doublons mais ça me récupère également les états qui ne contiennent pas le mot "OFF".

J'ai une préférence pour une commande qui fonctionne de façon dynamique car je suis amené à régulièrement effacer tous les états pour y mettre d'autres extractions. Si je ne dis pas de bétise, en VBA il faudrait que je lance la macro à chaque fois que j'efface ma colonne "Etat".

Merci d'avance.

 

[Xorys]

Bonjour,

Je me permet de relancer le sujet, est-ce qu'il vous faut des informations supplémentaires ?

 

[job75]

Bonjour Xorys,

Je me permet de relancer le sujet, est-ce qu'il vous faut des informations supplémentaires ?

Oui, un fichier, pas sorcier de l'anonymiser avec quelques lignes.

A+

 

[Xorys]

Je viens d'ajouter un fichier pour exprimer mon besoin.
J'ai très largement simplifié ce fichier, d'origine j'ai beaucoup plus de colonne dans l'onglet "Data" et donc plus de filtrer sur les tableaux croisés dynamique.

Merci.

 

[job75]

Re,

Pas besoin de VBA, formule matricielle en A2 de la feuille "Config" :

=SIERREUR(INDEX(Data!A:A;PETITE.VALEUR(SI((GAUCHE(Tableau1[Etat];3)="OFF")*(LIGNE(Tableau1[Etat])=1+EQUIV(Tableau1[Etat]&Tableau1[Assigné];Tableau1[Etat]&Tableau1[Assigné];0));LIGNE(Tableau1[Etat]));LIGNES(A$2:A2)));"")

Fichier joint.

A+

 

[Xorys]

Merci, dans le fichier ça fonctionne correctement.

J'ai voulu faire quelques ajustement notamment en retirant le Tableau1[Assigné] pour lequel je n'ai pas besoin de le filtrer et également mettre la commande en A1. Avec les modification ça me donne cette nouvelle commande et je ne comprends pas pourquoi je ne récupère aucune donnée :

=SIERREUR(INDEX(Data!A:A;PETITE.VALEUR(SI((GAUCHE(Tableau1[Etat];3)="OFF")*(LIGNE(Tableau1[Etat])=1+EQUIV(Tableau1[Etat];Tableau1[Etat];0));LIGNE(Tableau1[Etat]));LIGNES(A$1:A1)));"")

Est-il possible également possible d''ajouter une fonction à la commande afin d'éviter les doublons ? Une fois qu'il me sort "OFF Installation", j'ai pas besoin qu'il me l'extrait une seconde fois.

 

[Xorys]

Corrige moi si j'ai faux mais j'ai remis le paramètre Tableau1[Assigné] en changeant Assigné par Etat et je retrouve bien mes données :

=SIERREUR(INDEX('Data'!A:A;PETITE.VALEUR(SI((GAUCHE(Tableau1[Etat];3)="OFF")*(LIGNE(Tableau1[Etat])=1+EQUIV(Tableau1[Etat]&Tableau1[Etat];Tableau1[Etat]&Tableau1[Etat];0));LIGNE(Tableau1[Etat]));LIGNES(A$1:A1)));"")

 

[job75]

Re,

Pour moi il y a doublon quand il y a 2 paires Etat + Assigné identiques, c'est pour cela que je les concatène.

Cela paraît logique, d'où l'affichage de OFF Désinstallation + Céline et de OFF Installation + Céline.

A+

 

[Xorys]

C'est possible d'ajouter à cette commande le choix d'un second mot ? Par exemple si en plus de "OFF" je souhaite également filtrer le mot "Traiter" ?

=SIERREUR(INDEX(Data!D:D;PETITE.VALEUR(SI((GAUCHE(Tableau1[Description];3)="OFF")*(LIGNE(Tableau1[Description])=1+EQUIV(Tableau1[Description]&Tableau1[Description];Tableau1[Description]&Tableau1[Description];0));LIGNE(Tableau1[Description]));LIGNES(A$2:A2)));"")

 

[job75]

Bonjour Xorys,

En matricielle le ET est remplacé par le signe "*" et le OU par le sugne "+" :

=SIERREUR(INDEX(Data!A:A;PETITE.VALEUR(SI(((GAUCHE(Tableau1[Etat];3)="OFF")+(GAUCHE(Tableau1[Etat];7)="Traiter"))*(LIGNE(Tableau1[Etat])=1+EQUIV(Tableau1[Etat]&Tableau1[Assigné];Tableau1[Etat]&Tableau1[Assigné];0));LIGNE(Tableau1[Etat]));LIGNES(A$2:A2)));"")

Fichier (2).

Sur un autre fil on utilisait le VBA, qu'en est-il ?

A+