Hi
Its a bit slow but it does the job
Sub compare()
'
' compare Macro
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("OrigSheet").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "comptext"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-6]&RC[-5]&RC[-1]"
Range("H2").Select
Selection.Copy
Range("H3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Sheets("ReferenceSheet").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "comptext2"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]&RC[-6]&RC[-5]&RC[-1]"
Range("H2").Select
Selection.Copy
Range("H3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Sheets("OrigSheet").Select
Range("I1").Select
ActiveCell.FormulaR1C1 = "countmatch"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(ReferenceSheet!C[-1],RC[-1])"
Selection.Copy
Range("I3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$14").AutoFilter Field:=9, Criteria1:="=1", _
Operator:=xlOr, Criteria2:="=1048566"
Rows("2:2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Columns("I:H").Select
Selection.Delete Shift:=xlToLeft
Sheets("ReferenceSheet").Select
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Sheets("OrigSheet").Select
End Sub
Bookmarks