Discussion :

[totojava]

Bonjour,
Voilà une question générale mais qui se pose à moi dans le cadre de VBA.
Je veux comparer ligne à ligne 4 champs sur deux feuille F1 et F2 contenant 50000 lignes au moins. Les lignes correspondantes peuvent être n'importe où (par exemple ligne_1 de F1 = ligne_45987 de F2 !), voire ne pas exister dans un sens ou dans l'autre...

J'ai écrit un programme VBA qui fait ça, mais bien sûr il est long car il regarde chaque ligne de F1 pour la comparer à chaque ligne de F2. Même quand on trouve une correspondance pour une certaine ligne de F1 dans F2, la ligne dans F2 va être retestée pour les valeurs suivantes des lignes de F1, ce qui est inutile (car à une ligne correspond au plus une ligne).
Sachant que je ne veux pas supprimer les lignes dans F2 car j'ai besoin du numéro de ligne exact pour savoir qui concorde avec quoi entre F1 et F2, comment puis-je faire en sorte de ne pas retester les lignes déjà en correspondance (un peu comme dans un crible) ?

[lucasgaetan]

Bonjour totojava,

Tu pourrait créer une variable de type string, où tu insert tes lignes ayant une correspondance sur 5 caractères par exemple.
du genre:
Ma_variable = "00152;01245;00023" etc...
puis avant de tester ta ligne tu fais un test "instr" avec le n° de ligne

sinon tu créais une colonne où tu dis si t'as ligne à été tester, et pourquoi pas la masquer pour l’esthétisme.

bon courage.

[joe.levrai]

Bonjour,

ok pour la comparaison, qui équivaut selon tes explications à trouver les lignes des deux feuilles qui ont 4 champs identiques ... mais pour faire quoi ensuite ? Car sans cette explication, il y a des milliers de possibilités.

