Bonjour Mickaël, bonjour à tous,

 

Une suggestion par macro avec ce code, mais on peut aussi bien le faire à la main avec une formule et une suppression de lignes si cela te semble trop compliqué.

 

Le code dans un module standard, affecté au bouton "Go" dans la feuille 1 du fichier joint:

Sub Macro1()
Dim Dlig As Long

With Sheets("Feuil1")
    'test pour éviter de traiter plusieurs fois les données
    If .Cells(1, 6) = "Données traitées" Then Exit Sub
    'calcul de la dernière ligne
    Dlig = .Cells(Rows.Count, 1).End(xlUp).Row
    'tri des données
    With .Sort.SortFields
        .Clear
        .Add Key:=Range("A2:A" & Dlig), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("B2:B" & Dlig), _
             SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Feuil1").Sort
            .SetRange Range("A1:E" & Dlig)
             .Header = xlYes
             .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    'formule de test colonne F(même numéro, appel > 32 secondes)
    .Range("F2").FormulaR1C1 = _
        "=IF(AND(SUMPRODUCT((R2C1:R6C1=RC[-5])*(R2C3:R6C3=RC[-3]))>1,RC[-4]-R[-1]C[-4]>0.00037037037037037),""ok"",NA())"
        .Range("F2").Copy .Range("F2:F" & Dlig)
     ' élimination des erreurs
    .Range("F3:F" & Dlig).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    'Efface la colonne F
    .Columns("F:F").ClearContents
    'indicateur données traitées
    .Cells(1, 6) = "Données traitées"
   
End With

End Sub

Si non avec une formule voir feuille 2 du fichier joint

 

Vois si cela peut t'aider,

 

Très bonne soirée,

 

mth

Bonjour Coskippy, bonjour à tous,

 

Tu dois pouvoir utiliser un filtre élaboré, par exemple dans ton classeur joint j'ai mis les deux critères feuille "REF C2" cellules C1:D2 puis ce code affecté au bouton bleu "Go":

 

Option Explicit
Sub filtre()

Dim dlig As Long

With ActiveSheet
    'Calcule la dernière ligne de la feuille pour effacer
    'le contenu de la plage filtrée
    dlig = .Cells(Rows.Count, 1).End(xlUp).Row
    If dlig > 8 Then .Range("A8:N" & dlig).ClearContents
    'Lance le filtre:
    Sheets("Affaires").Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=.Range("C1:D2"), CopyToRange:=.Range("A8:N8")
End With
End Sub

Vois si cela peut t'aider,

 

Bien à toi,

 

mth

Bonjour Coskippy,

 

Ton fichier n'est pas passé, peux-tu essayer à nouveau de le joindre, ou un extrait allégé/zippé s'il est trop gros? ce sera plus facile pour que l'un d'entre nous puisse t'aider.

 

Bien à toi,

 

mth

Bonsoir le forum, bonsoir eudocha,

 

Sincèrement désolée d'avoir tant tardé à te répondre.

A la place de la formule actuelle, tu peux utiliser celle-ci:

=SI(D2<>"";REPT("0";3-NBCAR(NB.SI(D$2:D2;D2)))&NB.SI(D$2:D2;D2)&MINUSCULE(GAUCHE(D2;1)&STXT(D2;CHERCHE("_";D2)+1;1));"")&SI(ESTNUM(DROITE(D2;1)*1);DROITE(D2;1)*1;"")

A placer en cellule E2 de l'onglet "TableProducteurs" puis copier vers le bas.

 

En espérant que cela puisse t'aider,

Très bonne soirée,

 

mth

Bonsoir eudocha,

 

Tu trouveras ton fichier joint, sur lequel j'ai corrigé un décalage d'une position dans la liste déroulante (cette erreur vient de moi je crois, désolée...)

 

Concernant les numéros 004ie 005ie 007ie ou plus, ils n'étaient pas créés au préalable dans la liste des producteurs (colonne D de l'onglet TablesProducteurs). Si tu les crées d'abord, tu les auras à disposition dans ta liste déroulante.

