Macro comparaison onglet

JoyceD

XLDnaute Nouveau
Bonjour à tous,

Apres plusieurs jours de réflexion (si si ) j'ai une problématique que je souhaite partager.

dans un même fichier j'ai deux onglets

onglet détail et onglet global

Dans l'onglet global il y'a une somme global qui devrait être en total adéquation avec l'onglet détail via une clé de contrôle qui se nomme Ctrl Clé 1 (onglet détail) et clé s dans l'onglet global

Un membre pourrait me faire une macro qui permet de crée un onglet donne les écarts de chaque clé de contrôle?

ps : des écarts existe mais je ne souhaite pas les corriger uniquement les mettre en lumière.

j'ai mis en évidence un filtre juste pour vous montrez un exemple et les colonnes concernant le contrôle montant

Merci à tous d'avance.

J.
 

Pièces jointes

  • excel.xlsx
    713 KB · Affichages: 46

JoyceD

XLDnaute Nouveau
Hello Vgendron,

encore merci pour ce super travail.

je t'embête encore un peu est-il possible d'avoir une colonne supplémentaire avec la différence en écart.

Concernant la formule je suppose qu'elle ne peut rester en fixe je vais crée une colonne jumelle je pense pour l'avoir en fixe

encore merci d'avoir compris et mis en pratique ma demande aussi rapidement

Joyce.
 

vgendron

XLDnaute Barbatruc
pour la formule, comme je te le disais (ou pas), on peut la remettre ==> chose faite dans la PJ
pour l'écart. pas besoin de colonne supplémentaire. tu peux le mettre directement dans la colonne I: voir formule
 

Pièces jointes

  • excel.xlsm
    786.9 KB · Affichages: 44

zebanx

XLDnaute Accro
Bonjour Vgendron, JoyceD, le forum

Si la base "global" est complète (hypothèse retenue pour le comparatif*), une sommeprod ou même une somme.si font aussi le travail pour info.
Ceci alourdit toutefois bien trop le fichier (mais pas trop lent dans le cas) par rapport à la solution proposée supra.

@+

* après contrôle (par TCD), ce n'est pas le cas.
Il y a beaucoup plus de compte dans la base détail non repris dans la base global donc il doit s'agir d'une extraction ciblée -).
 

Pièces jointes

  • fichier_ecarts.xls
    2.5 MB · Affichages: 30
Dernière édition:

JoyceD

XLDnaute Nouveau
Merci Zebanx et vgendron d'avoir pris le temps de me répondre

en réalité le fichier fait plus de 30 000 lignes, il y'a bien des comptes concernant l'onglet détail qui n'existe plus dans Global. suite à migration il y'a eu une sorte de transcodage exemple le compte 215400 devient dans global le compte 215000 mais cela nécessite un onglet transcodage je suppose que cela complique beaucoup trop la macro ou notre tableau tcd je me trompe?

Encore merci pour vos retours sincèrement très surpris d'avoir eu des retours de ce niveau

Joyce
 

zebanx

XLDnaute Accro
Re-bonsoir

La transcodification permet effectivement d'avoir une exhaustivité complète mais pour la création des clés de transco", c'est de votre côté qu'il faut le faire...

Bon, pour cet exemple, sur une stricte exhaustivité, vous trouverez dans le fichier ci-joint un TCD.
Pour comparer les deux bases :
- colonne 1 : nom de chaque BDD de départ
- colonne 2 : compte recherché sur chaque onglet qui devraient "matcher"
- colonne 3 : montant.
Pour obtenir les écarts, je multiplie les valeurs d'une des BDD par -1.
Ensuite un TCD permet facilement d'obenir l'exhaustivité des clés (ordonnées) et les valeurs pour chaque base et les différences.

Et bienvenu sur le forum. J'aurais dû commencer par cela !
Oh oui, il y a de quoi faire et des réponses pertinentes tant sur les formules que le VBA.

xl-ment
 

Pièces jointes

  • fichier_ecarts.xls
    3 MB · Affichages: 28

JoyceD

XLDnaute Nouveau
Merci Zebanx pour ce nouveau fichier, je suis actuellement sur mes clés de transcodage onglet détail colonne U du bon boulot en perspective :)

vous m'avez sortie d'une bonne galère en très peu de temps mille mercis hâte de vous faire part rapidement de l'évolution de mon (notre) fichier

je vous souhaites une excellente soirée

ps: ma petite galère me permet de connaitre ce forum et surtout de savoir que vous êtes la pour chacun de nous j'espère pouvoir rapidement y contribuer

ps: je te pique ton xl-ment ^^
 
Dernière édition:

JoyceD

XLDnaute Nouveau
Bonjour à tous,

Dans l'euphorie de la résolution de mon fichier une chose ne fut pas dite (je suis en faute) .

Est-il possible d'utiliser la clé 2 (onglet détail) quand un compte commence par 28 ?

pour les 28 la colonne des montants à prendre est la Y (onglet detail)

Merci Vgendron et Zebanx pour votre temps

Joyce.
 
Dernière édition:

vgendron

