Comparaison de données sur trois fichiers

[le_petit_xl]

Bonjour tout le monde,

Je viens vous voir pour demander des cours d'Excel pour non pas me donner la solution mais m'accompagner pour la trouver.

Je vous expose mon projet. J'ai trois fichiers:
- fichier_client (le fichier que le client nous envoie avec ses commandes) -> un code par produit
- proforma (le fichier de référence pour les produits) -> un autre code par produit
- Equivalence client et proforma (concordance entre les deux différents codes)

Avec ces trois fichiers, je dois faire plusieurs opérations.

D'abord, il me faut créer plusieurs variables, je pense, par fichier:
Sur le fichier client: Ref No, Unit, QTY
Sur la proforma: ART, Unit, QTE
Sur l'équivalence: code clt, Unit, code frs

A partir de cela, je dois vérifier plusieurs conditions, sachant que le traitement se fait ligne par ligne sur le fichier client:
Sur le fichier client, si la QTY est supérieure à 0, alors je dois vérifier la concordance avec le code clt dans le fichier équivalence. S'il y a concordance, je dois récupérer le code frs sur la même ligne. Une fois ce code frs récupéré, je dois chercher ce code dans la proforma, qui se trouve dans la colonne ART. Une fois le code trouvé, je dois vérifier que le Unit dans le fichier Client est égal au Unit de la proforma, mais aussi la QTY avec le QTE. Si ce n'est pas égal, je dois surligner la ligne dans le fichier Client.

J'ai un quatrième fichier où je lance la macro afin de choisir le fichier que l'on souhaite traiter. J'ai un début de code si sur ce quatrième fichier, mais je suis coincé dans le for each.

Voilà à peu près le projet (vous trouverez les fichiers en pièces jointes), pour information je suis pas très bon en vba, mais j'ai une soif d'apprendre c'est pour cela que je demande une assistance.

Je vous remercie d'avance

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Bonjour.
Sans avoir ouvert le fichier, je peux vous donner deux conseils :
1) - Utilisez des tableaux de Variant chargés et déchargés dans leur intégralité dans des plages entières. Vous pouvez presque partir du principe de raisonnement qu'un Cells(L, C).Value exécuté 10000 fois dure 10000 fois plus longtemps qu'un Cells(1, 1).Resize(100, 100).Value, lequel est représenté par un tableau de Variant de 100 ligne et 100 colonnes.
2) - Préétablissez les correspondances de codes du proforma à l'aide de collections ou de Dictionary. La référence Microsoft Scripting Runtime cochée vous définit un type Dictionary.

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Merci Dranreb pour ta réponse,

Je vais regarder cela au niveau des tableaux de Variant (d'ailleurs merci pour ton astuce), ainsi que les dictionary.
Cela me fera gagner du temps plutôt que d'aller récupérer dans le fichier de correspondance le bon code, cela sera j'imagine automatique en appelant le Dictionary.

Je reviens vers vous dès que j'ai mis en place cela.

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Bonjour,

Alorc concernant le Dictionary, je suis arrivé à le mettre sur le fichier d'équivalence, lancé automatiquement lorsqu'il s'ouvre cependant je n'ai pas bien compris comment utiliser les tableaux de Variant. Les exemples que j'ai trouvé sont des exemples où les tableaux sont à remplir et non remplis.

Est-ce que quelqu'un a des exemples concrets par rapport à mon cas ?

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Bonjour.
Je ne comprends pas bien ce que vous dites.
Votre procédure doit en gros, si j'ai bien compris, faire en mémoire les opération suivantes dans cet ordre :
1) - Charger le proforma en Dictionary
2) - Charger en tableau de Variant la colonne des code dont on veut l'équivalence dans une autre colonne
3) - Explorer ce tableau et y remplacer chaque code par son correspondant donné par le Dictionary
4) - Décharger le tableau aux codes maintenant convertis dans sa colonne destinatrice.

Remarque: La Value d'un Range de cellules contigües prises d'un seule colonne est un tableau à 2 dimension dont la seconde est toujours 1.

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

En posant à plat mon projet, la procédure, dans l'idéal, doit être de la sorte:

1) Charger le Dictionary sur le fichier d'équivalence afin d'avoir la concordance entre les deux différents codes (un code client et un code proforma)
2) Dans fichier client, si la quantité est supérieure à 0, récupérer Ref No, Unit et QTY (quantité)
3) On va dans le fichier d'équivalence et on récupère le code frs équivalent à Ref No récupéré précédemment (code clt dans le fichier d'équivalence)
4) Aller dans le fichier proforma et chercher le code frs récupéré et voir sur la ligne sélectionnée le Unit et le Qte (quantité)
5) Vérifier que QTY et QTE sont semblables tous comme les deux Units, sinon on surligne les colonnes qui ne sont pas correspondantes.

Comme je dois le faire ligne par ligne, je sais qu'il faut que je passe par un For Each.

