Bonjour,

Alors voilà, j'aurais besoin d'aide concernant un tableau que j'ai a effectué,

J'ai 3 colonnes, 

1 colonne intitulé Guichet 1(Colonne A) avec ces valeurs (Colonne B),

Ainsi, que une colonne Guichet 2 (colonne D), 

La colonne 1 fait environ 1800 lignes, la D environ 40, 

J'aimerai établir une correspondance, entre la colonne A et D et si il trouve la même valeur en fonction de toutes lignes, il y intègre en colonne E la valeur,

Exemple : Si en colonne A10, il y'a 2011, et qu'en colonne D20 il y'a 2011 aussi, alors la valeur B10 va se mettre en E20,

et si c'était possible de supprimer les valeurs qui ne sont pas égales aux valeurs de la colonne D ça serait excellent,

Merci d'avance les experts !

Je vous mets en pièce jointe le fichier

Bonsoir tokren, et bienvenue sur le forum.

Plusieurs valeurs peuvent correspondre à un numéro de guichet, par exemple pour le "guichet 1" numéro 2011 nous avons 10 valeurs différentes en colonne B, du coup laquelle de ces valeurs faut-il afficher en colonne E ?

Très bonne soirée,

mth

Bonsoir, et merci de ta réponse :) ,

il faut afficher les 10 valeurs, 

Après pour la manière l'idéal serait :

Dans la cellule E5 pour le premier résultat trouver, F5 pour le 2ème, G5,H6 etc.. sur la même ligne en faite ,

Après si cela est trop difficile, simplement regrouper toutes les valeurs au sein de la même cellule séparé par un espace, en e5 par exemple serait suffisant 

Bonsoir tokren, bonsoir le forum,

Voici une approche à l'aide de formules en espérant qu'elle pourra t'aider.

Dans cellule E3 cette formule à copier vers la droite puis vers le bas:

=SI(COLONNES($E$1:E$1)<=NB.SI(PlageGuichet1;$D3);DECALER($A$2;EQUIV($D3;PlageGuichet1;0)+COLONNE(A$1)-1;1);"")

L'idée de cette formule est d'aller chercher dans la colonne B la première valeur puis la deuxième etc, en n'affichant que le nombre exact de valeurs correspondant au numéro de guichet, ce nombre pouvant varier.

Pour une meilleure lisibilité de la formule j'ai nommé la zone Feuil2!$A$3:$A$729 "PlageGuichet1"

(Voir ici pour nommer une plage de cellule)

Pour aller chercher la première valeur correspondant au numéro de guichet, puis la seconde etc., j'ai profité du fait que la première colonne soit triée, une fois que la première valeur est trouvée il suffit de se décaler de 1 ligne pour avoir la valeur suivante.

c'est ce que fait cette partie de la formule:

DECALER($A$2;EQUIV($D3;PlageGuichet1;0)+COLONNE(A$1)-1;1)

La fonction DECALER() permet, à partir d'une cellule de départ (ici la cellule A2), de se décaler verticalement et horizontalement pour atteindre une autre plage de cellules . (voir aide sur cette fonction ici)

Pour me décaler verticalement j'utilise les fonctions EQUIV() et COLONNE(), pour me décaler horizontalement je saisis 1, je ne me décale que d'une colonne vers la droite pour lire la valeur de la colonne B

La fonction EQUIV() permet de trouver la position d'un élément dans une plage (voir l'aide Excel ici qui est bien faite pour plus de détail)

En E3, je cherche la position de la valeur de D3 (soit 2004) au sein de la plage PlageGuichet1

2004 n'existant pas dans PlageGuichet1 la fonction renvoie une erreur. Mais pour la ligne suivante, 2112 existe, la fonction EQUIV() renvoie 110. 2112 est effectivement la à 110ème position de la plage.

La fonction COLONNE() renvoie le numéro de colonne. J'utilise cette fonction pour avoir la première valeur trouvée quand je suis en colonne E (COLONNE(A1)=1). Lorsque je copie la formule vers la droite, COLONNE(A1) devient COLONNE(B1) qui est donc égal à 2, ceci me permet de me décaler d'une ligne supplémentaire, etc.

Ainsi, si la fonction EQUIV() me renvoie 110 comme ci-dessus pour le guichet 2112, si j'ajoute 1 je me trouverai donc en position 113, position 114 en ajoutant 2 et ainsi de suite.

Une fois cette formule écrite, il ne faut pas indéfiniment recopier la formule sur la droite sans quoi j'afficherais des valeurs ne correspondant plus au on numéro de guichet (à force de me décaler d'une ligne à chaque fois). C'est pourquoi dans la première partie de la formule je calcule le nombre d'occurrence de mon numéro de guichet. S'il apparait 3 fois j'affiche 3 valeurs et pas plus.

NB.SI(PlageGuichet1;$D3) va compter le nombre d'occurrence de la valeur située cellule D3 au sein de la plage PlageGuichet1

Pour la première ligne, le résultat est zéro, pour la ligne suivante le résultat est 3.

Ceci signifie que je ne dois rien afficher sur la première ligne, que je dois afficher 3 valeurs sur la seconde ligne car le code guichet 2112 apparaît 3 fois, et que dès la 4ème colonne je ne dois plus rien afficher.

C'est pourquoi j'utilise la fonction COLONNES(). En Colonne E la formule COLONNES($E$1:E$1) renvoie 1 (il n'y a qu'une colonne)

En copiant la formule vers la droite, elle devient COLONNES($E$1:F$1), qui renvoie donc 2 car il y a 2 colonnes. (Le petit signe $ permet de figer la colonne E de départ, voir ici pour plus d'explications).

Ainsi arrivé en colonne H, j'ai COLONNES($E$1:H$1) qui renvoie 4, ce nombre est supérieur au nombre d'occurrence de mon numéro de guichet, donc je n'affiche rien.

Pour l'autre partie de la question, il faudrait une macro, mais une formule peut tout de même aider à repérer les numéros absents, par exemple en C3 et à copier vers la bas:

=RECHERCHEV(A3;$D$3:$D$37;1;0)

Si la valeur du guichet en A3 n'est pas trouvée dans la plage $D$3:$D$37, la fonction RECHERCHEV() renvoie #N/A, ce qui permet visuellement de bien repérer les valeurs absentes pour les supprimer.

(il est dommage que les deux listes soient au même niveau en haut de la feuille, car ensuite il suffirait de faire une recherche de cellule en colonne B, en cochant la case "erreur", puis clic droit "supprimer ligne entière". Ce n'est pas possible ici sous peine de supprimer les éléments du tableau de droite)

Voilà tokren j'espère que cela pourra t'aider, avec les formules écrites dans ton fichier pour voir plus concrètement.

Très bonne soirée,

mth

Bonjour tokren, bonjour le forum,

Merci tokren pour ton MP, ceci dit il est mieux de déposer les réponses sur le forum.

Du coup je me permets de passer moi même ton sujet en "résolu"

La fonction RECHERCHEV() fonctionne très bien dans ce cas là, mais elle ne te donne que le premier résultat trouvé, pas les suivants.

Je suis ravie que tu aies pu résoudre ton problème, très bonne soirée et à une prochaine fois sur le forum,

mth

Citation :