Macro Comparaison Complexe

[jacquesJ]

[Toutes Versions]Macro Comparaison Complexe

Bonjour a Tous,

Voila je débute tout juste en VBA et en programmation. Je sollicite votre aide car je suis quelque peu bloqué sur un projet que je voudrais faire.

Petit Topo:

J'ai 2 Feuilles excel ouvertes dans un classeur.

Premiere Feuille:
-------A---------B----------------C-------------D-----------E
1--Modele---Reference---------TYPE-----------couleur-----MARQUE
2 ---serie1--------1-------------hybride----------bleu--------BMW
3 ---serie1--------2-------------motrice----------Rose---------BMW
4----blabla--------1-------------hybride----------Noir--------toyota
5----TT----------1-------------hybride-----------Noir----------audi
6----TT----------2------------motrice-----------Jaune---------audi
7----TT----------2------------course------------rose---------audi


Deuxieme Feuille:

-------A---------B----------------C-------------D-----------E
1--Modele---Reference---------TYPE-----------couleur---MARQUE
2 ---M5--------1-------------hybride----------bleu--------BMW
3 ---M5--------2-------------motrice----------Rose---------BMW
4----titi--------1-------------hybride----------Noir--------toyota
5----R8----------1-------------hybride---------Noir----------audi
6----R8----------2------------motrice-----------Jaune---------audi
7----R8----------2------------course------------vert---------audi



On peut voir dans ces tableaux qu'on a des groupes de marques et de voitures.

La colonne B , reference , montre que si la valeur = 1 alors c'est la voiture "principale" et si la valeur = 2
alors ce sont des déclinaisons du meme modele.

J'aimerais comparer les données équivalentes de la feuille 1 a celles de la Feuille 2.
Que signifie équivalente? On a 3 colonnes de comparaison qui seront forcément identiques (MARQUE,TYPE,Reference), et grace a celles-ci je pourrais comparer les autres colonnes qui elles pourraient différer
.En gros, si Marque(feuille1)=Marque(Feuill2) ET TYPE(Feuille1)=TYPE(Feuill2) ET Reference("Feuille1")=Reference(Feuille2) alors on compare les 2 lignes.cellules a cellules.Si il y a une erreur copier la ligne de la deuxieme feuille dans une nouvelle feuille excel.

Il faut ignorer le Modele, car il sera toujours différent.
En Gros au finale je devrais avoir une Feuille avec les lignes de Feuille 2 qui ont eu une différence lors de la comparaison.

Bien sur j'ai mis que le minimum ici, sinon il y a des milliers de lignes et des dizaines d'autre colonnes.
C'est pour ca qu'il faut un code VBA pour automatiser ca a grande echelle.

Je suis conscient que j'en demande beaucoup,c'est assez complexe je pense ,mais je seche la.Je ne sais pas trop comment m'y prendre.
Si vous avez au moins des indications, je suis preneur.

Bref je vous remercie de me lire, et je suis dispo pour des infos supplémentaires.

 

[jacquesJ]

Re : Macro Comparaison Complexe

Salut Skoobi, Salut Eric.

Alors petit changement.Comme dit Eric et tes remarques précédentes m'ont ouvert les yeux, mon "délire" de matcher les lignes correspondantes entre elles
n'est pas du tout utile. Dans le fond c'est ca que je veux faire.Mais dans la forme, la méthode d'Eric est tres bien en fait.

En gros il faudrait,je pense, que le macro fasse comme ceci:

On comparer la Feuil2 et la Feuil1, si des lignes sont différentes dans la Feuil2 par rapport a la Feuil1 alors les recopier dans une nouvelle Feuille, et si possible colorier en rouge les cellules posant probleme.




Seulement dans les 2 tableaux, il y aura des colonnes a exclure de la comparaison mais a garder lors du résultat final.Comme la colonne modele car elle sera forcément différente.
Donc la solution de Eric est tres bien si on peut selectionner les colonnes.Seulement il y en aura pas mal.plus de cinquante.


