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.
 

Fichiers joints

vgendron

XLDnaute Barbatruc
Hello
voir proposition en PJ
note: la formule colonne G de la feuille Global est effacée en cours de macro.. on peut la remettre si besoin..
 

Fichiers joints

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
 

Fichiers joints

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 -).
 

Fichiers joints

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
 

Fichiers joints

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
 

JoyceD

XLDnaute Nouveau
Merci Vgendron :)

effectivement c'est une macro que je vais forcément utiliser

Si tu pouvais finaliser ma dernière demande tu serais un chef :)

encore merci
 

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
 

Fichiers joints

Dernière édition:

JoyceD

XLDnaute Nouveau
Merci Vgendron par contre le calcul des 28 ne ce fait pas via la colonne Y (onglet détail) en toute logique les colonnes 28 n'ont pas d'écart
 

zebanx

XLDnaute Accro
Bonjour à Vgendron, Joyce D, le forum

Ci-joint un autre tableau en repartant du code de VGENDRON de départ ( ;)) et j'ai perdu du temps donc pas prise en compte de toutes les modifications passées par lui.

De ma compréhension, la difficulté est que chaque ligne dans le tableau détail comprend à la fois le montant de départ dans un code spécifique d'immobilisations (code 21 à 27) pour le montant brut de l'immobilisation (colonne H), puis le montant de l'amortissement réalisé, de l'année et en colonne 25 (= Y) le montant total des amortissements. Le tout, en valeur absolue.

Les modifications apportées :

1/ On insère dans le tableau détail une autre "clé" qui permet d'avoir un compte correspondant à chaque compte d'immobilisation (colonne AA = 27) un compte d'amortissement (colonne Z = 26).

2/ On insère dans le code de VGENDRON un STXT qui, dans le tableau global vient rechercher une valeur "28" sur la colonne G à chaque changement de ligne :
Si il trouve le 28, il se réfère à la colonne 26 de "détail" et vient prendre une valeur négative dans le tableau détail (puisque dans le détail, tout est présenté en valeur absolue) qui correspond à la colonne 25 (amortissements)
Si il ne trouve pas 28, il se réfère à la colonne 27 (AA) de "détail" et vient prendre une valeur positive qui correspond à la colonne 8 (montant de l'investissement)

On enregistre dans une table spécifique (a) et on restitue en colonne I dans l'onglet tabglobal.
Il reste des écarts mais on voit en bout de tableau que des montants s'annullent entre investissements et amortissements.

Désolé d'avoir été long, peut-être à côté de la plaque par rapport à la demande de JoyceD.
J'ai travaillé en parallèle, tu m'en excuseras VGENDRON sur cette version alternative mais qui reprenait ton code de départ.

@+
zebanx
 

Fichiers joints

vgendron

XLDnaute Barbatruc
euh.. la. je t'avoue que j'ai du mal à suivre..
un coup, c'est le ctrlclé 1 en colonne Z ensuite c'est la clé 2 en X puis ca devient le ctrlClé2 en AA...
comme je te le disais précédemment, je trouve qu'il y a beaucoup de clés qui me semblent inutiles.
par exemple: feuille Detail..
les colonnes CtrlClé1 et CtrlClé2 (colonne Z et AA) apparemment, c'est juste la clé 1 et clé 2 (Colonnes W et X) avec un "0" ajouté à la fin..
pourquoi refaire une formule à base de recherche??==> il me semble que ca alourdit inutilement le fichier.

Hello @zebanx
aucun souci: au contraire. tu sembles avoir compris la logique du fichier.. .. moi.. pas encore :-D
 

JoyceD

XLDnaute Nouveau
Bon !
je ne suis pas contente alors pas du tout…..

c'est juste parfait une fusion entre zebanx et vgendron c'est juste incroyable.

Je vous assure que sans la barrière d'un écran il y'aurait eu champagne pour tous

je me suis permise de vous écrire ne privée afin de vous remercier.

Ps : comment survie le site web? car je ne vois pas la possibilité de contribuer pécuniairement par un système de don ….
 

JoyceD

XLDnaute Nouveau
euh.. la. je t'avoue que j'ai du mal à suivre..
un coup, c'est le ctrlclé 1 en colonne Z ensuite c'est la clé 2 en X puis ca devient le ctrlClé2 en AA...
comme je te le disais précédemment, je trouve qu'il y a beaucoup de clés qui me semblent inutiles.
par exemple: feuille Detail..
les colonnes CtrlClé1 et CtrlClé2 (colonne Z et AA) apparemment, c'est juste la clé 1 et clé 2 (Colonnes W et X) avec un "0" ajouté à la fin..
pourquoi refaire une formule à base de recherche??==> il me semble que ca alourdit inutilement le fichier.

Hello @zebanx
aucun souci: au contraire. tu sembles avoir compris la logique du fichier.. .. moi.. pas encore :-D
pour être honnête l'explication de zebanx moi même j'ai du mal à la suivre je pense que dans son explication il se mélange les pinceaux mais au niveau du fichier c'est un bon rendu je fais actuellement un contrôle dessus

je viens de voir que la clé 2 est en colonne Z et la 1 en colonne AA c'est pour cela vgendron, Zebanx à changer l'ordre d'où nos deux incompréhension

il y'a beaucoup de clé qui ne servent pas ou plus du tout seul les clés des colonnes sont utile

Z = cle 1
AA cle 2


sinon chez zebanx lui a changé le sens

Z = clé 2
AA clé 1

concernant l'alourdissement du fichier c'est du surtout à mon incompétence car il s'agit effectivement d'un zéro en plus car la transcodification à ajouter un zero…..
 

zebanx

XLDnaute Accro
Re-

J'ai effectivement été obligé de rajouter un "0" en colonne Z (de mon fichier) pour faire "matcher" avec la colonne G de tabglobal.
C'est pas clean...bien d'accord.

Après, je ne voyais pas trop comment faire autrement que décrit en #16.
Si on isole "28" (par MID...) sur la ligne en colonne G de table global -> c'est une recherche d'amortissement donc clé = colonne Z(26) et item = colonne Y (25) sur détail avec un chiffre négatif
Si on a une valeur <> "28" sur la ligne en colonne G de table global -> c'est une recherche d'immobilisation donc clé = colonne AA(27) et item = colonne H (8) sur détail avec un chiffre positif

La difficulté (pour moi) c'est que le tableaudétail contient des données de deux comptes (immobilisation + amortissement correspondant) et que le tableau global éclate bien ces données.
Plus "parlant" avec des couleurs sur le même tableau (sans modifications de code / #16).

@+

@Joyce : # post 18 :D.
Sinon, Pierre-Jean, Vgendron, Job75... seraient bien riches ... et plus ici ("Au revoir président...").
Un "merci" suffit donc à ces génies du code.
 

Fichiers joints

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas