Macro de vérification de données identiques par référence

Version imprimable

06/08/2018, 16h16
DiGio

Macro de vérification de données identiques par référence

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
06/08/2018, 18h32
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...
06/08/2018, 22h49
DiGio

1 pièce(s) jointe(s)

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
06/08/2018, 23h52
78chris

1 pièce(s) jointe(s)

RE

Citation:

Envoyé par DiGio

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.

Citation:

Envoyé par DiGio

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

Citation:

Envoyé par DiGio

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.
07/08/2018, 00h07
DiGio

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

Citation:

Envoyé par 78chris

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.

Citation:

Envoyé par 78chris

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...
07/08/2018, 08h25
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.
07/08/2018, 09h06
DiGio

Citation:

Envoyé par 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.

Merci pour ta réponse une fois de plus Chris. Malheureusement les infrastructures ne sont pas autant ouvertes dans le système bancaire et nous avons énormément de restriction pour les requêtes.

Une fois de plus, ma volonté de passer par VBA est d'avoir uniquement un bouton à cliquer sur une feuille de calcul et que tout se fasse (extraction, transformation, calcul et envoi de mail) en quelques secondes. Si je privilégie le VBA c'est avant tout pour cela. A mon grand étonnement également très peu de personnes sont familières avec Access/Excel et encore moins avec les TCD. Je ne doute pas de la transparence des logiciels, bien au contraire, mais pour faire se genre de requête sur un poste ça doit passer par plusieurs phases de validation qui ne sont pas toujours acceptée et ma requête urge donc je ne peux pas me permettre ce genre de chose.

Une solution full VBA n'est donc pas envisageable ? Je comprends ton "entêtement" à passer par tes solutions, qui sont je te l'accorde bien meilleures que ce que je propose, mais je n'en ai malheureusement pas la possibilité.

Bien à toi,

Jordan

Bonjour
Je crois que si tu ajoute dans la feuille 2 une colonne pour la somme devant chaque code les détails associés et une colonne pour les différences

voila un code à tester
certain que y a mille fois meilleur que ça mais c'est dans la limite de mes connaissance :aie:
Bonne journée à vous tous
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Sub test() Dim derligne As Long Dim j As Integer tablo = Sheets("Extract 1").Range("A2", "B" & Sheets("Extract 1").Range("B" & Sheets("Extract 1").Rows.Count).End(xlUp).Row) With Sheets("Extract 2") derligne = .Range("A" & Rows.Count).End(xlUp).Row For j = 2 To derligne x = 0 For i = LBound(tablo, 1) To UBound(tablo, 1) If .Cells(j, 1) = tablo(i, 1) Then x = x + CDbl(tablo(i, 2)) End If Next i .Cells(j, 3) = x .Cells(j, 4) = .Cells(j, 3) - .Cells(j, 2) .Cells(j, 3).NumberFormat = "#,##0.00 $" .Cells(j, 4).NumberFormat = "#,##0.00 $" Next j End With End Sub

07/08/2018, 14h27
78chris

re

Citation:

Envoyé par DiGio

...
Une fois de plus, ma volonté de passer par VBA est d'avoir uniquement un bouton à cliquer sur une feuille de calcul et que tout se fasse (extraction, transformation, calcul et envoi de mail) en quelques secondes. Si je privilégie le VBA c'est avant tout pour cela...

L'idée n'était pas de supprimer tout le VBA mais d'éviter sur la partie comparaison tant les des boucles que les RECHERCHEV qui rament tous deux sur de gros volumes pour utiliser des outils de BD qui eux savent faire.

Pour les requêtes c'était sur tes 2 classeurs Excel pas sur le système métier ni le datacenter...

Je remballe ma marchandise, lol, et te souhaite bonne continuation.
07/08/2018, 15h29
crica

les bons outils pour les bonnes solutions....

Bonjour,

M'est d'avis que si 78chris propose du TCD ou PowerQuery c'est que dans ce cas de figure c'est l'outil le plus rapide et le plus efficace pour faire ce job. Ok PowerQuery requiert une installation et dans les banques, c'est pas top, mais les tableaux croisés dynamiques, il sont par défaut disponibles dans Excel et je les utilises depuis 20 ans maintenant avec des résultats exceptionnels. J'ai encore pas connu un truc aussi top sans installation supplémentaire et sans se casser la tête. C'est de la BI avant la BI et sans SQL, bravo Microsoft.