Et pour finir, ton fichier Eric, j'ai des petits soucis avec.Car j'ai l'impression qu'elle ne compare que la colonne MARQUE.Pour exemple il y a une cellule "auto" dans la feuil2 et pas dans Feuil1 et ca ne la releve pas.Ou de meme lorsque je change une valeur dans Couleur, ca ne relevera pas l'erreur.

Merci encore pour vos précisions.Au plaisir

EDIT: Salut Fo Rum, c'est exactement ca apparament. Je vais le tester sur des plus gros tableaux.Avec d'autres valeurs.Pourrais-tu m'expliquer un peu ce que tu ais dans ta macro?

Je vous remercie tous en tout cas, vous etes des bons en vba.

ps: saurais-tu comment colorier en rouge les cellules qui posent probleme dans la feuille de résultat?Ou au moins savoir si c'est dur a faire?

 

[eriiic]

Re : Macro Comparaison Complexe

Re,

Et pour finir, ton fichier Eric, j'ai des petits soucis avec.Car j'ai l'impression qu'elle ne compare que la colonne MARQUE.Pour exemple il y a une cellule "auto" dans la feuil2 et pas dans Feuil1 et ca ne la releve pas
Voilà, c'est ce type de remontée qu'il me faut.
Testé vite fait car pas trop le temps mais effectivement j'ai dû mettre le brin en faisant une modif vite fait et plein d'autre choses ne doivent plus marcher.
Pas grave, ça sera corrigé.
Le plus important c'est de s'être compris sur le principe

eric

 

[jacquesJ]

Re : Macro Comparaison Complexe

Re Eric, merci de m'aider encore,ce serait cool de fix en effet car j'aimerais bien tester ta méthode sur mes tableaux avec des milliers de lignes et pres de cent colonnes.

Car j'ai testé la macro de Fo Rum et apparament ca foire sur de gros tableaux.
Car ma colonne marque est en colonne 167, j'ai bien sur modifier dans le code, mais apparament ca me releve des lignes qui sont identiques. :x

 

[skoobi]

Re : Macro Comparaison Complexe

re,
bonjour Fo_rum,

jacquesJ, que ce soit moi, Fo_rum ou Eric qui te proposons des solutions (eeuuuh moi j'ai encore rien fait ) basé sur un fichier qui ne reflête pas, au-moins de quelques lignes, ton fichier réel, on risque de tourner en rond, et de se lasser (moi en tout cas).

 

[jacquesJ]

Re : Macro Comparaison Complexe

Bien recu, je suis désolé, je vous envoie ce soir un fichier assez complet.

 

[eriiic]

Re : Macro Comparaison Complexe

Re re,

Effectivement, un oubli de variable et je contrôlais toujours la même colonne.
Voici la version corrigée.
J'ai ajouté la possibilité de saisie de fourchettes de colonnes, elles sont à saisir entre des " ".
Ex: colonnes = Array(5, 3, "1-2", 4, 6, "7- 25")
N'oublie pas que le choix des colonnes est primordial dans la vitesse d'exécution. La 1ère devrait être celle qui a le plus de valeurs différentes, la trier améliore encore la vitesse. Essai d'en choisir 3, ensuite des fourchettes c'est bon.

Comme tu as des pb de temps j'ai ajouté dans la ligne d'état (en bas) un compteur pour suivre l'évolution et savoir que ça tourne bien.
Je colorie les lignes absentes en Feuil2, en Feuil3 un commentaire te donne le n° de ligne d'origine de Feuil2

Il reste peut-être (sans doute) des bugs mais il est tard ;-) et ça te permettra de tester un peu...

eric

 

[jacquesJ]

Re : Macro Comparaison Complexe

Salut Eric, je te remercie je vais tester ca de suite. Par contre je crois que tu as malencontreusement oublié de up le fichier.

Merci encore.

 

[skoobi]

Re : Macro Comparaison Complexe

Bonjour tout le monde,

