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

12classeur1.xlsx (9.70 Ko)

Bonjour,

C'est un petit challenge ton affaire... J'ai établi une première partie destinée en consigner en dico les données du tableau externe. Je vais m'arrêter pour d'autres activités plus prosaïques mais j'ai été pris soudain d'un doute : je suppose que tu utilises Excel 365, ce qui n'informe pas sur le système d'exploitation, j'espère donc que tu n'es pas sur MAC, car si c'était le cas la méthode que j'ai choisie ne fonctionnerait pas. Donc question à confirmer avant que je poursuive dans la même voie.

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.
Il faudrait donc savoir ce qui doit être noté dans chaque cas ?

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 ! Dans mon code on cherche la présence de Fact ou fact dans le libellé pour la colonne facture et un libellé commençant par Montant ou montant pour la colonne Montant. Tu verras à l'usage si cela couvre au moins une majorité de cas... Si tu as un échantillonnage plus complet des libellés trouvés, on peut essayer d'en couvrir le maximum pour limiter les besoins d'intervention manuelle.

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 :

  • pas OK parce que divergence au niveau des montants
  • pas OK parce que ne figure pas dans l'un des deux fichier.
Il faudrait donc savoir ce qui doit être noté dans chaque cas ?

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

OK !

@+

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 ! On en discutera le cas échéant.

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.

12clegal.zip (27.27 Ko)

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

Rechercher des sujets similaires à "comparer fichiers"