Discussion :

[DiGio]

Bonjour tout le monde,

Je travaille actuellement sur l’elaboration d’une macro en VBA sur Excel (2010) me permettant d’effectuer la vérification de données identiques.

Je reçois chaque jours deux fichiers VBA. L’un comporte une liste de paiements détaillés et l’autre comporte la même liste de paiements mais groupés.

Par exemple dans mon premier fichier, si j’ai trois paiements sous la référence 0001, je vais avoir trois lignes distinctes tandis que dans mon second fichier je vais avoir uniquement une ligne avec référence 0001 et la somme des trois paiements.

Auriez-vous des axes de réflexion pour réaliser une macro me permettant de vérifier que la somme des montants par référence soit égale dans les deux fichiers ?

Pour tout ce qui est import des fichiers etc je n’ai aucun problème. Mon problème subsiste uniquement dans la boucle et la vérification des sommes par référence.

Je vous remercie d’avance.

Jordan

[78chris]

Bonjour

J'utiliserai un TCD ou PowerQuery (en add on sur 2010, intégré à 2016)

Pour le TCD mettre les données bout à bout avec une colonne indiquant la source : détail ou Global par exemple en mettant cette colonne en étiquettes de colonne, le compte en étiquette de ligne et un calcul par différence sur montant tu auras ton résultat en quelques clics...

[DiGio]

Merci pour ta réponse.

Le fait de passer par des TCD fait donc perdre cet intérêt à l'automatisation.

J'ai un grand nombre de données (plusieurs milliers de lignes) et ce chaque jour avec création d'un module d'envoi de mails automatique détaillant les écarts rencontrés. Pour être plus précis c'est dans le cadre d'un projet en banque CIB où je dois analyser les écarts de montants annoncés à la trésorière sur différentes devises.

J'ai joint un fichier exemple pour aider à visualiser la problématique. Pour faciliter la compréhension, j'ai mis l'ensemble des données dans un même fichier. Le début de mon code actuel correspond à aller chercher automatiquement les bonnes extractions réalisées pendant la nuit (au format csv) et les mettre convenablement en forme.

La seule idée que j'aurais serait de procéder en plusieurs étapes :
- Boucler dans l'onglet extract 1 afin de définir une variable "Somme Ref" et d'obtenir la somme par référence
- Reporter cette somme dans un nouvel onglet
- Boucler dans l'onglet extract 2 et réaliser un RechercheV afin de rapporter la somme correponsdant à la référence dans ce nouvel onglet créé
- Boucler dans ce nouvel onglet pour calculer les écarts par référence et mettre en avant les références comportant des écarts.

Cependant ce code n'est absolument pas optimal et reste une solution alternative qui mettra beaucoup plus de temps à tourner qu'un code "propre".

Je reste à l'écoute d'autres solutions (éventuellement la réalisation de TCD via VBA?).

Merci à vous !

Jordan

[78chris]

RE

Le fait de passer par des TCD fait donc perdre cet intérêt à l'automatisation.

Absolument pas: on a un TCD "perpétuel" sur un classeur à part qui synthétise 2 classeurs externes dont le nom est fixe : il suffit d'actualiser chaque période.
Dans ce type de méthode on organise une arborescence de type Année puis, en sous dossier, date ou mois selon la fréquence. Chaque sous-dossier contient 3 fichiers : les 2 extraits toujours de même nom et le classeur de synthèse.

J'ai un grand nombre de données (plusieurs milliers de lignes) et ce chaque jour avec création d'un module d'envoi de mails automatique détaillant les écarts rencontrés. Pour être plus précis c'est dans le cadre d'un projet en banque CIB où je dois analyser les écarts de montants annoncés à la trésorière sur différentes devises.

Quelques milliers de lignes n'est pas insurmontable pour un TCD

