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