Par exemple, en oubliant le VBA (pour commencer), la formule NB.SI.ENS() appliquée sur une nouvelle colonne de chacun des deux tableaux va très rapidement te sortir les occurrences identiques et te les dénombrer, de là tu viens de réaliser 80% du travail, quelle que soit la finalité (que tu n'as hélas pas exposé).

[totojava]

@lucasgaetan : hum hum, désolé mais je ne comprends pas le but/l'intérêt de Ma_variable...
(Rappel : lorsque je trouve une correspondance, il n'y en a pas d'autres, c'est certain.)

J'ai bien pensé à créer une colonne supplémentaire sur la feuille 2 et la marquer d'un "x" à chaque fois qu'une ligne est mise en correspondance avec une autre de la feuille 1, mais ça oblige à faire un test supplémentaire (i.e. "est-ce que cette ligne contient un "x" dans cette colonne ? Si oui, passer à la suivante") à chaque nouvelle boucle sur la feuille 2 pour savoir si la ligne doit être utilisée ou non. Et ce test se fera donc autant de fois qu'il y a ligne sur la feuille 1.
Au final, pas sûr que je gagne vraiment quelque chose à remplacer un test avec 3 "And" par un test sur "x" + éventuellement ce test avec les 3 "And" !!

J'avais plutôt penser à définir un tableau contenant initialement la colonne de valeurs à tester puis à "retirer" le numéro de ligne de cette plage de valeurs à chaque fois que je trouvais une correspondance, mais est-ce que de réarranger le tableau pour en diminuer la taille à chaque correspondance ne va pas compenser négativement le gain sur le nombre de lignes à tester ?

[totojava]

@joe.levrai : Je connais les fonctions directes dans les cellules Excel, mais je pensais précisément pour avoir fait des essais que les performances seraient meilleures avec du VBA.
Par ailleurs, le code de ma macro sera réexploitable (avec modifications éventuelles) à l'infini alors que refaire les formules à chaque fois, c'est un peu "galère".
Par ailleurs, une fois que j'ai identifié les lignes en correspondance, je fais un test supplémentaire sur un autre champ (c'est cela qui m'intéresse), facile à faire en VBA dans la même macro...

[joe.levrai]

Bonjour,

alors explique plus précisément ton projet, quelques copies écran de l'état initial, de l'état final.
et vraiment ce que tu fais ensuite. Là tu rajoutes une action supplémentaire qui n'était pas abordée dans ton premier message

par exemple, on croit "deviner" qu'il faut que les 4 champs de la feuille 1 soit en correspondance avec 4 autres champs de la feuille 2. Et ensuite, on a l'impression que "peut-être" c'est un seul champs en correspondance qui permet de toper la ligne comme "correspondante" ? Ca fait beaucoup de devinettes pour nous qui n'avons pas ton projet sous les yeux.
Si les 4 champs doivent correspondre, on peut utiliser une formule matricielle dans laquelle on concatène les 4 champs dans un critère unique.

Et je te garanti à 90%, pour un dénombrement comme celui-là, que c'est Excel qui va gagner face à VBA, autant manipuler les outils excel par VBA au pire.
Tous les outils excel sont déjà compilés contrairement aux actions VBA qui sont interprétées...

[totojava]

Pour avoir une idée (et éventuellement faire des remarques constructives...), voici la portion de code que j'utilise avec les deux boucles imbriquées. J'ai "anonymisé" le nom des variables (même les compteurs que je nomme de façon plus explicite en vrai). Par ailleurs, je ne fais plus que 3 tests au lieu de 4 (sur les entités A, B et C) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Dim vAFeuille(1 To 2) As Integer
Dim vBFeuille(1 To 2) As Integer
Dim vCFeuille(1 To 2) As Integer
 
' [... CODE ...]
' vAFeuille, vBFeuille, vCFeuille contiennent maintenant les numéros des colonnes 
' contenant les champs A, B et C à tester, et ce sur chaque feuille 1 et 2 
 
' Variables de raccourci pour éviter de manipuler des noms à rallonge dans le code :
Dim vA1 As Date
Dim vA2 As Date
Dim vB1 As Date
Dim vB2 As Date
Dim vC1 As String
Dim vC2 As String
Dim vZ1 As String
Dim vZ2 As String
 
' Compteur boucle externe :
Dim i As Long
i = 2
' Compteur boucle interne : 
Dim j As Long
' Résultat du test : 
Dim trouve As Boolean
 
Do Until (feuille(1).Cells(i, 1) = "")   ' On parcourt l'ensemble du tableau feuille 1 jusqu'à la dernière ligne
    feuilleResultat.Cells(i, 1) = i   ' On écrit le numéro de ligne en cours
    vA1 = feuille(1).Cells(i, vAFeuille(1))  ' Raccourci
    vB1 = feuille(1).Cells(i, vBFeuille(1))  ' Raccourci
    vC1 = feuille(1).Cells(i, vCFeuille(1))  ' Raccourci
    j = 1  ' (Ré)initialisation variable boucle interne
    trouve = False  ' (Ré)initialisation 
    Do Until (trouve Or feuille(2).Cells(j, 1) = "")  ' !!! On parcourt l'ensemble du tableau feuille 2 !!! 
        j = j + 1 
        vA2 = feuille(2).Cells(i, vAFeuille(2))  ' Raccourci
        vB2 = feuille(2).Cells(i, vBFeuille(2))  ' Raccourci
        vC2 = feuille(2).Cells(i, vCFeuille(2))  ' Raccourci
        trouve = ((vA1 = vA2) And (vB1 = vB2) And (vC1 = vC2))  ' test sur les trois colonnes
    Loop
 
    If trouve Then
        vZ(1) = feuille(1).Cells(i, vZFeuille(1))  ' Raccourci
        vZ(2) = feuille(2).Cells(i, vZFeuille(2))  ' Raccourci
        feuilleResultat.Cells(i, 3) = j 
        feuilleResultat.Cells(i, 2) = vZ(1)
        feuilleResultat.Cells(i, 4) = vZ(2)
        ' Mise en évidence si ça correspond bien : 
        If (vZ(1) Like vZ(2)) Then
            feuilleResultat.Cells(i, 2).Interior.ColorIndex = 4
            feuilleResultat.Cells(i, 4).Interior.ColorIndex = 4
        Else
            feuilleResultat.Cells(i, 2).Interior.ColorIndex = 3
            feuilleResultat.Cells(i, 4).Interior.ColorIndex = 3
        End If
    End If
    i = i + 1
Loop

[Philippe Tulliez]

Bonjour,
Je ne peux qu'abonder dans le sens de Joe que je salue au passage et j'ajouterais qu'après avoir établi la formule adéquate tu pourrais utiliser le filtre avancé d'excel pour filtrer sur place ou exporter les données concernées.
Si vraiment tu souhaites utiliser le VBA, c'est une ligne de code après avoir déclaré les zones data, critères et éventuellement exportation (Méthode AdvancedFilter de l'objet Range)

Exemple d'une formule qui compare trois colonnes et applicable avec le filtre avancé d'excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD( (A2=Feuil2!$A$2:$A$5) * (B2=Feuil2!$B$2:$B$5) * (E2=Feuil2!$E$2:$E$5)) = 1

[joe.levrai]

Bonjour,

au départ, j'avais commencé à réaliser l'exercice sous la même idée que Philippe , à savoir un filtre avancé.

mais comme tes données n'ont pas d'en-tête sur une des deux feuilles apparemment, et qu'on n'avait pas idée du traitement résultant de cette recherche, le code devenait vite long pour peu de choses, que de simples formules Excel (sur une colonne temporaire dédiée) pouvait résoudre dans sa partie principale (identification)

[totojava]

Merci pour vos remarques et suggestions, cependant elles ne répondent pas à ma demande initiale.
J'ai résolu mon problème en utilisant effectivement des tableaux et un objet appelé "dictionnaire" en m'inspirant de cet article : http://www.gcexcel.com/comparer-plusieurs-colonnes/.

Le résultat est stupéfiant : 50000 lignes comparées en moins de 2 secondes ! Alors qu'il fallait plusieurs dizaines de minutes en utilisant le code à boucles imbriquées ci-dessus...

Je ne sais pas combien de temps il faudrait avec des fonctions d'Excel (nb.si / recherchev / etc. combinées à des filtres plus ou moins savants), mais je ne suis pas certain d'après les essais que j'avais menés il y a quelques temps que cela aurait été plus rapide.

[joe.levrai]

En soit l'idée d'un dictionnaire n'est pas mauvaise mais ...

1) ce n'est pas un objet natif VBA, ton programme ne fonctionnera pas sous les versions MAC et pourrait poser des problèmes sur certaines machines sécurisées (inhibitions de bibliothèques)
2) bien que cela paraisse simple à utiliser (c'est en partie le cas), ce n'est pas non plus aussi évident que d'utiliser les outils Excel et objets VBA
3) ce type de procédé t'empêche dorénavant de construire ta mécanique en conservant la possibilité de ne pas utiliser du VBA et de tout faire sur l'interface Excel


