Faire apparaître une valeur selon une valeur de ligne ET de colonne
Bonjour à tous !
J'ai besoin de faire apparaître une valeur de cellule selon une valeur de ligne et de colonne.
J'ai un tableur contenant des centaires de lignes et pas mal de colonne, voici donc un tableur exemple pour illustrer ma question :
Actions spécifiques | Ville 1 | Ville 1 porteur | Ville 2 porteur | Ville 2 | Pêcheurs porteur | Pêcheur | Chasseurs | VTT |
1.1 | Lorem | Maitre d'œuvre | Partenaire financier | elit | sed | |||
1.2 | Ipsum | Partenaire technique | Bailleurs de fond | sit amet | ||||
1.3 | Maître d'œuvre | consecteur | Bailleur de fond | adipiscing | augue | |||
1.4 | dolor | Partenaire financier | Maître d'œuvre | Duis | a | |||
1.5 | Partenaire technique | turpis | egestas |
Et je veut remplir la Colonne Type d'engagement de ce tableur :
Actions spécifiques | Acteurs | Porteur |
1.1 | Ville 1 | Maître d'œuvre |
1.1 | Pêcheurs | Partenaire financier |
1.1 | VTT | |
1.2 | Ville 1 | Partenaire technique |
1.2 | Ville 2 | Bailleur de fond |
1.3 | Ville 2 | Maître d'œuvre |
1.3 | Pêcheurs | Bailleur de fond |
1.3 | Chasseurs | |
1.4 | Ville 1 | Partenaire financier |
1.4 | Pêcheurs | Maître d'œuvre |
1.4 | Chasseurs | |
1.5 | Ville 2 | Partenaire technique |
1.5 | VTT |
J'ai donc besoin de faire afficher la valeur dans la cellule si la ligne correspond a l'action spécifique et si la colonne contient le nom de l'acteur ET de le mot "porteur".
J'ai fouillé dans les formules, RECHERCHEH, RECHERCHEV, EQUIV, CHERCHE... mais j'avoue que je suis perdu dans les formules logiques.
Bonsoir,
Pourquoi rien en regard de VTT ou Chasseurs dans ton tableau cible ?
Ça peut arriver qu'il n'y ai pas d'informations relatives aux porteurs dans une des actions, voila pourquoi !
Compte tenu de ta dernière information, voilà ma proposition :
Sub ActionsPorteurs()
Dim aa, d As Object, asac, ac, aap() As String, i%, k%, prt As Boolean
Set d = CreateObject("Scripting.Dictionary")
aa = ActiveSheet.Range("A1").CurrentRegion
For k = 2 To UBound(aa, 2)
ac = Trim(aa(1, k))
If InStr(1, ac, "porteur") Then
prt = True
ac = Trim(Replace(ac, "porteur", ""))
End If
For i = 2 To UBound(aa, 1)
If aa(i, k) <> "" Then
asac = aa(i, 1) & "|" & ac
If d.exists(asac) Then
If prt Then d(asac) = aa(i, k)
Else
d(asac) = IIf(prt, aa(i, k), "")
End If
End If
Next i
prt = False
Next k
ReDim aap(d.Count, 2): i = 0
For Each ac In d.keys
asac = Split(ac, "|"): i = i + 1
aap(i, 0) = asac(0): aap(i, 1) = asac(1): aap(i, 2) = d(ac)
Next ac
aap(0, 0) = "Actions spécifiques"
aap(0, 1) = "Acteurs": aap(0, 2) = "Porteur"
With Worksheets("Feuil2").Range("A1")
.CurrentRegion.Clear
With .Resize(i + 1, 3)
.Value = aap
.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlYes
.Columns.AutoFit
.Borders.Weight = xlThin
.Columns(1).HorizontalAlignment = xlRight
With .Rows(1)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
End With
.Worksheet.Activate
End With
End Sub
Je joins exceptionnellement (pas mon habitude en l'absence de fichier fourni...) un fichier pour qu'il n'y ait pas de fausses interrogations sur le rapport du code à la source traitée.
Cordialement.
NB- une précision utile : indispensable que le libellé des colonnes prolongé par la mention "porteur" soit le même que celui de la colonne correspondante sans "porteur", si l'on ôte "porteur".
On avait "Pêcheurs porteur" et "Pêcheur", ce qui provoquait des anomalies.
Aussi des espaces parasites mais ça j'en ai tenu compte dans le code...
Bonjour valdg, MFerrand,
Je te propose ce fichier Excel :
À l'ouverture du fichier, tu es sur "Feuil2" : note qu'à part les entêtes, tout est vide.
Va sur "Feuil1" : tu peux voir toutes tes données ; Ctrl e ➯ travail effectué
Alt F11 pour voir le code VBA, puis revenir sur Excel
Si besoin, tu peux demander une adaptation.
Merci de me dire si ça te convient.
dhany
Merci à vous deux !!
Le code de MFerrand marche à merveille
Maintenant, j'ose utiliser votre expertise encore une fois. Si jamais j'avais besoin d'une autre colonne, qui fonctionne complètement de la même façon mais qu'au lieu du mot "porteur", un autre mot serait à rechercher, "statut" par exemple. Comment est-ce que je pourrais ajuster le code de MFerrand ?
Merciiiii !!!
S'il s'agit d'une mention additionnelle, comme pour porteur, on double la condition de la même façon avec "statut" :
If InStr(1, ac, "porteur") Then
prt = True
ac = Trim(Replace(ac, "porteur", ""))
ElseIf InStr(1, ac, "statut") Then
prt = True
ac = Trim(Replace(ac, "statut", ""))
End If
Dans ce cas que l'on ait "porteur" ou "statut", cela sera traité de la même façon.
Ce que fait le code en parcourant le tableau, c'est créer un couple Action (que tu as en 1re col.)-Acteur (que tu as en 1re ligne, pour en faire une clé d'élément de dictionnaire qui prendra comme valeur celle de la cellule intersection si Acteur avec mention additionnelle, rien si pas de mention additionnelle (en testant si l'élément est déjà créé pour le compléter si la col. avec arrive après la col. sans ou pour ne pas le mutiler dans le cas contraire).
Cordialement.
Merci ! C'est vraiment génial, et en plus j'en apprend un peu plus
Bonne journée !
Bonne continuation !
Bonjour valdg,
Tu a écrit :je ne peux pas voir pourquoi le code de dhany ne fonctionne pas...
Je suis très surpris par ta réponse, car sur mon PC, ça marche parfaitement !
J'aurais peut-être dû préciser que la macro ne s'exécute qu'à partir de "Feuil1" ?
en fin de macro, ça va automatiquement sur "Feuil2".
Si ça a pas marché sur ton PC, c'est peut-être à cause d'une différence de versions d'Excel ?
ou peut-être que pour ton fichier réel, tes données sont à un autre emplacement ?
ou peut-être que le nom de la feuille de ton tableau initial n'est pas "Feuil1" ?
Voilà pourquoi c'est mieux quand le demandeur transmet un fichier plutôt qu'une simple image !
(et ça m'a obligé à taper laborieusement toutes tes données !
Tu a écrit :au lieu du mot "porteur", un autre mot serait à rechercher, "statut" par exemple.
Dans ce cas, dans mon code VBA, il suffit de remplacer If Right$(.Value, 8) = " porteur" Then
par If Right$(.Value, 7) = " statut" Then
dhany
Mmm.. je ne comprend pas.. je vais réessayer à partir de mon ordinateur à la maison. Je suis désolée de ne pas avoir mis de fichier joint, je vais le faire la prochaine fois promis
Je te donne des nouvelles de mes test avec ton code ! Un gros merci pour ton temps !!
Je suis impatient d'connaître quels seront les futurs résultats de tes tests !
(j'suis vraiment curieux d'savoir pourquoi ça a pas marché la 1ère fois !)
dhany
Bonjour dhany, je ne sais toujours pas pourquoi le code buggais sur mon ordinateur au bureau.. il arrêtait sur la ligne : With Cells(lig, Cols(col))
vers la fin... probablement parce que je l'ai testé sur un fichier différent ? Je n'ai pas eu le choix parce que (peut-être a cause des paramètres de sécurité des orinateurs du bureau?) je n'arrivais pas à ouvrir le fichier Exo_valdg.xlsm, mais seulement la macro, à partir d'un autre tableur... mystère...
Maintenant les bonnes nouvelles
À la maison, ça a fonctionné, Yé ! J'ai testé avec le mot Statut pour voir, j'ai modifié le code comme tu m'as dit et fait quelques ajustements et ça fonctionne !
Je le teste sur mon fichier complet en arrivant au bureau dans 1 heure et j'espère bien que ça fonctionnera parce que ça réglerait mon autre problème
jte transmet mon nouveau fichier
Bonjour valdg,
Je te retourne ton fichier Excel modifié :
À l'ouverture du fichier, tu es sur la feuille "R2" : note qu'à part les entêtes, tout est vide.
Va sur la feuille "Porteurs" : clique sur le bouton "Porteur" ➯ travail effectué
Va sur la feuille "R1" : note qu'à part les entêtes, tout est vide.
Va sur la feuille "Statuts" : clique sur le bouton "Statut" ➯ travail effectué
Alt F11 pour voir le code VBA, puis revenir sur Excel
Mais oui : y'a qu'un seul Module, et tout a été fait par la même macro DoListe()
Sur mon propre PC, ça marche impeccable !
par contre, sur ton PC du bureau, j'peux rien t'garantir !
dhany
Wow génial ! Je test tout ça et le code de MFerrand à fond cet après midi avec les données réelles ! Merci énormément !!
Salut à vous deux !
Puisque ce code va servir, je l'espère, plusieurs années, et que je ne sais pas quelles seront les demandes futures, incluant dans les prochaines semaines
Je me retrouve avec ce code :
Sub ActionsStatutPorteurs()
Dim aa, d As Object, asac, ac, aap() As String, i%, k%, prt%
Set d = CreateObject("Scripting.Dictionary")
aa = ActiveSheet.Range("A1").CurrentRegion
For k = 2 To UBound(aa, 2)
ac = Trim(aa(1, k))
If InStr(1, ac, "porteur") Then
prt = 2: ac = Trim(Replace(ac, "porteur", ""))
ElseIf InStr(1, ac, "statut") Then
prt = 1: ac = Trim(Replace(ac, "statut", ""))
ElseIf InStr(1, ac, "Programme") Then
prt = 1: ac = Trim(Replace(ac, "Programme", ""))
End If
For i = 2 To UBound(aa, 1)
If aa(i, k) <> "" Then
asac = aa(i, 1) & "|" & ac
If d.exists(asac) Then
If prt > 0 Then d(asac) = _
IIf(prt = 1, aa(i, k) & d(asac), d(asac) & aa(i, k))
Else
If prt > 0 Then
d(asac) = IIf(prt = 1, aa(i, k) & ";", ";" & aa(i, k))
Else
d(asac) = ";"
End If
End If
End If
Next i
prt = 0
Next k
ReDim aap(d.Count, 4): i = 0
For Each ac In d.keys
asac = Split(ac, "|"): i = i + 1: aap(i, 0) = asac(0): aap(i, 1) = asac(1)
asac = Split(d(ac), ";"): aap(i, 2) = asac(0): aap(i, 3) = asac(1)
Next ac
aap(0, 0) = "Actions spécifiques": aap(0, 1) = "Acteurs"
aap(0, 2) = "Statut": aap(0, 3) = "Porteur": aap(0, 4) = "Programme"
With Worksheets("Resultats").Range("A1")
.CurrentRegion.Clear
With .Resize(i + 1, 4)
.Value = aap
.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlYes
.Columns.AutoFit
.Borders.Weight = xlThin
.Columns(1).HorizontalAlignment = xlRight
With .Rows(1)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
End With
.Worksheet.Activate
End With
End Sub
Actuellement ce code me permet de sortir l'information sous les colonnes programmes mais le place sous la colonne Statut..., il a quelque chose que je rate...
Mon objectif est d'être capable d'ajouter des colonnes à la demande. Alors, pour ne pas avoir à abuser de votre gentillesse chaque fois que j'aurais besoin d'une autre colonne, est-ce c'est possible pour vous de m'indiquer les changements que je dois apporter ? Ainsi, je pourrais le refaire la prochaine fois
ET pour ne pas vous faire travailler dans le vide, je vous transfert un fichier qui se rapporche plus de mon fichier réel !!
Bonjour valdg,
Je te retourne ton fichier modifié :
À l'ouverture du fichier, tu es sur la feuille "R3" : note qu'à part les entêtes, tout est vide.
Va sur la feuille "R2" : idem ; va sur la feuille "R1" : idem ; va sur "Feuil0" ; Ctrl e
T'as plus qu'à vérifier tous les résultats.
Alt F11 pour voir le code VBA (très différent du précédent, et plus facilement adaptable)
À te lire pour avoir ton avis.
dhany
Bonjour,
Le code aménagé pour prendre en compte simultanément statut et porteur peut effectivement donner lieu à extension dans les même conditions (sous réserve des règles que tu as établies concernant ces mentions statut et porteur et qui permettent de les détecter...) Mais il te faut maîtriser la façon dont le code est bâti...
L'outil Dictionnaire est utilisé, il s'agit de construire des éléments comportant une clé (qui l'identifie et n'admet pas de doublon) et une valeur.
La clé est constituée par le couple code action/acteur (qui occuperont tes 2 premières colonnes de résultat) : elle est formée par la concaténation de ces 2 valeurs avec un séparateur : "|"
La valeur affectée à l'élément, initialement ne comportait que la valeur "porteur", d'où la variable prt était booléenne, et on affectait alors la valeur de la colonne porteur ou une valeur vide ("") s'il s'agissait d'une colonne "neutre".
En ajoutant statut, on a basculé la variable en Integer, pour qu'elle prenne les valeurs 1 pour statut et 2 pour porteur (et reste à 0 si neutre), car tu as placé tes colonnes statut--porteur dans cet ordre. Il faut donner à prt une valeur correspondant au rang de la colonne en excluant les deux premières... (donc si tu en rajoutes une prt prendra les valeurs 1 ou 2 ou 3 selon l'ordre de la colonne).
Donc : on a 2 boucles imbriquées, la boucle externe pourcourt les colonnes, on prélève et traite le nom d'acteurs en première ligne, en l'épurant de la mention additionnelle, ce qui donne lieu à établissement de la valeur de prt (comme vu ci-dessus), et on a dans ac le nom d'acteur. Ça tu m'as de l'avoir assez bien perçu (mais à compléter par la valeur à donner à prt).
On parcourt les lignes de la colonne : si la cellule n'est pas vide, on constitue la clé en concaténant le code action et l'acteur comme déjà indiqué...
C'est ensuite qu'il faut bien faire attention à la manoeuvre : on teste si la clé existe. Si elle existe (c'est traité en premier dans la macro parce que c'est plus logique, mais pour l'explication il faut voir si elle n'existe pas d'abord)
Si la clé n'existe pas : elle va être créée donc, on teste prt : si prt =0 on est dans une colonne neutre, on va donc initialiser l'élément avec une valeur vide : c'était "" tant qu'on avait une seule colonne, avec 2 ce sera ";" [le point-virgule est un séparateur qu'on utilisera ultérieurement pour transformer la valeur en tableau avec Split, affecté seul, cela revient à affecter 2 valeurs vide (équivalent à " "";"" "). Si tu ajoutes une colonne, il faudra donc à ce stade ajouter 3 valeurs vide, soit : ";;"
Si prt est supérieur à 0 on est dans une colonne porteur ou statut (selon valeur prt) : la valeur qu'on donne à l'élément sera donc : pour statut (=1) "ValeurCellule;" [si 3 colonnes : "ValeurCellule;;"] et pour porteur (=2) ";ValeurCellule" [si 3 colonnes : ";ValeurCellule;"]
Et dans le cas 3e colonne (=3) ";;ValeurCellule"
Note que mon affectation utilise la fonction Iif parce que l'on n'a que deux valeurs alternatives, mais à partir de 3 il faut faire un peu autrement, et le plus simple sera sans doute un Select Case prt...
Revenons à si la clé existe : si prt = 0, c'est qu'on est dans une colonne neutre et que l'élément dico a été créé à partir d'une colonne non neutre, c'est à dire qu'il contient au moins une valeur à ne pas perdre, et les valeurs vide que l'on introduit lors de la création avec une colonne neutre y sont déjà.
Donc si prt = 0 on ne fait rien et on n'intervient que si prt > 0 : avec 2 col. selon la valeur de prt on avait alors une seule alternative : soit ValeurCellule & ValeurDico, soit ValeurDico & ValeurCellule.
A partir de 3 éléments, il faudra extraire la valeur dico existante en tableau avec Split, sur le séparateur ";" et affecter la ValeurCellule à l'élément prt-1 du tableau (les indices tableau partant de 0). Puis rétablir la chaîne avec la fonction Join [à l'inverse de Split transforme un tableau en chaîne] sur le séparateur ";"
Sur la fin de la macro, l'adaptation est plus facile : il faut ajuster le nombre de colonnes du tableau résultat, la clé comme la valeur de l'élément dico étaient déjà splittées, ça ne change, juste le nombre de colonne (et ajouter les intitulés de colonnes supplémentaires).
Pour la constitution du dico, jusqu'à 2 colonnes, je pouvais me contenter des deux variables (ac et asac), mais si l'on a à splitter la valeur de l'élément dico en cours, ces deux variables étant alors utilisées (ac conserve le nom d'acteur et asac forme la clé), il sera bon d'ajouter une nouvelle variable (non typée) pour cette manipulation supplémentaire.
Note que si tu ajoutes encore des colonnes, les adaptations deviendront plus faciles au-delà de 3 (mais il ne faudrait pas que cela devienne illimité...
Cordialement.
Wow, merci
Je vais tenter de faire un bout de chemin avec cela... Je ne sais pas si je vais parvenir à faire la partie :
extraire la valeur dico existante en tableau avec Split, sur le séparateur ";" et affecter la ValeurCellule à l'élément prt-1 du tableau (les indices tableau partant de 0). Puis rétablir la chaîne avec la fonction Join [à l'inverse de Split transforme un tableau en chaîne] sur le séparateur ";"
ni le reste d'ailleur ahah mais je vais au moin tenter quelque chose...
promis, je vais sérieusement me magasiner un cours de VBA