Comparer 2 fichiers
Bonjour à tous,
J'ai des ptits problèmes à réaliser ce que je veux faire...
Ma situation :
Je suis comptable et je dois comparer 2 comptes
Je dois comparer 2 fichiers Excel (plusieurs milliers de lignes) et trouver pourquoi il y a des différences.
J'ai 2 fichiers Excel, que j'appel Fichier 1 et Fichier 2. Pour des raisons de simplicité j'ai représenté ici sous forme d'onglet dans le fichier joint, mais il faut idéalement que le code soit fait pour des fichiers.
Ils ne sont pas identiques, c’est-à-dire que les informations ne sont pas situés dans les mêmes colonnes
Le fichier 1 à toujours la même présentation puisque c'est le mien, mais le 2eme fichier vient d'une source externe et est différent à chaque fois, que cela soit au niveau de l'emplacement des colonnes que la ligne du début du fichier,
Idéalement :
Il faut :
identifier sur le fichier 1 le numéro de facture et le montant qui est associé --> retrouver les mêmes info dans le fichier 2
Lorsque c'est fait mettre dans les 2 fichiers dans une colonne "OK" et dans une autre colonne le numéro de la ligne de l'autre fichier
Ou cela se complique un peu :
cela peut être dans un fichier ou dans l'autre :
les numéros de factures ne sont pas identique à 100%... La fin est identique mais il arrive que le début ne le soit pas (ex les n° de factures peuvent commencer par 3 ou 4 lettres dans 1 ficher et dans le 2eme cette info n'y est pas, par exemple fichier 1 ligne 5 et Fichier 2 ligne 7)
Il peut arriver qu'une facture soit enregistrée sur plusieurs lignes d'un coté mais pas de l'autre (Fichier 1 ligne 4,6 et Fichier 2 ligne 6)
Voila c'est les principaux cas.
Merci pour votre aide.
A très vite j'éspère
Christophe
Bonjour,
C'est un petit challenge ton affaire...
Sinon, compte tenu des conditions préalables que tu as indiquées, la procédure te demandera d'abord d'aller indiquer le fichier "Fichier 2" sur ton disque, elle l'ouvrira, cherchera où commencent les données, tentera de déterminer les colonnes Numéros de facture et Montant, si elle y parvient elle te demandera de confirmer ses résultats. Si tu ne confirmes pas ou si elle n'était pas parvenue à un résultat; elle te demandera de faire en sorte que l'on puisse trouver ces colonnes au niveau intitulé, soit que l'une contienne 'Facture' (ou Fact qui suffira) et l'autre débute par 'Montant', puis d'enregistrer, fermer et relancer...
On stocke donc ensuite les données dans un dico, les clés étant constituées par les numéros de factures (fonction établie pour extraire le numéro utile) et la valeur par une chaîne composée avec le numéro de ligne et le montant, et si facture en plusieurs lignes, la chaîne sera composée d'autant de numéros de ligne et montants alternés...
A ce stade je me posais la question de doubler le dico en faisant pareil pour le tableau interne (Fichier 1) puis traiter les comparaisons ou passer directement au traitement, je laisse mûrir...
Si c'est OK pas de problème pour les mentions souhaitées : OK et num ligne de l'autre fichier...
Si pas OK par contre, il y a 2 cas :
- pas OK parce que divergence au niveau des montants
- pas OK parce que ne figure pas dans l'un des deux fichier.
Cordialement.
Bonjour,
Tu dis :
les numéros de factures ne sont pas identique à 100%... La fin est identique mais il arrive que le début ne le soit pas...
comment considère-tu ces deux numéros de facture PMT121215-01
PMT121215
?
Re, Salut Theze !
En ce qui me concerne :
Function NumFact(fact As String)
Dim k%, nf
nf = Replace(fact, ".", "-")
For k = 1 To Len(nf)
Select Case Asc(Mid(nf, k, 1))
Case 48 To 57: Exit For
End Select
Next k
If k > Len(nf) Then NumFact = 0: Exit Function
If k > 1 Then nf = Right(nf, Len(nf) - k + 1)
NumFact = Val(nf)
End Function
renverra 121215 pour ces deux libellés, ce qui semble la partie (numérique) commune identifiable...
Cordialement.
Salut MFerrand !
C'est tout de même un peu bizarre ce suivit des numéros de facture !
Bonjour,
PMT121215-01 et PMT121215 : C'est simple et compliqué...
Pour faire au plus simple. Nous recevons une facture PMT121215-01 que nous enregistrons dans la comptabilité. Nous recevons une autre facture PMT121215-02 que nous enregistrons également dans la comptabilité (logique c'est 2 n° de factures différentes avec 2 montants différents). Sauf que ceux qui ont fait ces factures ont enregistré ces 2 factures sous 1 seule facture PMT121215 pour le montant total.
Le cas inverse se produit également dans le sens ou ils envoient 1 facture et eux en enregistrent 2.
Le 2eme fichier n'est pas en Français, au mieux il est en anglais au pire en Chinois.
Donc pour trouver les colonnes cela peut poser des problèmes.
Mais s'il faut que je renomme les colonnes d'une façon précise, pas de problème il faut juste me le dire.
Voila.
Si vous avez besoin de plus d'infos n'hésitez pas.
Encore merci pour voter aide
Bonjour,
Si les libellés d'en-tête sont en chinois, c'est sûr que la proc. ne les reconnaîtra pas !
Par ailleurs je te rappelle mes questions :
Si c'est OK pas de problème pour les mentions souhaitées : OK et num ligne de l'autre fichier...
Si pas OK par contre, il y a 2 cas :
Il faudrait donc savoir ce qui doit être noté dans chaque cas ?
- pas OK parce que divergence au niveau des montants
- pas OK parce que ne figure pas dans l'un des deux fichier.
Tes réponses conditionnent un peu la façon dont je vais poursuivre...
Cordialement.
re
Si c'est OK pas de problème pour les mentions souhaitées : OK et num ligne de l'autre fichier... PARFAIT
Si pas OK par contre, il y a 2 cas :
- pas OK parce que divergence au niveau des montants : MONTANT DIFFERENT
- pas OK parce que ne figure pas dans l'un des deux fichier. : Ne rien mettre
Merci beaucoup
Bonjour,
Quelques explications rapides, car je suis un peu pressé, si besoin je pourrais fournir plus de détails, mais pas avant ce soir...
4 procédures composent le programme, une seule est à lancer, je ne l'ai pas raccordée à un bouton, tu peux le faire si tu le souhaites ou la lancer à partir de la boîte de dialogue Macro (ou lui affecter un raccourci clavier...) Le code est dans ton Fichier1, si tu envisages de le placer dans un autre classeur, il faudra modifier la référence à ThisWorkbook dans l'une des procédures car elle ne collerait plus !
Le programme comprend une fonction destinée à renvoyer le numéro de facture, partie commune de la mention de la facture dans les deux fichiers.
Function NumFact(ByVal fact As String)
Dim k%, nf
nf = Replace(fact, ".", "-")
For k = 1 To Len(nf)
Select Case Asc(Mid(nf, k, 1))
Case 48 To 57: Exit For
End Select
Next k
If k > Len(nf) Then NumFact = 0: Exit Function
If k > 1 Then nf = Right(nf, Len(nf) - k + 1)
NumFact = Val(nf)
End Function
La fonction recherche le premier caractère numérique du libellé de facture, élimine de ce libellé les caractères qui précèdent et utilise la fonction Val pour extraire la partie numérique commune.
3 variables sont déclarées niveau Module (car plusieurs procédures les utiliseront) :
Dim d As Object, PlgExt As Range, kFact As Integer
d est le dictionnaire que l'on constitue pour opérer les comparaisons, il est constitué par les procédures de traitement Fichier2 et Fichier1 pour les données respectives de chacun des fichiers, utilisé par la proc de traitement Fichier1 pour les comparaison, et par la proc. de finalisation pour achever le traitement de Fichier2.
PlgExt est la plage de données de Fichier2 et kFact la colonne de cette plage où trouver les factures : initialisées et utilisées par la proc. de traitement Fichier2, utilisées par la proc. de finalisation.
La procédure de finalisation est celle qui doit être lancée, elle appelle d'abord une procédure de traitement du Fichier2 :
Sub TraitementFichierExt(Optional msq As Boolean)
Dim Fich, TbExt, nf, LnMt, kMt%, dLn%, i&, msg$
Fich = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx")
With Workbooks.Open(Fich).Worksheets(1)
If IsEmpty(.Range("A1")) Then
Set PlgExt = .Cells.Find("*")
End If
If Not PlgExt Is Nothing Then
Set PlgExt = PlgExt.CurrentRegion
Else
Set PlgExt = .Range("A1").CurrentRegion
End If
End With
TbExt = PlgExt.Value: dLn = PlgExt.Row - 1
For i = 1 To UBound(TbExt, 2)
If TbExt(1, i) Like "*[Ff]act*" Then kFact = i: Exit For
Next i
For i = 1 To UBound(TbExt, 2)
If TbExt(1, i) Like "[Mm]ontant*" Then kMt = i: Exit For
Next i
If kFact > 0 And kMt > 0 And kFact <> kMt Then
i = MsgBox("Voulez-vous confirmer que les Numéros de factures sont en colonne " _
& kFact & ", et que les montants sont en colonne " & kMt & " du tableau ?", _
vbYesNo + vbQuestion, "Confirmation de positionnement dans le tableau externe")
Else
msg = "Les colonnes Numéros de factures et Montant n'ont pu être identifiées." & Chr(10)
i = vbNo
End If
If i = vbNo Then
msg = msg & "Faîtes en sorte que la colonne Numéros de factures comporte le terme " _
& "'Facture' dans son intitulé et que l'intitulé de la colonne Montant débute par " _
& "'Montant." & Chr(10) & "Puis enregistrer et fermer le fichier, relancer la " _
& "procédure."
Else
msg = "Le traitement va se poursuivre. Patienter..."
End If
MsgBox msg, vbInformation, "Traitement des données externes"
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(TbExt, 1)
nf = NumFact(TbExt(i, kFact)) & "E"
If d.exists(nf) Then
LnMt = d(nf) & ";" & i + dLn & ";" & TbExt(i, kMt)
Else
LnMt = i + dLn & ";" & TbExt(i, kMt)
End If
d(nf) = LnMt
Next i
End Sub
La première partie de la proc. fait ouvrir le fichier par l'utilisateur, détecte la plage de données, opère une détection des colonnes facture et montant, en demande confirmation. Si OK elle se poursuivra, sinon demande à l'utilisateur de rendre les colonnes voulues détectables...
Ainsi que je l'ai déjà indiqué, on peut le cas échéant tenter de l'améliorer en disposant d'un échantillonnage des libellés d'en-tête de ce fichier externe.
La 2e partie, débutant à Set d = CreateObject("Scripting.Dictionary")
constitue un dictionnaire à partir des données où chaque élément, identifié par le numéro de facture suivi de "E", conserve numéros de lignes dans la feuille et montants.
La procédure appelante appelle ensuite une proc. de traitement du Fichier1 :
Sub TraitementFichierInt(Optional msq As Boolean)
Dim TbInt, nf, LnMt, nfE, LnMtE, i&, mt@, mtE@, j%, ln$, lnE$
Dim PlgInt As Range, k%
Set PlgInt = ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion
TbInt = PlgInt.Value
For i = 2 To UBound(TbInt, 1)
nf = NumFact(TbInt(i, 2)) & "I"
If d.exists(nf) Then
LnMt = d(nf) & ";" & i & ";" & TbInt(i, 4)
Else
LnMt = i & ";" & TbInt(i, 4)
End If
d(nf) = LnMt
Next i
Application.ScreenUpdating = False
k = PlgInt.Columns.Count + 1
For i = 2 To UBound(TbInt, 1)
nf = NumFact(TbInt(i, 2)) & "I"
nfE = nf: Mid(nfE, Len(nfE), 1) = "E"
LnMt = Split(d(nf), ";")
If LnMt(0) = "OK" Or LnMt(0) = "diff. Mt" Then
PlgInt.Cells(i, k).Resize(, 2).Value = LnMt
GoTo multiFact
End If
For j = 1 To UBound(LnMt) Step 2
mt = mt + CCur(LnMt(j)): ln = ln & " " & LnMt(j - 1)
Next j
If d.exists(nfE) Then
LnMtE = Split(d(nfE), ";")
For j = 1 To UBound(LnMtE) Step 2
mtE = mtE + CCur(LnMtE(j))
lnE = lnE & " " & LnMtE(j - 1)
Next j
If mtE = mt Then
LnMtE = "OK;" & ln: LnMt = "OK;" & lnE
Else
LnMtE = "diff. Mt;" & ln: LnMt = "diff. Mt;" & lnE
End If
d(nfE) = LnMtE: d(nf) = LnMt
PlgInt.Cells(i, k).Resize(, 2).Value = Split(LnMt, ";")
End If
mt = 0: mtE = 0: ln = "": lnE = ""
multiFact:
Next i
End Sub
Dans sa première partie cette procédure constitue les éléments de dictionnaire à partir des données du fichier dans les même conditions que la précédente pour le fichier précédent. Les éléments sont identifiés de même par le numéro de facture, mais ici suivi de "I".
La 2e partie, qui débute à Application.ScreenUpdating = False
, redéfile les lignes du fichier pour rappeler l'élément dico "I" créé pour la ligne, elle recherche un élément dico "E" correspondant, extrait les montants (en les additionnant si plusieurs) et les lignes (en concaténant si plusieurs de chaque élément, et procède à la comparaison des montants, si concordance des montants, elle remplace le contenu des éléments dico par "OK" et les numéros de lignes de l'autre fichier, si non concordance, ce sera "diff. Mt" et numéros de lignes... Elle sert le Fichier1 en affectant ces mentions dans la foulée (NB-Leur mémorisation dans l'élément dico demeure nécessaire car en cas de multi-facture, elle ne refait pas de comparaison mais reprend les résultats de la comparaison déjà effectuée. Si l'élément "E" n'existe pas, aucune mention n'est portée.
La procédure lancée au départ va assurée la finalisation de l'opération :
Sub Traitement()
Dim TbExt, nf, LnMt, i&, k%
TraitementFichierExt
TraitementFichierInt
TbExt = PlgExt.Value
k = PlgExt.Columns.Count + 1
Application.ScreenUpdating = False
For i = 2 To UBound(TbExt, 1)
nf = NumFact(TbExt(i, kFact)) & "E"
LnMt = Split(d(nf), ";")
If LnMt(0) = "OK" Or LnMt(0) = "diff. Mt" Then
PlgExt.Cells(i, k).Resize(, 2).Value = LnMt
End If
Next i
d.RemoveAll: Set d = Nothing
Set PlgExt = Nothing: kFact = 0
End Sub
Après appel des 2 proc. examinées ci-dessus, le traitement du Fichier1 est achevé, et elle va faire de même pour le Fichier2 en en redéfilant les lignes et rappelant les éléments de dico "E" correspondant à chacune, si l'élément comporte le résultat de la comparaison réalisée, il est affecté, sinon aucune mention n'est portée.
La proc. se conlut en vidant les variables module de leur contenu afin de libérer la mémoire.
Cordialement.
Bonjour,
Merci pour ta réponse / solution
Demain je suis en formation, je regarde mercredi sans faute.
Bonjour,
J'ai testé ta solution.
Elle fonctionne bien dans les fichiers tests que tu as envoyé, mais pas dans mes fichiers.
Il n'arrive pas à identifier les colonnes.
Je regarde demain si je peux et te redis ça.
Merci
Christophe
S'il n'y a que les colonnes, tu devrais pouvoir reproduire les critères de reconnaissance que j'ai indiqué.
D'autre part, avec un échantillonnage de libellés, on peut tenter d'élargir les critères de reconnaissance.
Si autre chose intervient, il faut savoir quoi, je ne peux travailler que sur les indications fournies...
Demain je serai certainement peu présent...
Bonsoir,
En déplaçant mes colonnes, cela va mieux, dans le sens où les colonnes sont identifiées.
Mais il bug sur la ligne
mt = mt + CCur(LnMt(j))
Vers la fin de : Sub TraitementFichierInt(Optional msq As Boolean)
Je ne sais pas pourquoi.
As-tu une idée ?
Christophe