XLDnaute Barbatruc
Hello

J'avais commencé une macro ou deux pour ne plus avoir de formule dans tes feuilles "Sociétés Immos" et "Etablissements Sage"
ca peut peut etre servir

VB:
Sub ExtraireNumClient() 'pour éviter la formule dans la colonne A de la feuille Etablissement Sage
'permet d'extraire le numéro de client (quand il existe)
Dim TabEta() As Variant
With Sheets("Etablissements Sage")
    TabEta = .UsedRange.Value
    For i = LBound(TabEta, 1) + 1 To UBound(TabEta, 1)
        If InStr(1, TabEta(i, 3), "-") <> 0 Then
            'NumClientDansC = (Split(TabEta(i, 3), "-")(1))
            'On Error Resume Next
            TabEta(i, 1) = IIf(IsNumeric(Split(TabEta(i, 3), "-")(1)), Split(TabEta(i, 3), "-")(1), IIf(IsNumeric(Split(TabEta(i, 4), "-")(1)), Split(TabEta(i, 4), "-")(1), "Pas de Num Client"))
        Else
            TabEta(i, 1) = IIf(IsNumeric(Split(TabEta(i, 4), "-")(1)), Split(TabEta(i, 4), "-")(1), "Pas de Num Client")
        End If
    Next i
    .UsedRange = TabEta
End With
End Sub

Sub SociétéImmos()
Dim TabEta() As Variant
Dim TabImmos() As Variant

Set dico = CreateObject("scripting.dictionary")
With Sheets("Etablissements Sage")
    TabEta = .UsedRange.Value
End With
With Sheets("Sociétés Immos")
    fin = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("F2:G" & fin).ClearContents
    TabImmos = .Range("A2:G" & fin).Value
End With


For i = LBound(TabEta, 1) + 1 To UBound(TabEta, 1)
    If Not dico.exists(TabEta(i, 1)) Then
        dico.Add TabEta(i, 1), TabEta(i, 2) & "***" & TabEta(i, 4)
    End If
Next i

For i = LBound(TabImmos, 1) To UBound(TabImmos, 1)
    If dico.exists(TabImmos(i, 4)) Then
        TabImmos(i, 6) = Split(dico(TabImmos(i, 4)), "***")(0)
        TabImmos(i, 7) = Split(dico(TabImmos(i, 4)), "***")(1)
    End If
Next i
With Sheets("Sociétés Immos")
    .Range("A2:G" & fin) = TabImmos
End With
End Sub
 

vgendron

XLDnaute Barbatruc
comme je ne suis pas sur de quelle clé il faut comparer avec quoi.. (il y a beaucoup de clés différentes très similaires)
voir code avec les commentaires qui devraient te permettre d'adapter en cas de besoin
VB:
Sub Compare()
Dim TabDetail() As Variant
Dim TabGlobal() As Variant
With Sheets("Detail")
    TabDetail = .UsedRange.Value 'on place tout le tablo Detail dans un tablo VBA
End With
With Sheets("Global")
    fin = .Range("A" & .Rows.Count).End(xlUp).Row 'dernière ligne de la feuille Global
    .Range("H2:H" & fin).ClearContents 'on efface la colonne H
    TabGlobal = .Range("A2:H" & fin).Value 'on met tout dans un tablo VBA
End With

For i = LBound(TabGlobal, 1) To UBound(TabGlobal, 1) 'pour chaque ligne du TabloGlobal
    For j = LBound(TabDetail, 1) To UBound(TabDetail, 1) 'on parcourt le tablo Detail
        If TabGlobal(i, 4) Like "28" & "*" Then 'si le compte colonne D commence par 28
            If TabDetail(j, 24) = TabGlobal(i, 7) Then 'si la clé 2 detail = clé S de global
                TabGlobal(i, 8) = TabGlobal(i, 8) + TabDetail(j, 8)
            End If
           
        Else 'ne commence pas par 28
                If TabDetail(j, 26) = TabGlobal(i, 7) Then 'si la clé "Ctrl Cl1" (colonne Z) de Detail = Clé S de Global
                    TabGlobal(i, 8) = TabGlobal(i, 8) + TabDetail(j, 8)
                End If
        End If
    Next j
Next i
   
With Sheets("Global")
    formuleG = "=B2&""/""&D2"
    .Range("A2:H" & fin).Value = TabGlobal
    .Range("G2").FormulaLocal = formuleG
    .Range("G2:G" & fin).FillDown
End With
   
End Sub
 

JoyceD

XLDnaute Nouveau
Merci pour ton retour Vgendron

Voici mon fichier final concernant la macro je m'y perd malgré ton détail après promis je ne t'embête plus
en jaune les colonnes nécessaires
la clé a utiliser dans l'onglet détail (colonne AA) pour compléter dans l'onglet global le montant des lignes qui commence par 28
Dans l'onglet détail la colonne (Y) sert au calcule des 28


Merci
 

Pièces jointes

  • final.xlsm
    769.6 KB · Affichages: 28
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 724
Messages
2 081 937
Membres
101 844
dernier inscrit
pktla