Maintenant, ce qui compte, c'est ton propre intérêt et tes envies.
avec 2 colonnes supplémentaires (une colonne dans chaque feuille) on pouvait gérer tout ça ... avec un poil de VBA pour lancer le processus (10 lignes de codes basiques). Et niveau temps de traitement, ça devenait quasiment de l'instantané. Cependant .... 2 secondes de traitement actuel, c'est déjà pas mal.

[RyuAutodidacte]

Bonjour,

Joe je ne peux qu'abonder dans tons sens pour tes remarques, je t'ai mis 2 x
aussi pour Philippe

Ryu

[Philippe Tulliez]

Bonjour Joe, Ryu,

Pourquoi faire simple quand on peut faire compliqué.
Le filtre avancé d'excel est tellement simple à implémenter, tellement facile à maintenir que je ne me casse plus la tête quand je développe.
Quant à la vitesse comme je l'ai expliqué dans une de mes réponses sur le sujet

Un test sur une zone data de 3 colonnes et 272 769 lignes avec un nombre de lignes exportées répondant aux critères de 3 732 en créant une feuille pour l'exportation.
Temps moins d'une seconde

[totojava]

Merci pour vos retours. Je ne doute pas qu'il existe des manières plus simples ou plus performantes de faire, et pour tout dire, c'est mon premier programme en VBA, alors déjà pour arriver à dépatouiller ces histoires de sélection, de cellules, et de modèle de données, ça m'a pris du temps vu que ce n'est pas toujours simple et que vu les multiples possibilités pour écrire la même chose, on se perd vite dans la syntaxe (si si !). L'utilisation d'un dictionnaire m'a paru très intuitive (j'ai une formation d'ingénieur informaticien, c'est le genre d'objets un peu évolué qu'on rencontre dans d'autres langages de programmation).
Mais en ce qui concerne les fonctions avancées d'Excel, pour reprendre la remarque de l'un d'entre vous sur le sujet cité par Philipe T. :

Le filtre avancé, que ce soit pour un filtre sur place ou pour une extraction, est effectivement un outil un peu méconnu, peut-être parce que sa mise en place n'est pas intuitive.

