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 , en fait j'aimerai ne colorier que la cellule qui pose problème et non la ligne ,car comme j'ai une centaine de colonne, pour que ca se voit tout de suite.

 

[eriiic]

Re : Macro Comparaison Complexe

Ok, ça sera pour demain soir seulement...
Et ça ne sera que la 1ère divergence de la ligne puisque je quitte dès qu'une différence est trouvée pour accélérer le traitement.
eric

 

[jacquesJ]

Re : Macro Comparaison Complexe

Ah ok, bah je ferai avec.Merci.

En gros ton next ligne tu le fais juste apres avoir trouvé une premiere différence.Ca ne va pas checker toute la ligne c'est bien ca?

 

[eriiic]

Re : Macro Comparaison Complexe

Oui, c'est ça.
Mais on peut l'enlever, c'est à toi de choisir en sachant que c'est au détriment des performances.

 

[jacquesJ]

Re : Macro Comparaison Complexe

OK bah ce serait cool que je puisse tester les 2, car savoir précisément ou sont les cellules d'erreurs est vraiment pas mal.Car imagine tu as 50 lignes en Feuille3 composées de 100 colonnes.Apres recomparer à la mano pour savoir ou est l'erreur, ca risque d'etre un peu galere.

Mais vous en avez deja beaucoup fait, vraiment vos scripts me sont tres utiles pour apprendre un peu le système des boucles.Vraiment Merci.

 

[eriiic]

Re : Macro Comparaison Complexe

A y réfléchir ça va poser un problème : c'est que dès que je ne trouve pas une ligne identique totalement je vais te ressortir toutes les lignes en divergence. cad toutes les lignes de Feuil1 et ça pour toutes les lignes de Feuil2 non trouvées, ça va faire du monde...
Et même si on se limite à 1 divergence (ce qui complique singulièrement le programme) il risque d'avoir plusieurs lignes.
Réfléchi à ça et si tu as un algorithme tu m'expliques.

 

[jacquesJ]

Re : Macro Comparaison Complexe

Quand tu dis: "c'est que dès que je ne trouve pas une ligne identique totalement je vais te ressortir toutes les lignes en divergence.", tu veux dire que ca va colorier les lignes de Feuil1 et Feuil2 entierement?Je n'ai pas tres bien compris je crois.

Au niveau de la comparaison, c'est de Feuil2 à Feuil1.Donc la Feuil1 on y touche pas.Ce sont les lignes de Feuil2 qui seront fausses,comme le fait ta macro.

 

[eriiic]

Re : Macro Comparaison Complexe

Effectivement, tu as raison, j'ai été un peu trop loin.
Mais prenons une ligne de Feuil2 non trouvée en Feuil1.
Par rapport à quelle ligne de Feuil1 je dois comparer pour marquer les cellules en divergence ?
Qu'est-ce qui la distingue des autres ? J'aurais des divergences sur toutes les lignes de Feuil1...

 

[jacquesJ]

Re : Macro Comparaison Complexe

Quand ta macro cherche les ligne de Feuil2 non existante dans Feuil1.Elle va checker les lignes de feuil2 et lorsque qu'une cellule d'une ligne n'est pas présente dans Feuil1 ,elle considere cela comme une divergence et donc copie cette ligne dans Feuil3.Et donc on pourrait colorier cette cellule.Et au lieu d'un next ligne des la premiere divergence, on peut laisser la macro checker toute la ligne pour voir si il y a d'autre divergence.Enfin c'est vrai que je en sais pas trop, tout dépend comment fonctionne ta macro.Mais si elle arrive bien a trouver une ligne qui n'existe pas dans Feuil1, ca veut bien dire qu'elle a trouver une cellule qui ne correspondait pas.

 

[eriiic]

Re : Macro Comparaison Complexe

La macro actuellement recherche si 1 ligne est exactement similaire, si il n'y en a pas elle sort en divergence.
Maintenant met 1 ligne en Feuil2 avec TT dans chaque cellule.
J'aurais des divergences sur chaque ligne de Feuil1.

 

[jacquesJ]

Re : Macro Comparaison Complexe

J'ai mis TTa chaque cellule d'une ligne de Feuill2 et il n'y a pas de probleme.Ca m'affiche bien comme une ligne ne correspondant pas car il n'y en a pas dans Feuill1.Donc ca, ca va.