C'est là une limite de la réponse que je t'ai fournie, car il faut créer au préalable les numéros dont on a besoin avant de les avoir dans la liste déroulante, alors que toi tu souhaitais pouvoir soit utiliser un numéro existant soit en créer un nouveau dans la foulée au moment de ta saisie.

C'est pour ma part beaucoup plus compliqué, du coup je te propose de voir si cette proposition te dépanne en attendant, si non d'autres plus "VBaïstes" que moi pourront te répondre.

 

A très bientôt eudocha,

 

mth

Bonsoir eudocha,

 

As-tu vérifié également que le code VBA placé dans l'onglet TableProducteurs s'exécute correctement?

Il permet de trier les données des Axes et Producteurs des colonnes I J et K ce qui est important pour que la liste de validation fonctionne.

Si vraiment tu ne vois pas, peut-être peux-tu nous renvoyer ton fichier tel quel, mais en limitant à  3 ou 4 lignes dans l'onglet BDD pour qu'il ne soit pas trop lourd. On regardera ce qui ne va pas.

 

Bien à toi,

 

mth

Bonjour eudocha, bonjour le forum,

 

Je suis vraiment ennuyée que tu n'arrives pas à reproduire ce numéro producteur dans ta base réelle.

Si l'on reprend étape par étape pour ce numéro:

 

Dans l'onglet "TableProducteurs" as-tu bien ce code:

Il permet trier les Axes et Producteurs dans les colonnes I J K:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Columns(4)) Is Nothing Then
Dim lig As Long, Plage As Range
    With ActiveSheet
        Target.Offset(0, 2) = Date
        lig = .Cells(Rows.Count, 4).End(xlUp).Row
        Set Plage = .Range("D1:F" & lig)
        Plage.Copy
        .Range("I1").PasteSpecial Paste:=xlPasteValues
            With .Sort.SortFields
             .Clear
             .Add Key:=Range("I2:I" & lig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                 xlSortNormal
             .Add Key:=Range("J2:J" & lig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                 xlSortNormal
            End With
            
            With .Sort
             .SetRange Range("I1:K" & lig)
             .Header = xlYes
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
            End With
    End With
End If
Application.CutCopyMode = False
Target.Select
Application.ScreenUpdating = True
End Sub

Ensuite dans les zones nommées, as-tu bien dans ton fichier cette zone: "Numproducteur", qui fait référence à:

=DECALER(TableProducteurs!$J$2;;;NBVAL(TableProducteurs!$J:$J)-1;)

ainsi que cette zone "AxesTirés":

=DECALER(TableProducteurs!$I$2;;;NBVAL(TableProducteurs!$I:$I)-1;)

Enfin, dans ton onglet BDD colonne F, ta liste de validation fait-elle bien référence à ceci:

=DECALER(NumProducteur;EQUIV($B6;AxesTriés;0);;NB.SI(AxesTriés;$B6))

Peux-tu vérifier étape par étape, si tu n'y arrivais pas pourras-tu nous dire à quel endroit tu bloques?

 

Bon courage eudocha et à très bientôt,

 

mth

Bonjour Eudocha, bonjour à tous

 

Je te remercie pour ton message, mais je te conseille de retirer très vite ton adresse mail en clair dans le forum, sans quoi les robots du net vont te repérer et ta boite mail sera envahie de spams .

Quant aux explications, c'est un peu l'esprit du site XLPages, si tu as l'occasion de lire des réponses de JeanMarie par exemple, tu verras qu'il prend soin de toujours expliquer les choses, ses posts sont souvent de véritables cours

 

Concernant les listes de validation de l'onglet principal:

 

1. Colonne A "Département": La liste de validation fait référence à la zone nommée "Départements". Tu peux le voir en sélectionnant une cellule colonne A et en cliquant dans le ruban sur l'onglet "Données" puis "Validation des données", dans la fenêtre de validation de données tu vois la source: =Departements

Pour voir à quoi correspond ce nom "Departements", dans le ruban onglet "Formules" tu cliques sur "Gestionnaire de noms", dans la fenêtre qui s'ouvre le nom "Departements" apparaît et en cliquant dessus tu vois qu'il fait référence aux cellules A1:C1 de l'onglet Tables, soit =Tables!$A$1:$C$1

 

1. Colonne B "Axe": Dans le Gestionnaire de noms, apparaissent 3 zones nommées pour les Axes:

l'axe SANGHA faisant référence à la plage A2:A10 de l'onglet Tables soit:  =Tables!$A$2:$A$10

l'axe LIKOUALA : =Tables!$B$2:$B$6

CUVETTE_OUEST: =Tables!$C$2

Dans la colonne B de l'onglet principal, j'utilise la fonction INDIRECT() pour la liste de validation afin de faire référence à la zone nommée SANGHA LIKOUALA ou CUVETTE_OUEST qui est renseignée colonne A. La formule de la source de valilation de cette colonne B est =INDIRECT($A6) pour la première ligne.

 

1. Colonne E "Village": Tu vois dans l'onglet "Tables"que j'ai organisé un peu différemment la liste des villages par rapport à ton fichier initial, les villages sont en colonne (colonne P) avec leur Axe à coté colonne O, et, point très important, ils sont triés par Axe.

Dans le gestionnaire de noms apparaît le nom "Village" qui fait référence aux cellules remplies de cette colonne P, avec cette formule:

=DECALER(Tables!$P$2;;;NBVAL(Tables!$P:$P)-1;)

C'est ce qu'on appelle une zone dynamique, tu trouveras quelques explications sur les zones dynamiques ICI.

Sur le même principe j'ai également nommé les axes de la colonne O, la zone nommée "Axe" est une zone dynamique qui fait référence aux cellules remplies de la colonne O avec cette formule:

=DECALER(Tables!$O$2;;;NBVAL(Tables!$O:$O)-1;)

J'utilise ces zones dynamiques Village et Axe dans la liste de validation de l'onglet principal colonne E. Cette liste a comme source:

=DECALER(Village;EQUIV($B6;Axe;0)-1;;NB.SI(Axe;$B6))

Un peu d'explications en complément de ce que tu trouveras dans le tutoriel sur les zones dynamiques:

- Point de départ de la fonction DECALER est la zone Village

- Le décalage de ligne est calculé avec EQUIV($B6;Axe;0)-1. La fonction EQUIV est bien expliquée dans l'aide Excel, elle permet d'indiquer la position d'un élément dans une plage. Ici, je cherche la position de la valeur en B6 (soit Epéna_Sud) au sein de la zone Axe. Cette formule renvoie 83, et je retire 1 pour tenir compte de la présence d'une entête colonne P. Si tu descends le long de cette colonne P tu trouves effectivement pour la première fois le libellé Epéna_Sud ligne 84

- Pas de décalage de colonne (les deux point-virgules sont l'un à coté de l'autre ce qui équivaut à zéro)

- Hauteur de la zone: elle est calculée grâce à: NB.SI(Axe;$B6), cette fonction renvoie le nombre de fois où apparaît le libellé cellule B6, dans la zone Axe. Ici cette fonction renvoie 14, il y a effectivement 14 lignes concernant Epéna_Sud dans la colonne P.

 

Pour finir, notre formule =DECALER(Village;EQUIV($B6;Axe;0)-1;;NB.SI(Axe;$B6)) part de la zone Village colonne P, se décale de 83 lignes vers le bas pour se retrouver en cellule P84, puis prend une hauteur de 14. Cette formule permet donc de définir toute la zone de P84 à P97 qui correspond à Epéna_Sud de la cellule B6 de l'onglet principal.

 

1. Colonne F: Numéro Producteur.

Pour la gestion de ces numéros j'utilise les données de l'onglet "TableProducteurs".

La liste des Axes se trouve colonne A.

Dans la colonne D se trouve une liste déroulante permettant de choisir l'axe sur lequel on veut créer un producteur. Elle fait référence à la zone nommée ListeAxes, qui est simplement définie comme ceci: =TableProducteurs!$A$2:$A$16

La formule de la colonne E est décrite dans le post précédent.

A chaque modification de la colonne D de cet onglet "TableProducteurs", la macro (que tu peux voir dans le code de la feuille) copie les données colonnes D E F et les met colonnes I J K , mais en les triant (très important pour la suite) par axe  et par numéro de producteur.

C'est cette zone triée que j'utilise pour la validation de données de la colonne F de l'onglet principal.

Exactement sur le même principe, je nomme la zone NumProducteurs comme ceci:

=DECALER(TableProducteurs!$J$1;;;NBVAL(TableProducteurs!$J:$J)-1;)

Puis la zone AxesTriés comme ceci:

=DECALER(TableProducteurs!$I$2;;;NBVAL(TableProducteurs!$I:$I)-1;)

Je fais référence à ces zones dans la validation de données:

=DECALER(NumProducteur;EQUIV($B6;AxesTriés;0);;NB.SI(AxesTriés;$B6))

Tu reconnais là le même type de formule que pour les villages ci-dessus.

 

 

A partir de l'onglet principal, une fois que tu as mis en place une validation de données, tu peux sélectionner ta cellule, puis "Copier", sélectionner les cellues où tu souhaites mettre en place ta liste de validation, puis "Collage Spécial", choisir "Validation".

 

Voilà Eudocha, en espérant que cela puisse t'aider, si non dis nous.

A très bientôt

 

mth

Bonjour Eudocha, bonjour à tous,

 

Concernant le numéro du producteur il ne s'arrête pas à 4, mais mes explications étaient sans doute trop sommaires, désolée.

Si tu vas dans l'onglet "TableProducteurs", colonne D dans la première cellule vide, tu as une liste déroulante te permettant de choisir l'axe pour lequel tu souhaites créer un nouveau numéro de producteur, cette liste déroulante fait référence à la zone nommée ListeAxes

Dès que tu as effectué ton choix, la formule dans la colonne E indique le nouveau numéro du producteur (j'ai ajouté par macro l'inscription de la date au cas où, en colonne F)

 

Cette formule est la suivante en E2 par exemple:

 

=SI(D2<>"";REPT("0";3-NBCAR(NB.SI(D$2:D2;D2)))&NB.SI(D$2:D2;D2)&MINUSCULE(GAUCHE(D2;1)&STXT(D2;CHERCHE("_";D2)+1;1));"")

 

En détaillant un peu:

• CHERCHE("_";D2) indique la position du caractère _ dans le nom de l'axe. En cellule E2 le résultat renvoyé est 6, effectivement pour Epena_Nord le - est en 6ème position, il suffit d'ajouter 1 pour avoir la position du caractère suivant soit le N qui sera utilisé dans le nom du producteur. -voir ICI pour la fonction CHERCHE()-
• La fonction STXT() (voir ICI) permet d'extraire une chaine de caractères, ici elle va extraire au sein de la cellule D2 un chaîne de caractères commençant à la position 7 (caluclée ci-dessus avec CHERCHE()) et d'une longueur de 1. STXT() renvoie donc la lettre N
• GAUCHE(D2;1)  (voir ICI pour la fonction GAUCHE() ) cette fonction permet de renvoyer le premier caractère à gauche de la cellule D2, soit la lettre E
• le signe & est équivalent à la fonction CONCATENER() -voir ICI-, cela permet d'obtenir les deux caractères l'un à la suite de l'autre soit EN
• La fonction MINUSCULE() permet de transformer ce résultat en lettres minuscules pour obtenir: en
• NB.SI(D$2:D2;D2) permet de calculer le nombre de fois où le contenu de la cellule D2 apparaît dans la plage de cellule D$2:D2. Ligne 2 cette fonction renvoie 1 car le libellé Epéna_Nord n'est présent qu'une fois. En passant ligne 2 qui contient également Epéna_Nord, cette fonction NB.SI() renvoie 2 car ce libellé apparaît pour la deuxième fois. D'autres libellés différents se succèdent jusqu'à la ligne 30 où l'on voit à nouveau Epéna_Nord, la fonction NB.SI() sur cette ligne renvoie 3 car c'est effectivement la 3ème fois que ce libellé est présent dans la colonne. Ce numéro passera à 4, 5, 6, etc. en augmentant de 1 à chaque fois que Epéna_Nord apparaîtra.

Comme tout à l'heure le signe & permet de concatener les résultats, les différents calculs ci-dessus permettent d'obtenir par exemple lige 2 le résultat 1en

 

Afin de faire précéder ce résultat de 1 ou 2 zéros j'utilise cette partie de la formule:

REPT("0";3-NBCAR(NB.SI(D$2:D2;D2)))

• NB.SI(D$2:D2;D2) donne la même chose que tout à l'heure
• la fonction NBCAR() (voir ICI)permet de compter le nombre de caractères  d'une chaîne, ainsi si la fonction NB.SI() renvoie 3, le nombre de caractères renvoyé par NBCAR() sera 1, si NB.SI() renvoie 25, NBCAR() renverra 2 caractères.
• Si tu peux avoir 100 producteurs, cela signifie que l'on peut avoir 3 caractères maximum.
• C'est là qu'intervient la fonction REPT() (voir ICI) qui permet de répéter des caractères un certain nombre de fois, ici le caractère à répéter est le 0, qui apparaîtra 2 fois (exemple 002) 1 fois (exemple 025) ou jamais.
• Ce nombre de répétition du caractère zéro est calculé par 3-NBCAR(NB.SI(D$2:D2;D2)), 3 parce que c'est lé maximum, NBCAR(NB.SI(D$2:D2;D2)) pouvant renvoyer 1 2 ou 3. si j'ai 2 caractères je complète par un seul zéro devant (3-2=1), si j'ai un caractère je complète par deux zéros devant (3-1=2) etc.

 Pour la ligne 2 j'obtiens deux fois le caractère 0 

 

En concaténant le tout j'obtiens donc 001en ligne 2.

 

Voilà pour les explications de la formule. La logique que j'ai suivie consiste à créer d'abord le nouveau numéro du producteur si le cas se présente. Une fois le numéro créé, il sera alors disponible dans la liste déroulante de la colonne F de la table principale.

 

D'après ce que je comprends tu souhaiterais éviter cette étape intermédiaire en remplissant ta base directement soit avec un numéro existant soit en en créant un dans la foulée.  J'imagine qu'il faudrait ouvrir un Userform au clic des cellules colonne F, ce Userform présenterait les numéros existants pour l'axe en question, tout en offrant la possibilité d'en créer un nouveau. C'est certainement faisable mais beaucoup plus compliqué (me semble-t-il) y compris pour toi qui devra par la suite assurer la maintenance de cette base.

 

Vois si déjà tu peux tirer quelque chose du fichier joint, on pourra peut-être affiner ensuite.

 

Très bonne soirée,

 

mth

 

 

Bonjour Eudocha, bonjour le forum,

 

Merci Eudocha pour tes explications, concernant ce numéro de producteur tu trouveras une approche parmi d'autres dans le fichier joint.

J'ai créé un autre onglet "TableProducteurs", permettant d'ajouter un producteur pour les axes, l'axe se choisit colonne D dans une liste déroulante, la formule colonne E permet de lui attribuer son numéro sans doublon.

J'ai placé du code dans la feuille pour trier cette liste lors de chaque ajout ou modification colonne D, la liste triée se place à coté colonne I et c'est cette liste que j'utilise dans l'onglet principal pour la liste déroulante de la colonne F.

 

Concernant le calcul colonne T, j'ai cru comprendre qu'il y avait deux cas de figure Sacs ou Kilos, et que les colonnes n'étaient donc pas toutes remplies en même temps d'où une formule toute simple.

 

Vois si cela peut t'aider sinon dis nous pour que l'on cherche autre chose.

 

Bien à toi,

 

Mth