Et je confirme que les fonctions avancées sont loin d'être intuitives, beaucoup moins que l'utilisation d'un dictionnaire selon mon point de vue personnel .

Enfin, oui, j'ai bien compris dès le départ toutes les limitations à utiliser du VBA ici (j'ai dû moi-même autoriser la bibliothèque nécessaire pour l'utilisation des dictionnaires, et j'ai tout de suite pensé que si je devais "exporter" ma macro sur un autre poste il faudrait gérer cela ; celui qui a galéré avec les bibliothèques java et les problèmes d'OS et de version 32/64 bits ne peut pas ne pas avoir ça en tête !). Mais je n'ai jamais dit que j'avais des exigences à ce sujet. Cette macro me permettra de faire une ou deux comparaisons sur mon poste à moi qu'est rien qu'à moi et je savais dès le départ que je n'étais pas limité à ce niveau. Donc pas d'inquiétude...

Enfin, je suis ouvert à découvrir les filtres avancées et plus généralement toutes les possibilités d'Excel en général qui est, je n'en doute pas une seule seconde, exploité à à peine quelques pourcents de ses possibilités par la majorité des utilisateurs (fonction tableur simple, avec un rendu joli grâce à toutes ses couleurs !). Aussi je vais regarder le tuto de Philippe T., mais je doute que ça me prenne moins de temps pour arriver à une solution que les trois ou quatre heures que j'ai dû investir déjà et m'ont permis de repérer des problèmes sur 50000 lignes, sachant que je peux extraire des fichiers de plus de 400.000 lignes d'une base qui contient 22 millions d'enregistrement en tout.

Je reviendrai avec plaisir poster ici si je rencontre des problèmes sur ces fonctions de filtres .

[Philippe Tulliez]

Bonjour,
J'ai été regardé le lien que tu as indiqué qui t'a permis de réaliser ton code avec le dictionnaire.
Je suis parti sur le même exemple à savoir exporter les lignes dont les données sont identiques en colonne D, E & F
Voici le code VBA qui est identique quels que soient les critères

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub ExportByAdvancedFilter()
 Dim areaSource As Range, areaTarget As Range, areaCriteria As Range
 With ThisWorkbook
  Set areaSource = .Worksheets("db").Range("A1").CurrentRegion
  Set areaTarget = .Worksheets("Export").Range("A1").CurrentRegion
  Set areaCriteria = .Worksheets("db").Range("B15:B16")
 End With
 areaTarget.Worksheet.Cells.Clear ' supprime les données de la feuille cible avant exportation
 areaSource.AdvancedFilter xlFilterCopy, areaCriteria, areaTarget   ' Exportation des données suivant critères
 Set areaSource = Nothing: Set areaTarget = Nothing: Set areaCriteria = Nothing
End Sub

La formule du critère nommé (B16)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=OU(D2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);4);E2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);5);F2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);6))

cette formule permet donc d'exporter les données identiques
Si la personne qui demande le travail souhaite maintenant faire l'inverse exporter les lignes contenant des données différentes dans une des colonnes D, E ou F
le critère devient

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NON(OU(D2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);4);E2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);5);F2<>INDEX(db_2!$A$2:$G$11;EQUIV(A2;db_2!$A$2:$A$11;0);6)))

et tout cela sans modifier le code VBA

[EDIT]
Si l'on nomme les plages des zones des données, des critères et la cellule cible le code est encore plus court.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub ExportByAdvancedFilter_2()
 Range("areaTarget").Worksheet.Cells.Clear ' supprime les données avant exportation
 Range("areaSource").AdvancedFilter xlFilterCopy, Range("areaCriteria"), Range("areaTarget")
End Sub

Je crois que c'est nettement plus court que les cinquante lignes de code dont tu as eu besoin pour utiliser le dictionnaire et faire la comparaison des critères.

En utilisant la méthode AdvancedFilter il suffit de modifier la zone des critères manuellement dans la cellule Excel pour obtenir un autre résultat.

De plus en utilisant la fonction INDIRECT et une liste déroulante, on peut construire un choix de critères illimités que l'utilisateur peut choisir à sa guise (voir le chapitre XIII. Sky is the limit du tutoriel Les filtres avancés ou élaborés dans Excel
Tout cela sans modifier le code VBA

Pour la pérennité du projet, je crois que la démonstration est faite.