Microsoft 365 Rechercher un texte lorsque la réponse est dans une cellule avec énumération. Voir fichier

[annieginette]

Bonjour à vous les experts.

Dans le tableau joint, j'ai une liste de contrats et dans une colonne j'ai les villes qui se rattache à ce contrat, or, quand le contrat comprend plusieurs villes, elles sont énumérées dans la même cellule et elles sont séparées par des virgules.

Or, j'aimerais avoir une fourmule, comme RECHERCHEV mais qu'il me sorte le no. de contrat de cette ville, même si elle n'est pas la 1ere de l'énumération.

Je vous joint un fichier, ça va être plus facile de comprendre.
Merci encore une fois à l'avance, car je sais que vous y porterez attention comme toujours.

 

[sylvanu]

Bonjour annieginette,
"Nicolet" apparaissant en D3 et D4, quel N° de contrat doit on remonter ?
Ou doit on remonter les deux ? ( 123, 456 )

 

[annieginette]

Bonjour annieginette,
"Nicolet" apparaissant en D3 et D4, quel N° de contrat doit on remonter ?
Ou doit on remonter les deux ? ( 123, 456 )

Bonjour Sylvanu,
En faite, Nicolet a 2 contrats, mais pas de la même catégorie. Chaque Ville a 1 contrat (ou pas) par catégorie.

 

[sylvanu]

Re,

PS je n'ai pas peur des codes VBA, j'en utilise souvent, alors si la solution doit passer par là, pas de soucis.

