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 porteurVille 2 porteurVille 2 Pêcheurs porteur PêcheurChasseursVTT
1.1LoremMaitre d'œuvrePartenaire financierelitsed
1.2IpsumPartenaire techniqueBailleurs de fondsit amet
1.3Maître d'œuvreconsecteurBailleur de fondadipiscingaugue
1.4dolorPartenaire financierMaître d'œuvreDuisa
1.5Partenaire techniqueturpisegestas

Et je veut remplir la Colonne Type d'engagement de ce tableur :

Actions spécifiques ActeursPorteur
1.1Ville 1Maître d'œuvre
1.1PêcheursPartenaire financier
1.1VTT
1.2Ville 1Partenaire technique
1.2Ville 2Bailleur de fond
1.3Ville 2Maître d'œuvre
1.3PêcheursBailleur de fond
1.3Chasseurs
1.4Ville 1Partenaire financier
1.4PêcheursMaître d'œuvre
1.4Chasseurs
1.5Ville 2Partenaire technique
1.5VTT

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...

16valdg-tablotft.xlsm (25.21 Ko)

Bonjour valdg, MFerrand,

Je te propose ce fichier Excel :

9exo-valdg.xlsm (19.08 Ko)

À 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 . Mes connaissance (presques nulles) en VBA font que je ne peux pas voir pourquoi le code de dhany ne fonctionne pas... il doit s'agir d'un détails que je n'arrive pas à repérer .

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 ! tu aurais dû avoir ceci en "Feuil2" :

screen

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 que j'ai posté hier soir sur le forum.

jte transmet mon nouveau fichier

2exo-valdgv2.xlsm (30.08 Ko)

Bonjour valdg,

Je te retourne ton fichier Excel modifié :

13exo-valdgv2.xlsm (25.60 Ko)

À 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 ! sur le PC qui est chez toi, ça devrait aller aussi !

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 , j'ai tenté de voir comment ajuster le code pour qu'il affiche une quatrième colonne, avec, par exemple, le mot programme. Je suis partie du code de MFerrand parce que j'arrive un peu plus à m'y retrouver (ne le prennez pas mal ce n'est pas la clarté du code le problème mais mes connaissances en VBA )

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é :

8exo-valdgv3.xlsm (41.91 Ko)

À 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 !

Rechercher des sujets similaires à "apparaitre valeur ligne colonne"