La seule idée que j'aurais serait de procéder en plusieurs étapes :
- Boucler dans l'onglet extract 1 afin de définir une variable "Somme Ref" et d'obtenir la somme par référence
- Reporter cette somme dans un nouvel onglet
- Boucler dans l'onglet extract 2 et réaliser un RechercheV afin de rapporter la somme correpondant à la référence dans ce nouvel onglet créé
- Boucler dans ce nouvel onglet pour calculer les écarts par référence et mettre en avant les références comportant des écarts.

Des boucles sont loin d'être optimum de même que RECHERCEV sur les gros volumes alors que les outils tels que TCD ou PowerQuery orientés base de données le sont.

Si tu as la possibilité d'installer PowerQuery (add on sur 2010 et 2013, intégré à partir de 2016) ou à défaut d'utiliser Access, tu auras la solution la plus efficace.

Un calcul d'écart fait par PowerQuery peut être, comme pour la solution TCD, fait avec des fichiers toujours de même noms, soit on peut passer le chemin et nom de fichier sources en paramètres (paramètres passés manuellement ou par code si tu traites déjà les csv par code ou avant le code de fabrication des mails).

PowerQuery sait utiliser directement des CSV mais je n'ai jamais testé sur de gros volumes.

[DiGio]

Tout d'abord, merci Chris pour ta réactivité et ta gentillesse.

RE

Absolument pas: on a un TCD "perpétuel" sur un classeur à part qui synthétise 2 classeurs externes dont le nom est fixe : il suffit d'actualiser chaque période.
Dans ce type de méthode on organise une arborescence de type Année puis, en sous dossier, date ou mois selon la fréquence. Chaque sous-dossier contient 3 fichiers : les 2 extraits toujours de même nom et le classeur de synthèse.

J'ai un peu de mal à imaginer, n'étant pas familier avec ce type de procédé. Je vais me documenter dessus.

RE

Quelques milliers de lignes n'est pas insurmontable pour un TCD

Des boucles sont loin d'être optimum de même que RECHERCEV sur les gros volumes alors que les outils tels que TCD ou PowerQuery orientés base de données le sont.

Si tu as la possibilité d'installer PowerQuery (add on sur 2010 et 2013, intégré à partir de 2016) ou à défaut d'utiliser Access, tu auras la solution la plus efficace.

Un calcul d'écart fait par PowerQuery peut être, comme pour la solution TCD, fait avec des fichiers toujours de même noms, soit on peut passer le chemin et nom de fichier sources en paramètres (paramètres passés manuellement ou par code si tu traites déjà les csv par code ou avant le code de fabrication des mails).

PowerQuery sait utiliser directement des CSV mais je n'ai jamais testé sur de gros volumes.

Mon fichier csv arrive dans un état désastreux qui demande une remise en forme et une déconcaténation réalisée via VBA comme expliqué, d'où mon intérêt de continuer directement dans le code.

Malheureusement, l'ajout/suppression d'add-in et logiciels en tout genre sont interdits sur les postes au bureau. Je ne possède ni Access et n'aurait donc pas la possibilité d'installer PowerQuery.

L'intérêt principal du code et de permettre à "n'importe qui" d'utiliser ce code, et notamment aux personnes les moins familières avec Excel (et les TCD) de pouvoir faire tourner cette macro et donc d'envoyer les mails aux équipes de trésorier avec d'effectuer les couvertures nécessaires sur les devises concernées.

Je reste tout de même perplexe face à la complexité de ce projet. Via Access/SQL ça me semble plus simple, mais malheureusement tous les postes/utilisateurs n'ont pas les droits pour ces logiciels.

Une somme de chaque onglet par référence et une comparaison des résultats dans un troisième onglet me semblait être une tache aisée...

[78chris]

RE

Que ce soit une solution TCD ou PowerQuery ou Access elle peut être transparente pour les utilisateurs

Access peut être sur un seul poste (serveur). Si les choses sont bien faites C'est une requête Access qui réalise le calcul d'écart sur Excel, requête évoquée depuis VBA.