Mais lorsque tu m'as parlé des tableaux de Variant, je pensais initialiser les tableaux sur chaque fichier pour pouvoir mieux les utiliser, mais est-ce que c'est possible de les déclarer alors qu'il y a déjà des données avant la création du tableau ?

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Comme je dois le faire ligne par ligne, je sais qu'il faut que je passe par un For Each.

Non, une fois que le tableau est chargé en mémoire on l'explore généralement par For L = 1 To Ubound(T)

Je ne parlais que de tableaux volatile en mémoire. Rien n'est à figer dans des feuilles ou quoi que soit, pas plus que le Dictionary. Ce sont juste des outils de manipulation rapide purement en mémoire.
Pour constituer le Dictionary, passez aussi par un tableau de Variant. Il faut à tout prix éviter d'utiliser des milliers de fois les méthodes Range et Cells, ça fait toujours du code très très lent à s'exécuter.

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Donc en gros, il vaut mieux que je place les données essentielles de chaque fichier en mémoire, plutôt que de faire une exploration ligne par ligne, puis fichier par fichier ?

Voilà comment j'ai fait mon Dictionary dans le tableau d'équivalence:

Sub Auto_Open()

Dim Dico

 Set Dico = CreateObject("Scripting.dictionary")
 
    For Each c In Sheets("Sheet1").Range("B4", Range("B4").End(xlDown))
       Dico.Add c.Value, c.Offset(0, 3).Value 
    Next c

  k = Dico.keys
    i = Dico.items
End Sub

Le mieux serait de le remanier ou bien pour le tableau d'équivalence, ça peut passer ?

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Pas bon. Votre Dico est une variable locale. Il ne sera pas conservé pour d'autre procédures. Le mieux c'est de le constituer dans la procédure où vous en aurez besoin.
Plus rapide à constituer avec un tableu de Variant T :

Dim Dico As Dictionary ' Implique référence Microsooft Scripting Runtime
Dim T(), L As Long
Set Dico = New Dictionary
With Worksheets("Sheet1")
   T = .[B4].Resize(.Cells(.Rows.Count, "B").End(xlUp).Row - 3, 4).Value
   End With
For L = 1 To UBound(T)
   Dico.Add T(L, 1), T(L, 4)
   Next L

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Ce que je faisais avec mon code, c'était d'ouvrir le fichier dans la macro, et comme ça le Dictionary s'activait automatiquement à l'ouverture du fichier. Je vais tester ton code pour en voir les différences.

Donc, si je comprends ce que tu me dis, il faut que je fasse des tableaux de Variant sur chaque fichier, que je puisse accéder plus facilement aux différentes colonnes ?

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Petite question néanmoins Dranreb,

Comment fait-on avec ton code pour pouvoir sortir le deuxième code, celui sur T(L, 4) ?

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Dans la macro qui aura commencé par constituer ce Dictionary on pourra ensuite faire :
If Dico.Exists(x) then y = Dico(x), x étant en principe une valeur qui figurait en colonne B (colonne 1 de B:E) et y devant recevoir la valeur qui correspondait sur la même ligne en colonne E (colonne 4 de B:E).

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Bonjour Dranreb,

Voici le développement pour pouvoir récupérer le Ref No dans la fiche client si la quantité est supérieure à 0. J'ai un peu galéré et je ne sais pas si cela est optimisé comme il faut:

Workbooks.Open Filename:=fichierB
    ActiveWorkbook.Activate
    LastLine = Cells(Rows.Count, "A").End(xlUp).Row
    ReDim Tableau1(LastLine)
    ReDim Tableau2(LastLine, 7)
    For I = 1 To LastLine
        If (Range("F" & Trim(Str(I)))) > 0 Then
            Tableau1(I) = Range("A" & Trim(Str(I)))
        End If
    Next

Je vais rajouter la ligne que tu m'as donné, je pense que ça va grandement m'aider, je te remercie.

 

[Dranreb]

Re : Comparaison de données sur trois fichiers

Bonjour.
Non, ce n'est pas optimisé. Comme vous utilisez 2 fois Range, ça sera environ 2 * LastLine fois plus long à exécuter que Tableau1 = Range("F1").Resize(LastLine).Value même quitte à corriger ensuite par une boucle qui refait If Tableau1(I, 1) <= 0 Then Tableau1(I, 1) = Empty
Les accès aux éléments de tableaux en mémoire sont des dizaines de milliers de fois plus rapides que les méthodes Range ou Cells

 

[le_petit_xl]

Re : Comparaison de données sur trois fichiers

Je te remercie beaucoup Dranreb pour ton aide, mon code se développe petit à petit. D'ici ce soir, j'aurais terminé, je mettrais le code ici dans la journée.

Je solliciterai une nouvelle fois ton aide pour vérifier l'optimisation la plus totale possible, si tu le veux bien