Fonction ou VBA pour placement automatique dans liste

[arnaud130]

Bonjour,

Etant en stage, j'utilise la majeure partie Excel et grâce à ce forum, j'ai déjà pu résoudre de nombreux problèmes sans avoir besoin de demander de l'aide. Cependant, j'ai actuellement un problème que je n'arrive pas du tout à résoudre, même en essayant de chercher sur le forum. Je m'explique.

J'ai des références en colonne A qui sont installés dans l'ordre dans un entrepôt avec une surface en colonne B. En colonne C, j'ai la nouvelle liste de référence et c'est là qu'intervient mon problème. Il faudrait que les références déjà présentes restent à leur place et que les nouvelles références prennent la place des anciennes mais attention, en fonction de la place disponible.

J'ai essayé en faisant certaines colonnes supplémentaires mais en vain. Je crois qu'une VBA serait plus facile mais je ne maitrise pas assez pour en faire un moi-même
En pièce jointe, voici mon exemple avec quelques annotations, n'hésitez pas à créer une nouvelle feuille si vous le souhaitez.

Merci d'avance pour votre aide qui m'aiderait à un point pas possible pour la suite de mon projet.

 

[CISCO]

Re : Fonction ou VBA pour placement automatique dans liste

Bonjour

Cf. en pièce jointe, une possibilité. La formule (matricielle, donc à valider avec Ctrl+maj+entrer) importante est dans la colonne I. Comme elle comporte plusieurs fois le même calcul,

NON(ESTNUM(EQUIV(LIGNE($5:$26);I$4:I4;0)))*(H$5:H$26>D5)*G$5:G$26

on peut nommer cette dernière dans le gestionnaire de noms, ici surfacessélectionnées, ce qui est fait dans le second fichier.

La formule est alors de la forme

SI(MAX(surfacessélectionnées)=0;"";ARRONDI.AU.MULTIPLE(MOD(MIN(SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26+LIGNE($5:$26)/10000;9^9))*10000;100);1))

MAX(surfacessélectionnées) donne la surface la plus grande répondant aux critères correspondant à la ligne en cours.

SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26 permet de garder toutes les dates H$5:H$26 sur les lignes correspondant aux ref avec la surface la plus grande pouvant être placées sur la ligne en cours.

MIN(SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26;9^9) garde la date la plus petite dans cette liste, donc la place libérée le plus tôt. Le 9^9 est là pour ne pas avoir de 0 à la place des FAUX, 0 qui seraient pris comme MIN, 0 que l'on ne veut pas...

Mais comme je veux, non pas cette date, mais le n° de la ligne contenant cette date, je fais avec
MIN(SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26+LIGNE($5:$26)/10000;9^9)

le /10000 servant à donner peu de poids au n° de la ligne (On veut la date la plus petite, avec le MIN, pas le n° de ligne le plus petit).

MOD(MIN(SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26+LIGNE($5:$26)/10000;9^9))*10000;100)
ramène le n° de ligne tout seul. Ex :MOD(1,1525*10000;100) ="reste" de 115025/100 = 25.

Mais cette fonction ne ramène pas toujours un nombre entier. Elle donne par exemple 16,9999 au lieu de 17. Donc, pour contourner ce pb, on passe par
ARRONDI.AU.MULTIPLE(MOD(MIN(SI(surfacessélectionnées=MAX(surfacessélectionnées);H$5:H$26+LIGNE($5:$26)/10000;9^9))*10000;100);1)

@ plus

P.S: la formule ne donne pas le même résultat que toi en ligne 17, mais une autre possibilité convenant aussi.
P.S : le test (H$5:H$26>D5) est inutile dans l'exemple proposé, mais sait-on jamais... Il vérifie que certaines dates de sortie des nouvelles références sont supérieures à la date de sortie de l'ancienne référence de la ligne en cours. Si cela n'est pas le cas, c'est que la nouvelle référence ne peut pas être placée sur la ligne en cours. Si cela n'est jamais le cas, c'est qu'aucune nouvelle ref...

 

[arnaud130]

Re : Fonction ou VBA pour placement automatique dans liste

Bonjour,

Un tout grand merci pour votre aide !

Cependant, j'ai essayé d'implanter votre formule dans mon fichier de base, ce qui donne :

[/{=IF(MAX(NOT(ISNUMBER(MATCH(ROW($407:$432),N$406:N406,0)))*(L$407:L$432<=C407)*(M$407:M$432>D407)*L$407:L$432)=0,"",MROUND(MOD(MIN(IF(NOT(ISNUMBER(MATCH(ROW($407:$432),N$406:N406,0)))*(M$407:M$432>D407)*(L$407:L$432<=C407)*L$407:L$432=MAX(NOT(ISNUMBER(MATCH(ROW($407:$432),N$406:N406,0)))*(L$407:L$432<=C407)*(M$407:M$432>D407)*L$407:L$432),M$407:M$432+ROW($407:$432)/10000,9^9))*10000,100),1))}CODE]

Cela me donne malheureusement beaucoup de numéros les mêmes...

Meri d'avance.

 

[CISCO]

Re : Fonction ou VBA pour placement automatique dans liste

Bonjour

Ma dernière proposition ne fonctionne correctement que si tu utilises de n° de lignes < 100, car j'ai utilisé MOD(....*10000;100) pour ramener ces n° de lignes. Comme tu en as plus, et moins de 1000, tu peux faire avec MOD(....*100000;1000). Fais toi quelques exemples du style
=MOD(15123;10)
=MOD(15123;100)
=MOD(15123;1000)
pour comprendre à quoi sert cette fonction.

Il faut aussi mettre dans J407 la formule SI(I407="";"";INDEX(E$1:E$428;I407)) et tirer cette formule vers le bas.

Je te met en pièce jointe le fichier corrigé. Vas voir vers la ligne 400.

@ plus

 

[arnaud130]

Re : Fonction ou VBA pour placement automatique dans liste

Encore une fois merci, ca a fonctionné à un endroit !

Cependant, alors que je veux aussi utiliser cette formule en dessous pour faire le même tableau que dans l'exemple, j'ai une erreur #VALEUR que je n'arrive pas à résoudre....

Voici ma formule :

=IF(MAX(NOT(ISNUMBER(MATCH(ROW($436:$461),O$435:O435,0)))*(L$436:L$461<=I436)*(N$436:N$461>J436)*L$436:L$461)=0,"",MROUND(MOD(MIN(IF(NOT(ISNUMBER(MATCH(ROW($436:$461),O$435:O435,0)))*(N$436:N$461>J436)*(L$436:L$461<=I436)*L$436:L$461=MAX(NOT(ISNUMBER(MATCH(ROW($436:$461),O$435:O435,0)))*(L$436:L$461<=I436)*(N$436:N$461>J436)*L$436:L$461),N$436:N$461+ROW($436:$461)/100000,9^9))*100000,1000),1))

Encore merci.

 

[CISCO]

Re : Fonction ou VBA pour placement automatique dans liste

Bonjour

Ce n'est pas facile de travailler sur une capture d'écran...Sur la dernière que tu as postée, je vois en haut à droite, des n° de ligne, et juste en dessous des références, dans la même colonne. Normalement, on ne devrait pas avoir ce genre de situation !
Ne peux tu mettre ton fichier, en ne laissant que la partie intéressante dans le cas présent ?

@ plus