Mais tu dis que la macro recherche les lignes exactement similaire.Donc ca veut dire que la macro détermine aussi les lignes non exactement similaire.Elle check donc les cellules non similaires. Ou du moins on pourrait pas prolonger la macro dans le "If Not Ok" pour qu'elle le fasse?
A ce niveau la: "If Not ok Then Exit For", on ne peut pas enlever le exit et dire If Not ok alors check la ligne pour regarder quelle cellule pose probleme?

Merci encore pour ton suivi.


Edit: Je comprends en fait ce que tu veux dire je pense. En gros quand une ligne de Feuil2 est fausse, on a pas d'élément de comparaison pour savoir avec quelle ligne de Feuill1 va cette ligne de Feuill2.Donc on ne pourra pas savoir précisément la cellule. Et donc en fait, tout ce que j'expliquais au début du sujet, et que je pensais inutile , et bien en fait est important pour cela.A savoir Dire en gros que Si Reference"Feuil1" = reference"Feuil2" ET Type"Feuil1"=Type"Feuil2" ET Marque"Feuil1" = Marqu"Feuil2" alors on retrouvera quelle ligne de Feuill2 correspond a quelle ligne de Feuill1 et on trouvera les cellules précises posant probleme.

Arf, je suis désolé de te dire ca maintenant apres tout ce que tu as fait.

Fo_Rum a fait une macro reprenant cette idée en bas de la page 1.Maais je n'arrive pas a la faire fonctionner lorsque la colonne MARQUE n'est plus en position 5 (mais en 7, ou 150 par exemple).Pourtant j'ai bien changer le numéro dans le code.

Edit2: En fait la macro de Fo_Rum marche bien. Je n'ai pas encore testé sur des gros fichiers.Il manquerai juste la couleur sur les cellules posant probleme.Enfin Bref je te remercie beaucoup Eric pour ce que tu as fait quand meme. Ta macro me fais apprendre pas mal de chose.

 

[skoobi]

Re : Macro Comparaison Complexe

Bonjour Eric, Jacques,

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.