Par ailleurs, pour une solution "one clic with 100% VBA", il est possible de mettre à jour le/les TCD/s via le code.

Et si les connexions ODBC sont possibles, il est également possible de faire pointer le TCD sur un fichier externe (csv) et en activant l'option TCD "mise à jour lors de l'ouverture du fichier".

L'avantage de la proposition de 78chris c'est de tirer parti du meilleur de chaque outil.

Hope this helps,
CR

1 pièce(s) jointe(s)

Bonsoir à tous, je me permets de remonter le messages après quelques temps d'inactivité.

J'ai donc suivi vos recommandations et j'ai donc effectué - entre-autre - la création de deux TCD issues de deux tableaux distincts présents sur deux feuilles différentes.

Le code ci-dessous me permets d'aller chercher mes données dans chacune de ces deux feuilles pour ensuite construire mes deux TCD.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
Sub InsertPivotTableMast ()
‘Declare Variables
Dim PSheet As Worksheet
Dim DSheetMast as Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PrangeMast As Range
Dim LastRowMast As Long
Dim LastColMast As Long
 
	‘Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets (“PivotTable”).Delete
Sheets.Add After:=Worksheets (“Matisse”)
ActiveSheet.Name = “PivotTable”
Application.DisplayAlerts = True
Set PSheet = Worksheets (“PivotTable”)
Set DSheetMast = Worksheets (“Mast”)
 
‘Define Data Range
LastRowMast = DSheetMast.Cells (Rows.Count, 1).End (xlUp).Row
LastRowMast = DSheetMast.Cells (1, Columns.Count).End(xlToLeft).Column
Set PRangeMast = DSheetMast.Cells (1, 1).Resize (LastRowMast, LastColMast)
 
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
 (SourceType:=xlDatabase, SourceData:=PRangeMast). _
CreatePivotTable (TableDestination:=PSheet.Cells (1, 1), _
TableName:=”MastPivotTable”)
 
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells (1, 1), TableName:=”MastPivotTable”)
 
‘Insert Row Fields
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“ValueDate”)
	.Orientation = xlRowField
	.Position = 1
End With
 
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“M_CURRENCY”)
	.Orientation = wlRowField
	.Position = 2
End With
 
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“OriginalRef”)
	.Orientation = wlRowField
	.Position = 3
End With
 
‘Insert Data Field
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields “Amount”)
	.Orientation = xlDataField
	.Function = xlSum
	.NumberFormat = “#,##0.00”
	.Name =”AmountSum”
End With
 
End Sub
 
Sub InsertPivotTableMatisse ()
‘Declare Variables
Dim PSheet As Worksheet
Dim DSheetMatisse as Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PrangeMatisse As Range
Dim LastRowMatisse As Long
Dim LastColMatisse As Long
 
‘Insert a New Blank Worksheet
ActiveSheet.Name = “PivotTable”
Application.DisplayAlerts = True
Set PSheet = Worksheets (“PivotTable”)
Set DSheetMatisse = Worksheets (“Matisse”)
 
‘Define Data Range
LastRowMatisse = DSheetMatisse.Cells (Rows.Count, 1).End (xlUp).Row
LastRowMatisse = DSheetMatisse.Cells (1, Columns.Count).End(xlToLeft).Column
Set PRangeMatisse = DSheetMatisse.Cells (1, 1).Resize (LastRowMatisse, LastColMatisse)
 
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
 (SourceType:=xlDatabase, SourceData:=PRangeMatisse). _
CreatePivotTable (TableDestination:=PSheet.Cells (1, 4), _
TableName:=” MatissePivotTable”)
 
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells (1, 1), TableName:=” MatissePivotTable”)
 
‘Insert Row Fields
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“Theorical value date”)
	.Orientation = xlRowField
	.Position = 1
End With
 
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“Currency”)
	.Orientation = wlRowField
	.Position = 2
End With
 
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“External ref. 1”)
	.Orientation = wlRowField
	.Position = 3
End With
 
‘Insert Data Field
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields “Amount”)
	.Orientation = xlDataField
	.Function = xlSum
	.NumberFormat = “#,##0.00”
	.Name =”AmountSum”
End With
 
End Sub

Ils me donnent donc le résultat suivant :

https://image.noelshack.com/minis/20...4-img-4248.png

Cependant je bloque sur la manière de comparer les résultats entre-eux (via VBA).

