Besoin d'aide sur formule compliquée, voir une macro.. Help please ! [Résolu/Fermé]

Signaler
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
-
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
-
Bonjour,

J’ai besoin d’un expert en Excel !

Je cherche à travailler sur le groupement de commande chez un client, et on souhaiterait leur donner une estimation du gain potentiel en coût de transport si ils groupaient leurs commandes avant de nous les envoyer.

Pour ça, je travaille sur le fichier en PJ. (extrait du fichier original qui fait 10 Mo.. )

Sur ce fichier, une ligne = une commande, un client.

On se dit que des commandes pourraient être groupées jusqu’à 4 jours après la ère commande (en date glissante). Passé 4 jours, le compteur doit redémarrer à la prochaine commande.

J’essaye d’illustrer un peu l’idée pour être plus clair dans l'onglet "exemple" de mon fichier.

Dans l'onglet données, j’ai numéroté les jours (date d’intégration) pour supprimer le pbl des samedi et dimanche dans le calcul des écarts entre les commandes. Par exemple, j’ai les jours de 1 à 5 pour aller du lundi au vendredi, et le 6ème jour et le lundi suivant, et ainsi de suite..

J’ai ensuite trié les données par code client et par n° de jour croissant.

Je voudrais en colonne N, un résultat qui affiche une valeur commune sur chaque ligne de commande regroupable. « A » et « B » dans mon exemple, et ceci par client (1 client une couleur dans le fichier)..

Comme ça je n’aurai plus qu’à faire une TCD pour additionner les poids et les colis par regroupement, et par client. Ça me permettrait de calculer un nouveau coût de transport, et donc de voir les éventuelles économies..

Mais pbl, je ne vois pas du tout comment avoir ce résultat…

Quelqu'un pour m'aider ?

D'avance merci pour votre aide.

Voici mon fichier : https://mon-partage.fr/f/NIrSqt3U/

17 réponses

Messages postés
679
Date d'inscription
mardi 5 juin 2018
Statut
Membre
Dernière intervention
7 avril 2020
52
Bonjour,

Pourquoi A et pourquoi B ?

Cordialement.

Daniel
1
Merci

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez Comment Ça Marche

CCM 65478 internautes nous ont dit merci ce mois-ci

Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Je n'ai pas envoyé le bon fichier ! le voici
https://mon-partage.fr/f/Ak2OTOcJ/

J'ai également traité sur celui ci le cas du dépassement de la lettre Z pour le codage, on passe ensuite à AA, AB ...

Messages postés
5469
Date d'inscription
lundi 16 avril 2012
Statut
Contributeur
Dernière intervention
19 juin 2020
452
Bonjour ludodil62

Pourquoi compliquer les choses !

Utiliser le numéro de semaine pour faire le regroupement

Mettre cette formule dans une colonne supplémentaire en prenant la date en colonne E ou L

=NO.SEMAINE(E2;21) ou =NO.SEMAINE(L2;21)

puis trier par numéro se semaine
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Bonjour,

Daniel,

Ceux sont des exemples A et B. Si c'est plus simple de mettre 1 et 2, pourquoi pas. Et 0 sur les non regroupables par exemple. Le principal est d'avoir une valeur commune sur chacun des regroupement possible, mais une valeur différente par regroupement et par client.

Philou,

J'ai proposé de faire une analyse à la semaine, ça serait effectivement plus facile. Mais notre client ne veut pas malheureusement, je dois faire une analyse en jour glissant a partir de la 1ère commande et étendre la recherche sur 4 jours.. Au 5ème jour, le compteur doit repartir à 0.

Pas simple je te l'accorde !
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Bonjour

Avec une macro
https://mon-partage.fr/f/1AA5VeuG/

Cdlmnt
Via
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Merci beaucoup via55 ! C'est exactement ce qu'il me fallait !

Je ne pensais pas que c'était possible. Il va falloir que je me mette au VBA, ça multiplie les possibilités !

Par contre je ne suis pas à l'abri que mon client demande ensuite la même analyse, sur 3 ou 5 jours d'intervalle par exemple.

Que devrais je changer dans ce code ?

Sub regrouper()
Dim Ligne As Long
Ligne = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
Range("N4:N" & Ligne).ClearContents

For n = 4 To Ligne
If Range("C" & n) <> Range("C" & n - 1) Then x = Range("C" & n): cod = 64
If Range("M" & n + 1) - Range("M" & n) < 5 And Range("N" & n) = "" And Range("C" & n) = Range("C" & n + 1) Then
cod = cod + 1
Range("N" & n) = Chr(cod)
Range("N" & n + 1) = Chr(cod)
For y = 1 To 3
If Range("M" & n + 1 + y) - Range("M" & n) < 5 And Range("C" & n) = Range("C" & n + 1 + y) Then Range("N" & n + 1 + y) = Chr(cod)
Next
End If
Next

End Sub


En tout cas, encore un grand merci ! ça va bien m'aider.
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Bien

Satisfait que cela te convienne

Ce sont d'abord les deux lignes où il y a Range("M" & n) < 5 qu'il faut modifier
le <5 conditionne une différence maximale de 4 jours, il suffit de le remplacer par 4 ou par 6