Alors profitons en !
Just for the fun, un essai en PJ avec deux fonctions perso :
En G2 : =ListeContrat(F3)
En B13 : =contrat(Tableau13[[#Cette ligne];[VILLE]];Tableau13[[#En-têtes];[DÉNEIGEMENT]])

 

[sylvanu]

Oups ! Percussion
Oui mais en F3 il n'y a pas de catégorie de répertoriée !

1) Pourquoi avec 'Nicolet' en E3, il ne me sort pas 456 en G3 ?

Et comment saurait il qu'il faut remonter 456 et non 123, 456 ? ( ce que fait ma fonction )

 

[annieginette]

Re,

Alors profitons en !
Just for the fun, un essai en PJ avec deux fonctions perso :
En G2 : =ListeContrat(F3)
En B13 : =contrat(Tableau13[[#Cette ligne];[VILLE]];Tableau13[[#En-têtes];[DÉNEIGEMENT]])

Yes c'est génial, excactement ce que je veux... par contre tu comprends que ce tableau exemple n'est pas le vrai, si je veux reproduire cette solution dans mon vrai tableau, comment je fais pour créer la fonction ListeContrat ?

 

[annieginette]

Yes c'est génial, excactement ce que je veux... par contre tu comprends que ce tableau exemple n'est pas le vrai, si je veux reproduire cette solution dans mon vrai tableau, comment je fais pour créer la fonction ListeContrat ?

Haaaa j'ai trouvé dans le VBA. Super, je vais tester ça sur mon fichier au bureau lundi.

Merci encore

 

[annieginette]

Oups ! Percussion
Oui mais en F3 il n'y a pas de catégorie de répertoriée !

Et comment saurait il qu'il faut remonter 456 et non 123, 456 ? ( ce que fait ma fonction )

Tu as raison, je testais au début avec une simple rechercheV et je voyais que ça ne fonctionnait pas. Mais l'important est vraiment le tableau du bas pas le F3.

 

[sylvanu]

Donc j'en déduis que vous ne connaissiez pas les fonctions perso ?
C'est dommage, c'est bien utile. Dans ce cas un petit tuto : Lien

 

[annieginette]

Donc j'en déduis que vous ne connaissiez pas les fonctions perso ?
C'est dommage, c'est bien utile. Dans ce cas un petit tuto : Lien

Yes merci je vais étudier ça

 

[Cousinhub]

Bonjour,
Bonjour Sylvanu
Une autre solution, n'utilisant pas VBA, mais Power Query, en natif dans ta version d'Excel
Le code :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    ModType = Table.TransformColumnTypes(Source,{{"CONTRATS", type text}}),
    Fract = Table.ExpandListColumn(Table.TransformColumns(ModType, {{"VILLE", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "VILLE"),
    GroupBy = Table.Group(Fract, {"VILLE"}, {{"RESP.", each Text.Combine([#"RESP."],",")}, {"Tbl", each _, type table [#"RESP."=text, CATÉGORIE=text, CONTRATS=nullable text, VILLE=nullable text]}}),
    Expand = Table.ExpandTableColumn(GroupBy, "Tbl", {"CATÉGORIE", "CONTRATS"}, {"CATÉGORIE", "CONTRATS"}),
    Pivot = Table.Pivot(Expand, List.Distinct(Expand[CATÉGORIE]), "CATÉGORIE", "CONTRATS")
in
    Pivot

Dans le fichier joint, un clic droit dans le Tableau final, "Actualiser", ou en cliquant sur le bouton "Actualiser tout" du ruban "Données" pour mettre à jour
Bonne fin d'apm, et bon W-E
Edit, code et fichier modifiés, petite erreur de ma part....

 

[DJunqueira]

Bonjour tous

Une solution simple avec des formules.

 

[annieginette]

Yes c'est génial, excactement ce que je veux... par contre tu comprends que ce tableau exemple n'est pas le vrai, si je veux reproduire cette solution dans mon vrai tableau, comment je fais pour créer la fonction ListeContrat ?

Merci j'ai réussi (tant bien que mal) à adapter la formule dans mon vrai tableau, ça fonctionne. c'est génial. Mais petite question, comment faire pour actualiser, rafraichir automatiquement? je m'explique, je dois cliquer au bout de la formule et faire 'enter' pour que le no. de la commande apparaisse, j'ai plus de 575 lignes et 50 colonnes alors pas évident. Merci encore

 

[annieginette]

Bonjour tous

Une solution simple avec des formules.

Merci je vais tester dans mon vrai tableau lundi je te reviens

 

[annieginette]

Bonjour,
Bonjour Sylvanu
Une autre solution, n'utilisant pas VBA, mais Power Query, en natif dans ta version d'Excel
Le code :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    ModType = Table.TransformColumnTypes(Source,{{"CONTRATS", type text}}),
    Fract = Table.ExpandListColumn(Table.TransformColumns(ModType, {{"VILLE", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "VILLE"),
    GroupBy = Table.Group(Fract, {"VILLE"}, {{"RESP.", each Text.Combine([#"RESP."],",")}, {"Tbl", each _, type table [#"RESP."=text, CATÉGORIE=text, CONTRATS=nullable text, VILLE=nullable text]}}),
    Expand = Table.ExpandTableColumn(GroupBy, "Tbl", {"CATÉGORIE", "CONTRATS"}, {"CATÉGORIE", "CONTRATS"}),
    Pivot = Table.Pivot(Expand, List.Distinct(Expand[CATÉGORIE]), "CATÉGORIE", "CONTRATS")
in
    Pivot

Dans le fichier joint, un clic droit dans le Tableau final, "Actualiser", ou en cliquant sur le bouton "Actualiser tout" du ruban "Données" pour mettre à jour
Bonne fin d'apm, et bon W-E
Edit, code et fichier modifiés, petite erreur de ma part....

C'est super ce que peut offrir les tableaux PQ, faudrait vraiment que je m'y mette ! Dans ton tableau, mon enjeu est quand il y 2 contrats pour la même ville, ça me créé 2 lignes, exemple J'ai ajouter FRANCIS contrat 333 de ménage pour la ville CAP, dans le tableau on voit ceci
VILLE RESP. DÉNEIGEMENT MÉNAGE
CAP FRANCIS 123
CAP FRANCIS 333

Or, j'aimerais qu'une ligne
CAP FRANCIS 123 333

Est-ce possible?