En effect comme expliqué plus haut, je dois trouver les différences par références mais j'ai du mal à imaginer comment construire cette requête.

Par exemple, j'aurais besoin d'avoir (peut-être sur une nouvelle feuille de calcul) l'écart entouré sur l'image sous la référence 19802682.

Pouvez-vous m'éclairer ?

Je vous remercie d'avance.

Bien à vous,

Jordan

09/09/2018, 22h34
78chris

Bonjour

Relis mes posts #2 et #4

Dans le premier, je conseillais un seul TCD et non deux, ce qui permet d'intégrer le calcul d'écart DANS le TCD

et dans le suivant une solution pour ne pas réinventer tout à chaque fois : un TCD fait sans VBA sur des fichiers externes. TCD qu'il suffit d'actualiser et non de reconstruire...
09/09/2018, 22h49
DiGio

Je n'ai malheureusement pas trouvé le moyen à travers mon code de mettre les données de mes deux feuilles au sein d'un même TCD.

D'autant plus que je dois trouver le moyen de filtrer, à travers le code uniquement les devises EUR, GBP, CAD, USD et CHF (dans le cas ou c'est supérieur à 50M) pour la date 20180903 et ensuite toutes les autres pour la date 20180904.

Peux-tu m'éclairer sur comment modifier mon code afin de créer un seul et même TCD pour des données présentes dans deux feuilles distinctes ?
De plus as-tu une idée de comment effectuer les filles sus-mentionnés ?
09/09/2018, 22h55
78chris

RE

Il faut mettre les données bout à bout en indiquant la source dans une colonne supplémentaire, ceci de façon virtuelle avec une requête Msquery ou POwerQuery, ou réelle.

Comme déjà dit et répété oublie VBA pour la construction des TCD : tu pourras constater sur ce forum et d'autres que nous sommes nombreux à le dire... Un TCD se crée une fois pour toute manuellement et peut vivre des années sans problème et sans VBA...

1 pièce(s) jointe(s)

Ma réponse ci-haut est elle testée ????

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub test()
Dim derligne As Long
Dim j As Integer
tablo = Sheets("Extract 1").Range("A2", "B" & Sheets("Extract 1").Range("B" & Sheets("Extract 1").Rows.Count).End(xlUp).Row)
 
With Sheets("Extract 2")
.Columns("C:D").Select
Selection.ClearContents
.Cells(1, 3).Resize(1, 2) = Array("Total", "Différence")
derligne = .Range("A" & Rows.Count).End(xlUp).Row
For j = 2 To derligne
x = 0
For i = LBound(tablo, 1) To UBound(tablo, 1)
If .Cells(j, 1) = tablo(i, 1) Then
x = x + CDbl(tablo(i, 2))
End If
Next i
.Cells(j, 3) = x
.Cells(j, 4) = .Cells(j, 3) - .Cells(j, 2)
.Cells(j, 3).NumberFormat = "#,##0.00 $"
.Cells(j, 4).NumberFormat = "#,##0.00 $"
Next j
End With
End Sub

Citation:
Envoyé par BENNASR
Ma réponse ci-haut est elle testée ????
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Sub test() Dim derligne As Long Dim j As Integer tablo = Sheets("Extract 1").Range("A2", "B" & Sheets("Extract 1").Range("B" & Sheets("Extract 1").Rows.Count).End(xlUp).Row) With Sheets("Extract 2") .Columns("C:D").Select Selection.ClearContents .Cells(1, 3).Resize(1, 2) = Array("Total", "Différence") derligne = .Range("A" & Rows.Count).End(xlUp).Row For j = 2 To derligne x = 0 For i = LBound(tablo, 1) To UBound(tablo, 1) If .Cells(j, 1) = tablo(i, 1) Then x = x + CDbl(tablo(i, 2)) End If Next i .Cells(j, 3) = x .Cells(j, 4) = .Cells(j, 3) - .Cells(j, 2) .Cells(j, 3).NumberFormat = "#,##0.00 $" .Cells(j, 4).NumberFormat = "#,##0.00 $" Next j End With End Sub
Bonsoir,

C'est exactement une solution dans le genre que je cherchais. Je suis entrain d'essayer d'adapter ça à mes extractions et mes problématiques qui sont multiples (dates de valeur, devises à exclure etc).

Merci pour la piste, je reviendrais vers toi une fois celle-ci adaptée.

Bonne soirée

Jordan