Il y avait une erreur dans mon code, je l'ai rectifié et pas mal remanié vu que tu veux colorier la/les cellules qui sont différentes.
Par contre, comme le dit eric, cela va effectivement allonger le temps de traitement.
Regarde bien les commentaires que j'ai mis dans le code afin de l'adapter à ton fichier, j'ai essayé d'être le plus clair possible.
Le voici (basé sur le dernier fichier que tu m'as envoyé):

Option Compare Text 'pas de distinction entre majuscule et minuscule
Sub CompareSkoobi_v2()
'Déclaration de variables
Dim BDDSource As Variant, BDDCompare As Variant, Tresultat() As Variant, TColDiff() As Variant
Dim i As Long, j As Long, k As Long, NbCol As Long, LigSuiv As Long ', ColSource As Variant, ColCompare As Variant
Dim RefTypMarq As String, RefTypMarqCompare As String, ColoreAddrr As String  ', LigSource As String, LigCompare As String
'on définie la première et dernière colonne pour le résultat
'ATTENTION: la disposition doit être la même entre les 2 feuilles et toujours laisser "ColDebut" à 1:
Const ColDebut As Long = 1, ColFin As Long = 167
'on définie la première et dernière colonne pour la comparaison. Si les colonnes ne se suivent pas, il faudra créer
'une liste "Array".
Const ColDebutComp As Long = 2, ColFinComp As Long = 167
'on définie les colonnes REFERENCE, TYPE et MARQUE (pour les 2 feuilles)
Const ColRef As Long = 2, ColType As Long = 3, ColMarq As Long = 5
'pour voir le temps d'execution (pour beaucoup de données)
t = Timer
Application.ScreenUpdating = False
LigSuiv = 0
'on vide la feuille 3
Sheets("Feuil3").Cells.Clear
'transfert de la liste de données de la feuil1 vers le tableau VBA "BDDSource" ATTENTION: cela suppose que le tableau
'commence en cellule A1 (entête)
With Sheets("Feuil1")
  BDDSource = .Range("A2", .Cells(.Range("A2").End(xlDown).Row, ColFin))
End With
'transfert de la liste de données de la feuil2 vers le tableau VBA "BDDCompare" ATTENTION: cela suppose que le tableau
'commence en cellule A1 (entête)
With Sheets("Feuil2")
  BDDCompare = .Range("A2", .Cells(.Range("A2").End(xlDown).Row, ColFin))
End With
'la boucle "i" correspond à chaque ligne de la feuille 1 mais commence toujours à 1 (tableau VBA)
For i = LBound(BDDSource, 1) To UBound(BDDSource, 1)
'on concatène REFERENCE, TYPE et MARQUE de la ligne "i" de la feuil1 dans "RefTypMarqSource"
  RefTypMarqSource = UCase(BDDSource(i, ColRef)) & "#" & UCase(BDDSource(i, ColType)) & "#" & UCase(BDDSource(i, ColMarq))
'la boucle "j" correspond à chaque ligne de la feuille 2 mais commence toujours à 1 (tableau VBA)
  For j = LBound(BDDCompare, 1) To UBound(BDDCompare, 1)
'on initialise "ColoreAddrr", "NbCol" et "TColDiff"
    ColoreAddrr = "": NbCol = 0: Erase TColDiff
'on concatène REFERENCE, TYPE et MARQUE de la ligne "j" de la feuil2 dans "RefTypMarqCompare"
    RefTypMarqCompare = UCase(BDDCompare(j, ColRef)) & "#" & UCase(BDDCompare(j, ColType)) & "#" & UCase(BDDCompare(j, ColMarq))
'on vérifie l'égalité:
    If RefTypMarqCompare = RefTypMarqSource Then
'on compare la ligne, cellule par cellule pour identifier les cellules différentes:
      For k = ColDebutComp To ColFinComp
        If BDDSource(i, k) <> BDDCompare(j, k) Then
'on récupère les colonnes dans "TColDiff" si différences
          NbCol = NbCol + 1
          ReDim Preserve TColDiff(1 To NbCol)
          TColDiff(NbCol) = k
        End If
      Next
'si des cellules sont trouvées:
      If NbCol > 0 Then
        LigSuiv = LigSuiv + 1
'on identifie les cellules pour les colorier:
        For k = LBound(TColDiff) To UBound(TColDiff)
          ColoreAddrr = ColoreAddrr & Cells(LigSuiv, TColDiff(k)).Address(0, 0) & ","
        Next
        ColoreAddrr = Left(ColoreAddrr, Len(ColoreAddrr) - 1)
'on cré "Tresultat" afin de copier "d'un cout" dans la feuil3
        ReDim Preserve Tresultat(1 To ColFin, 1 To LigSuiv)
        For k = ColDebut To ColFin
          Tresultat(k, LigSuiv) = BDDCompare(j, k)
        Next
'et on applique la couleur sur les cellules différentes de la ligne analysée
        With Sheets("Feuil3")
          .Range(ColoreAddrr).Interior.ColorIndex = 45  'orange clair
        End With
      End If
    End If
  Next j
Next i
With Sheets("Feuil3")
'la copie:
  .Range("A1", .Cells(LigSuiv, ColFin)).Value = Application.Transpose(Tresultat)
'on récupère la ligne d'entête de la feuil1:
  .Rows(1).Insert Shift:=xlDown
  Sheets("Feuil1").Rows(1).Copy .Rows(1)
End With
'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

Bon bah je crois que c'est vite vu.

C'est vraiment super ce que tu as fait.Un code de brute.Ca répond exactement à ce que j'attendais.Je te remercie beaucoup pour ton aide.

Je remercie beaucoup Eric et Fo_Rum également.

Ca a été sans doute laborieux voire exaspérant pour vous, j'en suis désolé.Je m'excuse aussi pour mes pavés, mes confusions. Sachez que toutes vos macros m'ont été d'une aide précieuse et me serviront de support et de modele pour progresser.

Merci encore d'avoir été si disponible, et merci au forum également.

 

[skoobi]

Re : Macro Comparaison Complexe

Re,

De rien .

Par contre, si tu pouvais donner le résultat sur le temps d'execution en précisant le nombre de lignes traitées dans la feuil2, le nombre de lignes trouvées en feuil3 ainsi que les colonnes, ça m'intéresse.

A bientôt peut-être.

 

[eriiic]

Re : Macro Comparaison Complexe

Je vois qu'on a fini par se comprendre ;-)
Il faut donner les 3-4 champs importants pour pouvoir dire 'si je n'ai pas de ligne exacte, je compare si ces champs sont égaux et si oui je marque les autres champs divergents'
Comme tu as ta solution avec skoobi inutile que je fasse la modif.
eric