Ensuite le nombre de lignes en dessous de celle avec le 1er A ( ou le 1ere B..) étudiés dépend de la boucle For y = 1 To 3 (ici 3 lignes), si on augmente le nombre de jours il faut augmenter en conséquence le nombre de lignes prises en compte et donc remplacer 3 par 4, 5 ...

Cdlmnt
Via

Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Très bien, c'est noté.

Merci pour cette aide précieuse apportée ! Je vais pourvoir aller plus loin dans mon analyse grâce à toi.

A une prochaine fois peut-être.

Bye !
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Bonjour à tous,

J'ai de nouveau besoin de votre aide.

Mon besoin de regroupement de commande à changé, il ne faut plus faire sur 5 jours glissant, mais d'une autre façon.

Pour faire plus simple, un exemple est plus simple qu'un long discours.

Voici le fihier que j'avais avant, ca fonctionnait parfaitement avec la maro de via55 que je remercie encore :

https://mon-partage.fr/f/aTGNRCwb/

Et voici ce qu'il faudrait faire maintenant :

https://mon-partage.fr/f/pSebc1JJ/

Quelqu'un saurait il modifier la macro en ce sens ? Je n'y connais rien en macro, et je ne vois pas comment faire autrement malheureusement..

Merci d'avance pour votre aide !
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Désolé, je viens de me rendre compte d'une erreur sur le deuxième lien, voici le bon :

https://mon-partage.fr/f/q0psssO0/
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Bonjour Ludovic

Voir si ça te convient
https://mon-partage.fr/f/hEQKU7Bp/

J'ai annoté la macro pour comprendre ce qu'elle fait

J'ai rajouté une formule dans la colonne V pour différencier un client sur deux ce qui permet par mise en forme conditionnelle de visualiser mieux chaque bloc de commandes par client, j'ai également différencié par MFC les lignes Réassort et Reliquat

Cdlmnt
Via

Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Bonjour via55,

Je viens de regarder le fichier, et c'est une nouvelle fois exactement ce qu'il me fallait ! MERCI !!

Par contre en exemple je n'ai mis qu'un extrait du fichier original qui doit faire environ 50 000 lignes.

Pour recalculer tout ça, je n'ai qu'a copier les données à la suite ? Même mise en forme, même tri (par client puis n°jour intégration) ? Et je n'aurai plus qu'à lancer la macro ?

Cdlt

Ludovic
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Re

Oui exactement, la macro repère la dernière ligne remplie de la colonne 1
Par contre avec 50 000 lignes ça va ramer un peu je pense !

Tiens moi au courant
A plus
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Bonjour Via 55,

Bon, effectivement avec 50 000 lignes ça a ramé un peu.. mais après environ 15 minutes de patience sans toucher le PC, ça a fonctionné ! Encore merci pour le travail effectué.

Par contre ce n'est pas possible de faire en sorte de ne mettre des lettres que sur les commandes groupées ?

En effet, si je veux identifier les commandes groupées par une lettre, c'est pour pouvoir les isoler du reste et calculer un nouveau volume de commande en fonction de ce critère commun via un TCD.

Du coup j'ai galéré un peu pour supprimer les lettres sur les commandes non groupées. C'était un peu galère mais j'ai fini par y arriver. C'est complexe de modifier la macro pour avoir ce résultat (cellule vide si pas de groupement possible) ?

Cdlt.

Ludovic
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Bonjour Ludovic

Pour les reliquats isolés c'est simple il te suffit de supprimer purement et simplement vers la fin de la macro ces lignes :
   Else ' sinon on incremente le code et on inscrit pour le reliquat isolé
                 cod = cod + 1: Range("N" & n) = Chr(cod)


par contre pour les réassorts il faut que je regarde

Cdlmnt
Via
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Re

Voir si cette macro te donne les résultats attendus, en principe lettre que pour commandes groupées
https://mon-partage.fr/f/g6v1FLhG/

Par contre je me pose une question concernant les jours d’intégration, tu les as tous calculé un par un ? cela doit être possible par formule ou par macro
Messages postés
12359
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
1 juillet 2020
1 915
Bonjour

Je t'avais indiqué les premières lignes à supprimer dans mon message 15 et je ne l'ai pas fait dans le fichier que je t'ai envoyé dans le 16, je me suis occupé des réassorts mais j'ai zappé les reliquats !
Voilà le bon fichier :
https://mon-partage.fr/f/cZPMrV4x/

Cdlmnt
Via
Messages postés
63
Date d'inscription
jeudi 1 mai 2014
Statut
Membre
Dernière intervention
8 mars 2020
3
Bonjour,

Merci pour les fichiers. Je vais tester avec le deuxième dans ce cas.

Pour les jours d'intégration, j'ai fait simple.
Je les ai trié du plus ancien au plus récent. J'ai inséré une colonne a droite de la date, puis saisi 1 dans la première cellule.

Sur les cellules du dessous, jai fait :
Si date = date du dessus, alors même valeur qu'au dessus (soit 1), sinon valeur du dessus + 1.

Ensuite j'ai retrié par numéro de client et numéro de jour.

Je teste le fichier avec mes 50 000 lignes et te tiens au courant. Je vais avoir la même analyse a faire mais sur une autre période.

Cdt

Ludovic