j'ai loupé un épisode?
tu n'as pas mis le fichier, Jacques .
Sur quoi ce base Eric pour les tests?

Bon week-end à tous .

 

[jacquesJ]

Re : Macro Comparaison Complexe

Je ne l'ai pas encore mis, car je pense qu'en fait l'erreur vient de moi et non du script de Fo_Rum, donc j'attend d'etre sur, c'est assez prise de tete avec toute ces colonnes. .Et les fichiers sont limités a 50Ko, c'est assez peu.

Je fini mes tests avec le script de Fo_Rum,(faut bien que je comprenne son code comme ca je pourrai tester plus efficacement) si ce n'est pas fructeux, je mettrai le fichier en 2-3 .zip.

Sinon Eric lui dans sa macro fait comme cela si j'ai bien compris:

On selectionne les colonnes qui vont rentrer dans la comparaison.Puis la macro affichera les lignes de Feuil2 qui ne sont pas présentes dans Feuil1.
Ca marche aussi.

 

[eriiic]

Re : Macro Comparaison Complexe

oupsss, le fichier.... ;-)
Oufff, je me mélangeais un peu entre les fichiers et j'ai cru que je l'avais perdu.

Sur quoi ce base Eric pour les tests?
Ben sur les 5 malheureuses lignes du début qui ne présentaient aucune différences entre les 2 feuilles. Le reste j'essaie d'imaginer...
Normal que mes tests soient toujours bons ;-)

Le voici

 

[skoobi]

Re : Macro Comparaison Complexe

Re,

Je ne l'ai pas encore mis, car je pense qu'en fait l'erreur vient de moi et non du script de Fo_Rum, donc j'attend d'etre sur, c'est assez prise de tete avec toute ces colonnes. .Et les fichiers sont limités a 50Ko, c'est assez peu.

Bon, en attendant que tu sois sûr, et que je comprends qu'il n'est pas facile de mettre tout ça dans un fichier de 50Ko, je te propose ma solution, vu le temps que j'ai passé à lire et relire ton problème...
Je l'ai commenté comme tu peux t'en apercevoir .
Ce code est basé sur ton premier fichier.
Sans couleurs ou autres pour identifier les différences, en attendant tes précisions.

Sub Compare()
'Déclaration de variables
Dim BDDSource As Variant, BDDCompare As Variant, LigneJ As Variant
Dim i As Long, j As Long, k As Long, LigSuiv As Long, ColSource As Variant, ColCompare As Variant
Dim RefTypMarq As String, RefTypMarqCompare As String, LigSource As String, LigCompare As String

'pour voir le temps d'execution (pour beaucoup de données)
t = Timer
'on vide la feuille 3
Sheets("Feuil3").Cells.Clear
'on définie les colonnes voulues pour la comparaison sur la ligne: "ColSource" pour le feuil1,"ColCompare" pour la feuil2
ColSource = Array(2, 3, 4, 5, 6, 7)
ColCompare = Array(1, 2, 3, 4, 5, 6)
'transfert de la liste de données de la feuil1 vers le tableau VBA "BDDSource"
With Sheets("Feuil1")
BDDSource = .Range("A2:G" & .Range("A2").End(xlDown).Row)
End With
'transfert de la liste de données de la feuil2 vers le tableau VBA "BDDCompare"
With Sheets("Feuil2")
BDDCompare = .Range("A2:F" & .Range("A2").End(xlDown).Row)
End With
'la boucle "i" correspond à chaque ligne de la feuille 1
For i = LBound(BDDSource, 1) To UBound(BDDSource, 1)
'on concatène REFERENCE (colonne 3), TYPE (colonne 4) et MARQUE (colonne 6) de la ligne "i" de la feuil1 dans "RefTypMarqSource"
  RefTypMarqSource = BDDSource(i, 3) & "#" & BDDSource(i, 4) & "#" & BDDSource(i, 6)
