Comparaison listes

[SERHY]

bonjour
pourriez vous m'aider à mettre en place une recherche et comparaison de deux listes de données.
Il me faut comparer les deux "sources" afin de voir si les données sont identiques et identifier les erreurs ou manques eventuels fichier test ci-joint
merci

 

[Jocelyn]

Re : Comparaison listes

Bonjour le forum,
Bonjour SERHY,

Un essai en fichier joint

Cordialement

 

[Sheldor]

Re : Comparaison listes

bonjour,
j'utilise le code ci dessous pour ce genre de comparaison, à partir d'une sélection d'une plage de cellules (dans deux colonnes seulement, les deux à comparer, vous pouvez coller B et F par exemple pour les comparer)

si ça peut aider...

Application.ScreenUpdating = False

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Range("G3").Select
ActiveCell.FormulaR1C1 = "=""liste: ""&(R[-2]C[-6])"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=""liste: ""&(R[-3]C[-5])"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-1]C[-7]:R[65533]C[-7])"
Range("H4").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-2]C[-6]:R[65532]C[-6])"
Range("I3").Select
ActiveCell.FormulaR1C1 = "valeurs"
Range("I4").Select
ActiveCell.FormulaR1C1 = "valeurs"
Range("I5").Select
aaa2 = Cells(3, 8)
bbb2 = Cells(4, 8)

'
Range("C1").Select
ActiveCell.FormulaR1C1 = "colonne A présent dans colonne B"
Range("D1").Select
ActiveCell.FormulaR1C1 = "colonne B présent dans colonne A"
Range("C11").Select
With Selection
.WrapText = True
End With


Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-2])"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-2])"


Range("C2").Select
Selection.Copy
Range(Cells(2, 3), Cells(aaa2 + 1, 3)).Select

'Range("C2:C30").Select
ActiveSheet.Paste
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(2, 4), Cells(bbb2 + 1, 4)).Select
ActiveSheet.Paste
Range("G3").Select
Application.CutCopyMode = False
'
Range("J3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C[-6]:R[64997]C[-6])"
Range("J4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C[-7]:R[64996]C[-7])"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=""sont présentes dans ""&R[1]C[-4]"
Range("K4").Select
ActiveCell.FormulaR1C1 = "=""sont présentes dans ""&R[-1]C[-4]"
Range("K5").Select
'coloration
Range("A:A,C:C").Select
Range("C1").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("G3:M3").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("B:B,D").Select
Range("D1").Activate
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("G4:M4").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("G8").Select
ActiveWindow.SplitRow = 3.94117647058824
ActiveWindow.FreezePanes = True
'
Range("G3:G4").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J3:J4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J3:M4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("G:G").ColumnWidth = 16.43
Range("G3:I4").Select
Range("I3").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G8").Select
'MEFC rouge si 0

Range(Cells(2, 3), Cells(aaa2 + 1, 3)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 46
Range("H15").Select

Range(Cells(2, 4), Cells(bbb2 + 1, 4)).Select

'
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 46
Range("H15").Select

 

[SERHY]

Re : Comparaison listes

Re
Super merci de votre aide aussi rapide et efficace
bonne journée et à bientôt le forum