Comparaison de 2 listes
N
Bonjour,
Je souhaiterais comparer 2 listes (2 onglets : "liste" et "liste(1)") et mettre en évidence les lignes qui diffèrent dans un troisième onglet ("compare")
Pour l'instant j'ai pensé créer une macro qui identifie les lignes qui diffèrent mais celle que j'ai rédigé ne me donne pas du tout le résultat attendu.
Plus en détail au niveau du résultat attendu :
Si pour 2 lignes ayant les mêmes identifiants (colonne "code") certaines des informations correspondantes à ce code diffèrent entre les deux listes je veux identifier quelles sont les différences et les lister dans un troisième onglet.
Voilà le code que j'ai rédigé pour l'instant :
Sub test()
Dim cell As Range
Dim f3line As Integer
Set F1 = Worksheets("liste")
Set F2 = Worksheets("liste (1)")
Set F3 = Worksheets("COMPARE")
f3line = 2
For Each cell In F1.Range("A2", F1.Range("A2").End(xlDown))
Dim code As String
Dim f1line As Integer
code = cell.value
f1line = cell.Row
Set c = F2.Columns("A:A").Find(what:=code)
If Not c Is Nothing Then
Dim f2line As Integer
f2line = c.Row
Dim test_nom As Boolean
Dim test_info1 As Boolean
Dim test_info2 As Boolean
Dim test_X1 As Boolean
Dim test_info3 As Boolean
Dim test_X2 As Boolean
Dim test_X3 As Boolean
test_nom = F1.Cells(f1line, 2) <> F2.Cells(f2line, 2)
test_info1 = F1.Cells(f1line, 3) <> F2.Cells(f2line, 5)
test_info2 = F1.Cells(f1line, 4) <> F2.Cells(f2line, 7)
test_X1 = F1.Cells(f1line, 5) <> F2.Cells(f2line, 6)
test_info3 = F1.Cells(f1line, 6) <> F2.Cells(f2line, 8)
test_X2 = F1.Cells(f1line, 7) <> F2.Cells(f2line, 3)
test_X3 = F1.Cells(f1line, 8) <> F2.Cells(f2line, 4)
If test_nom = True Or test_info1 = True Or test_info2 = True Or test_X1 = True Or test_info3 = True Or test_X2 = True Or test_X3 = True Then
F3.Cells(f3line, 1) = code
If test_nom = True Then
F3.Cells(f3line, 2) = F2.Cells(f2line, 2)
ElseIf test_info1 = True Then
F3.Cells(f3line, 3) = F2.Cells(f2line, 5)
ElseIf test_info2 = True Then
F3.Cells(f3line, 4) = F2.Cells(f2line, 7)
ElseIf test_X1 = True Then
F3.Cells(f3line, 5) = F2.Cells(f2line, 6)
ElseIf test_info3 = True Then
F3.Cells(f3line, 6) = F2.Cells(f2line, 8)
ElseIf test_X2 = True Then
F3.Cells(f3line, 7) = F2.Cells(f2line, 3)
ElseIf test_X3 = True Then
F3.Cells(f3line, 8) = F2.Cells(f2line, 4)
f3line = f3line + 1
End If
End If
End If
Next cell
End SubDonc ce que j'espérais que ça fasse :
Si pour 2 codes égaux il y a une information qui diffère, le code est intégré dans l'onglet "compare" puis la ou les information(s) qui diffère(nt) sont à leur tour intégrée(s) dans la même ligne de l'onglet "compare" et ainsi de suite.
Le fichier est en pièce jointe.
Merci d'avance pour votre aide !
Nuns
Bonjour Nuns
En espérant ne pas me tromper
Sub test()
Dim cell As Range
Dim f3line As Integer
Set F1 = Worksheets("liste")
Set F2 = Worksheets("liste (1)")
Set F3 = Worksheets("COMPARE")
f3line = 2
For Each cell In F1.Range("A2", F1.Range("A2").End(xlDown))
Dim code As String
Dim f1line As Integer
code = cell.value
f1line = cell.Row
Set c = F2.Columns("A:A").Find(what:=code)
If Not c Is Nothing Then
Dim f2line As Integer
f2line = c.Row
Dim test_nom As Boolean
Dim test_info1 As Boolean
Dim test_info2 As Boolean
Dim test_X1 As Boolean
Dim test_info3 As Boolean
Dim test_X2 As Boolean
Dim test_X3 As Boolean
test_nom = F1.Cells(f1line, 2) <> F2.Cells(f2line, 2)
test_info1 = F1.Cells(f1line, 3) <> F2.Cells(f2line, 5)
test_info2 = F1.Cells(f1line, 4) <> F2.Cells(f2line, 7)
test_X1 = F1.Cells(f1line, 5) <> F2.Cells(f2line, 6)
test_info3 = F1.Cells(f1line, 6) <> F2.Cells(f2line, 8)
test_X2 = F1.Cells(f1line, 7) <> F2.Cells(f2line, 3)
test_X3 = F1.Cells(f1line, 8) <> F2.Cells(f2line, 4)
If test_nom = True Or test_info1 = True Or test_info2 = True Or test_X1 = True Or test_info3 = True Or test_X2 = True Or test_X3 = True Then
F3.Cells(f3line, 1) = code
If test_nom = True Then F3.Cells(f3line, 2) = F2.Cells(f2line, 2)
If test_info1 = True Then F3.Cells(f3line, 3) = F2.Cells(f2line, 5)
If test_info2 = True Then F3.Cells(f3line, 4) = F2.Cells(f2line, 7)
If test_X1 = True Then F3.Cells(f3line, 5) = F2.Cells(f2line, 6)
If test_info3 = True Then F3.Cells(f3line, 6) = F2.Cells(f2line, 8)
If test_X2 = True Then F3.Cells(f3line, 7) = F2.Cells(f2line, 3)
If test_X3 = True Then F3.Cells(f3line, 8) = F2.Cells(f2line, 4)
End If
f3line = f3line + 1
End If
Next cell
End SubN
Bonjour,
Merci ca fonctionne parfaitement !
Si je veux en plus de ca colorer les cellules avec les informations dans l'onglet "compare" comment dois je faire ?
J'ai essayé :
If test_nom = True Then F3.Cells(f3line, 2) = F2.Cells(f2line, 2) and F3.Cells(f3line, 2).Interior.ColorIndex = 6Mais ça ne fonctionne pas...
Re
pour la liste1
Sub test()
Dim cell As Range
Dim f3line As Integer
Set F1 = Worksheets("liste")
Set F2 = Worksheets("liste (1)")
Set F3 = Worksheets("COMPARE")
f3line = 2
For Each cell In F1.Range("A2", F1.Range("A2").End(xlDown))
Dim code As String
Dim f1line As Integer
code = cell.value
f1line = cell.Row
Set c = F2.Columns("A:A").Find(what:=code)
If Not c Is Nothing Then
Dim f2line As Integer
f2line = c.Row
Dim test_nom As Boolean
Dim test_info1 As Boolean
Dim test_info2 As Boolean
Dim test_X1 As Boolean
Dim test_info3 As Boolean
Dim test_X2 As Boolean
Dim test_X3 As Boolean
test_nom = F1.Cells(f1line, 2) <> F2.Cells(f2line, 2)
test_info1 = F1.Cells(f1line, 3) <> F2.Cells(f2line, 5)
test_info2 = F1.Cells(f1line, 4) <> F2.Cells(f2line, 7)
test_X1 = F1.Cells(f1line, 5) <> F2.Cells(f2line, 6)
test_info3 = F1.Cells(f1line, 6) <> F2.Cells(f2line, 8)
test_X2 = F1.Cells(f1line, 7) <> F2.Cells(f2line, 3)
test_X3 = F1.Cells(f1line, 8) <> F2.Cells(f2line, 4)
If test_nom = True Or test_info1 = True Or test_info2 = True Or test_X1 = True Or test_info3 = True Or test_X2 = True Or test_X3 = True Then
F3.Cells(f3line, 1) = code
If test_nom = True Then F3.Cells(f3line, 2) = F2.Cells(f2line, 2): F2.Cells(f2line, 2).Interior.Color = 255
If test_info1 = True Then F3.Cells(f3line, 3) = F2.Cells(f2line, 5): F2.Cells(f2line, 5).Interior.Color = 255
If test_info2 = True Then F3.Cells(f3line, 4) = F2.Cells(f2line, 7): F2.Cells(f2line, 7).Interior.Color = 255
If test_X1 = True Then F3.Cells(f3line, 5) = F2.Cells(f2line, 6): F2.Cells(f2line, 6).Interior.Color = 255
If test_info3 = True Then F3.Cells(f3line, 6) = F2.Cells(f2line, 8): F2.Cells(f2line, 8).Interior.Color = 255
If test_X2 = True Then F3.Cells(f3line, 7) = F2.Cells(f2line, 3): F2.Cells(f2line, 3).Interior.Color = 255
If test_X3 = True Then F3.Cells(f3line, 8) = F2.Cells(f2line, 4): F2.Cells(f2line, 4).Interior.Color = 255
End If
f3line = f3line + 1
End If
Next cell
End Subou la feuille compare
Sub test()
Dim cell As Range
Dim f3line As Integer
Set F1 = Worksheets("liste")
Set F2 = Worksheets("liste (1)")
Set F3 = Worksheets("COMPARE")
f3line = 2
For Each cell In F1.Range("A2", F1.Range("A2").End(xlDown))
Dim code As String
Dim f1line As Integer
code = cell.value
f1line = cell.Row
Set c = F2.Columns("A:A").Find(what:=code)
If Not c Is Nothing Then
Dim f2line As Integer
f2line = c.Row
Dim test_nom As Boolean
Dim test_info1 As Boolean
Dim test_info2 As Boolean
Dim test_X1 As Boolean
Dim test_info3 As Boolean
Dim test_X2 As Boolean
Dim test_X3 As Boolean
test_nom = F1.Cells(f1line, 2) <> F2.Cells(f2line, 2)
test_info1 = F1.Cells(f1line, 3) <> F2.Cells(f2line, 5)
test_info2 = F1.Cells(f1line, 4) <> F2.Cells(f2line, 7)
test_X1 = F1.Cells(f1line, 5) <> F2.Cells(f2line, 6)
test_info3 = F1.Cells(f1line, 6) <> F2.Cells(f2line, 8)
test_X2 = F1.Cells(f1line, 7) <> F2.Cells(f2line, 3)
test_X3 = F1.Cells(f1line, 8) <> F2.Cells(f2line, 4)
If test_nom = True Or test_info1 = True Or test_info2 = True Or test_X1 = True Or test_info3 = True Or test_X2 = True Or test_X3 = True Then
F3.Cells(f3line, 1) = code
If test_nom = True Then F3.Cells(f3line, 2) = F2.Cells(f2line, 2): F3.Cells(f3line, 2).Interior.Color = 255
If test_info1 = True Then F3.Cells(f3line, 3) = F2.Cells(f2line, 5): F3.Cells(f3line, 3).Interior.Color = 255
If test_info2 = True Then F3.Cells(f3line, 4) = F2.Cells(f2line, 7): F3.Cells(f3line, 4).Interior.Color = 255
If test_X1 = True Then F3.Cells(f3line, 5) = F2.Cells(f2line, 6): F3.Cells(f3line, 5).Interior.Color = 255
If test_info3 = True Then F3.Cells(f3line, 6) = F2.Cells(f2line, 8): F3.Cells(f3line, 6).Interior.Color = 255
If test_X2 = True Then F3.Cells(f3line, 7) = F2.Cells(f2line, 3): F3.Cells(f3line, 7).Interior.Color = 255
If test_X3 = True Then F3.Cells(f3line, 8) = F2.Cells(f2line, 4): F3.Cells(f3line, 8).Interior.Color = 255
End If
f3line = f3line + 1
End If
Next cell
End Sub