Microsoft 365 Récupérer en commentaires un nombre de colis par rapport à une référence

ivan27 · 21 Novembre 2020

Bonsoir à tous,

Le titre n'est pas très explicite mais vous comprendrez mieux ma demande en téléchargeant le fichier exemple.
J'ai un tableau avec des références en colonne C et des commentaires en colonne G.
Je souhaite modifier chaque cellule de la colonne G en fonction du nombre de références.
Je vous ai mis le résultat attendu en colonne L
Merci d'avance pour votre aide.
Bonne soirée

Ivan

mapomme · 22 Novembre 2020

Bonjour @ivan27, @Recycleur

Un essai par VBA. Cliquer sur le bouton Hop!

Le code est dans module1.

VB:

Const Feuille = "Feuil1"      'feuilles des données
Const debut = "B6"            'première cellule des données (yc en-tête)

Sub Commenter()
Dim derlig&, t, i&, clef, s$, r$, comm$, n&

   Application.ScreenUpdating = False
   Sheets(Feuille).Activate
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
   derlig = Range(debut).Offset(, 1).End(xlDown).Row
   t = Range(Range(debut).Offset(1), Cells(derlig, Range(debut).Column + 5))
   Set dico = CreateObject("scripting.dictionary")
   dico.CompareMode = TextCompare
 
   For i = 1 To UBound(t): dico(t(i, 2)) = dico(t(i, 2)) & ";" & t(i, 6): Next

   For Each clef...

Recycleur · 21 Novembre 2020

Comment feras-tu pour indiquer le type de colis si tu mets une formule dans la colonne G? Une cellule ne peut pas contenir à la fois une valeur entrée manuellement et une formule.

ivan27 · 21 Novembre 2020

Re bonsoir le Forum, Recycleur,

Effectivement.
Idéalement c'est d'utiliser VBA; de récupérer la valeur de la colonne G; sa référence en C puis de compter le nombre de références identiques sur toute la colonne C et enfin de réécrire le commentaire avec le nombre de références obtenues.
et pourquoi pas de respecter le singulier et le pluriel....
Ivan

Recycleur · 22 Novembre 2020

ivan27, voici d'autres questions car avant d'entreprendre l'ingénierie d'un projet il faut bien en comprendre les enjeux. Si VBA peut être évité, je pense que c'est plus pratique.
1- Le tableau présenté doit-il absolument conserver cette forme, avec exactement les mêmes colonnes sans plus ni moins?
2- Est-il possible de rajouter une autre feuille au classeur?
3- Est-il efficace pour l'opérateur d'entrer des textes complexes dans les cases, comme COLIS MANQUANT TOTAL? À la longue c'est du temps improductif qui s'accumule et les fautes de frappe risquent de dérailler le processus. Mais peut-être que ces données proviennent telles quelles d'un autre système?

Solution sans VBA si la réponse est oui aux deux premières questions
Si c'est possible, j'ajouterais trois minces colonnes à gauche du commentaire, ou ailleurs, chacune représentant un des trois cas. La sélection serait rapide en ne tapant qu'un x au bon endroit. Et voilà, les formules s'occupent du reste.
Explications: j'ajoute une deuxième feuille sur laquelle on compile la liste de tous les NMR uniques de la feuille 1. J'ai prévu presque 200 actifs en tout moment, étends si nécessaire et n'oublie pas en même temps les formules de la colonne J de la feuille 1. Pour chacun des NMR je compile le nombre de colis des trois catégories, je masse tout cela en chaînes de caractères, puis la colonne J de la feuille 1 retourne chercher son résultat. Si ta version d'Excel ne supporte pas la fonction RECHERCHEX, on peut la remplacer par RECHERCHEV en modifiant légèrement les formules.

Solution avec VBA s'il n'est pas possible de modifier le fichier
À suivre.

Jean

ivan27 · 22 Novembre 2020

Bonjour le Forum, Jean,
1 - Le tableau tel que présenté, l'est par mon logiciel métier et l'ordre et le contenu des colonnes est imposé par mon client.
2 - On peut ajouter d'autres feuilles mais pas d'autre classeur
3 - Les termes ''COLIS.....'' ne sont pas saisis dans cette feuille qui provient du logiciel métier.
Je privilégie VBA car ce tableau sera distribué à divers utilisateurs et je voudrais éviter une suppression accidentelle.
Bien cordialement,

mapomme · 22 Novembre 2020

Bonjour @ivan27, @Recycleur

Un essai par VBA. Cliquer sur le bouton Hop!

Le code est dans module1.

VB:

Const Feuille = "Feuil1"      'feuilles des données
Const debut = "B6"            'première cellule des données (yc en-tête)