'la boucle "j" correspond à chaque ligne de la feuille 2
   For j = LBound(BDDCompare, 1) To UBound(BDDCompare, 1)
'on concatène REFERENCE (colonne 2), TYPE (colonne 3) et MARQUE (colonne 5) de la ligne "j" de la feuil2 dans "RefTypMarqCompare"
    RefTypMarqCompare = BDDCompare(j, 2) & "#" & BDDCompare(j, 3) & "#" & BDDCompare(j, 5)
'on vérifie l'égalité:
    If RefTypMarqCompare = RefTypMarqSource Then
'on récupère les colonnes de la feuil1 via "BDDSource" que l'on concatène dans "LigSource"
      For k = LBound(ColSource) To UBound(ColSource) - 1
        LigSource = LigSource & BDDSource(i, ColSource(k)) & "#"
      Next
'on récupère les colonnes de la feuil2 via "BDDCompare" que l'on concatène dans "LigCompare"
      For k = LBound(ColCompare) To UBound(ColCompare) - 1
        LigCompare = LigCompare & BDDCompare(j, ColCompare(k)) & "#"
      Next
'on compare, enfin, les 2 lignes:
      If LigSource <> LigCompare Then
'on cré "LigneJ" afin de copier "d'un cout" dans la feuil3
        LigneJ = Application.Index(BDDCompare, j)
        LigneJ = Application.Transpose(LigneJ)
        With Sheets("Feuil3")
'"LigSuiv" correspond à la prochaine ligne pour copier le résultat
          LigSuiv = .Range("A65536").End(xlUp).Row + 1
'la copie:
          .Range("A" & LigSuiv & ":F" & LigSuiv).Value = Application.Transpose(LigneJ)
        End With
      End If
    End If
  Next
Next
'affiche le temps d'execution, en seconde, dans la fenêtre d'execution (Ctrl+G)
Debug.Print Timer - t
End Sub

Bon test.

 

[jacquesJ]

Re : Macro Comparaison Complexe

Salut Skoobi,

donc j'ai testé ta macro et j'ai quelques petits soucis.Je te met mon fichier de test ci-joint.

En gros je choisi les colonnes 2, 3, 4, 5, 6, 7 pour la comparaison, pour les 2 feuilles.

la seule différence entre les 2 fichiers est la cellule F3 de la feuille 2.Et donc en résultat, j'ai 3 lignes.J'ai modifié aussi la place des colonnes aussi.

Pour marquesource et marquecompare:

BDDCompare(j, 2) & "#" & BDDCompare(j, 3) & "#" & BDDCompare(j, 5)

car les colonnes sont a la meme place dans les 2 feuilles.

Merci pour ton aide en tout cas.

 

[eriiic]

Re : Macro Comparaison Complexe

pour info j'ai mis le fichier à 13h54
eric

 

[jacquesJ]

Re : Macro Comparaison Complexe

Salut Eric, j'ai donc testé ta macro et apparament ca marche nickel.

Encore Merci et Chapeau.Je vais rebosser dessus si tu veux bien, pour voir comment tu as fait et essayer d'apprendre de toi.
Sinon pour mettre dans Feuill3 la donnée qui a posé probleme en couleur, est-ce une simple modif de:
sh2.Cells(cptligF2, 1).Resize(1, nbcol).Copy Destination:=sh3.Cells(cptligF3, 1)
sh2.Cells(cptligF2, 1).Resize(1, nbcol).Interior.ColorIndex = 38
?

Merci, au plaisir.

 

[eriiic]

Re : Macro Comparaison Complexe

Si tu la veux en couleur également dans Feuil3 inverse les 2 lignes (pour la colorer avant la copie)

Si c'est encore un peu lent on peut accélérer en quittant la boucle quand le champ 1 Feuil1 n'a plus la valeur de champ 1 Feuil2 à condition que celui-ci soit toujours trié (tri que l'on peut inclure dans la macro)
Ca évite de balayer Feuil1 jusqu'en bas pour rien pour chaque ligne de Feuil2

eric