Sub Commenter()
Dim derlig&, t, i&, clef, s$, r$, comm$, n&

   Application.ScreenUpdating = False
   Sheets(Feuille).Activate
   If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
   derlig = Range(debut).Offset(, 1).End(xlDown).Row
   t = Range(Range(debut).Offset(1), Cells(derlig, Range(debut).Column + 5))
   Set dico = CreateObject("scripting.dictionary")
   dico.CompareMode = TextCompare
 
   For i = 1 To UBound(t): dico(t(i, 2)) = dico(t(i, 2)) & ";" & t(i, 6): Next

   For Each clef In dico
      s = dico(clef) & ";": dico(clef) = "": r = ""
      Do While s <> ""
         Do While Left(s, 1) = ";": s = Mid(s, 2): Loop
         If s = "" Then Exit Do
         comm = Split(s, ";")(0) & ";"
         If comm = "" Then Exit Do
         n = Len(s): s = Replace(s, comm, ""): n = (n - Len(s)) / Len(comm)
         If n > 0 Then r = r & n & " " & Left(comm, Len(comm) - 1)
         If n > 1 Then r = r & "S"
         r = Replace(r & " - ", "TOTALS", "TOTAL")
         dico(clef) = r
      Loop
   Next clef
       
   For i = 1 To UBound(t): t(i, 6) = dico(t(i, 2)): Next
   For i = 1 To UBound(t)
      If Len(t(i, 6)) <> 0 Then t(i, 6) = Left(t(i, 6), Len(t(i, 6)) - 3)
   Next i
   Range(Cells(Range(debut).Row + 1, Range(debut).Column + 10), Cells(derlig, Range(debut).Column + 10)) = Application.Index(t, 0, 6)
End Sub

ivan27 · 22 Novembre 2020

Re bonjour le forum, mapomme
Cette proposition correspond très précisément à ma demande. Merci beaucoup !
J'ai juste ajouter la déclaration de la variable 'dico' et renvoyer la copie finale sur la colonne G.
Vraiment Génial.
Je vous réitère mes remerciements.
Bon après-midi
Ivan

mapomme · 22 Novembre 2020

Re,

ivan27 à dit:
J'ai juste ajouter la déclaration de la variable 'dico'

J'avais supprimé une ligne de déclaration inutile où était présente la déclaration du dico. J'ai corrigé et rechargé le bon fichier.

ivan27 à dit:
renvoyer la copie finale sur la colonne G

Pour les tests, j'en avais assez de recopier la colonne G initiale. C'est pourquoi j'ai mis le résultat à place du résultat souhaité.

Bon dimanche

ivan27 · 29 Novembre 2020

Bonjour le forum, mapomme,
Je relance cette discussion suite à une demande de mon client qui souhaite une modification.
mapomme, te serait-il possible de modifier ton code s'il te plaît ?
Sur la colonne 'I', il faudrait compter un nombre de références uniques au lieu de faire une somme.
Exemple : Pour la commande 1222377, c'est la même référence sur les 4 lignes, donc 1 seul colis abîmé.
Je joins un nouvel exemple avec le résultat attendu.
Merci d'avance et bonne fin de journée
Ivan

mapomme · 29 Novembre 2020

Re ,

Un peu de mal à comprendre. Prenons le NMR 1222291.
Je ne vois qu'un référence NMR. Comment arriver à "2 COLIS ABIMES - 1 COLIS MANQUANT" ???
Faut il prendre comme référence le couple (NMR, CAN) ?

Éviter de parler de référence quand le terme n'est p)as défini et ne figure même pas dans les en-têtes des colonnes.

ivan27 · 29 Novembre 2020

Bonsoir mapomme,
Colonne I, 2 numéros différents pour colis abimé.
Bien cordialement,

mapomme · 29 Novembre 2020

Re,

Un essai (vite fait) à vérifier...

Une référence = couple (NMR, CAN)

ivan27 · 29 Novembre 2020

Re bonsoir le forum, mapomme,
Merci beaucoup pour ta réactivité. le résultat semble concluant. Je teste demain sur mon fichier d'exploitation pour confirmer le bon fonctionnement,
Bien cordialement,
Ivan

Microsoft 365 Récupérer en commentaires un nombre de colis par rapport à une référence

ivan27

XLDnaute Occasionnel

Pièces jointes

mapomme

Recycleur

XLDnaute Nouveau

ivan27

XLDnaute Occasionnel

Recycleur

XLDnaute Nouveau

Pièces jointes

ivan27

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

Pièces jointes

ivan27

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

ivan27

XLDnaute Occasionnel

Pièces jointes

mapomme

XLDnaute Barbatruc

ivan27

XLDnaute Occasionnel

mapomme

XLDnaute Barbatruc

Pièces jointes

ivan27

XLDnaute Occasionnel

Discussions similaires