Macro to compare cells

[Richyog]

Hi,

I want a macro to compare two columns in two different sheets.

Suppose:-
Sheet1 in column "A" contains a
b
c
d

and Sheet2 in column "A" contains a
b
c
d
e
now i want that my macro should compare these two columns and different cell should be put in the third sheet

or different cell "e" should automatically gets pasted below the "d" in sheet1

 

[Richyog]

No reply...............

 

[John Davis]

No reply...............

Maybe:

Sub Richyog()
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Sheet2")

    Columns("B:B").Insert shift:=xlToLeft
    
        With Range("B2:B" & lr)
        
            .Formula = "=VLOOKUP(A2,Sheet1!$A$2:$A$1000,1,false)"
            .Value = .Value
            .Replace What:="#N/A", Replacement:="Copy", Lookat:=xlWhole
            .AutoFilter Field:=1, Criteria1:="Copy"
            .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
            .AutoFilter
            .AutoFilter
            
        End With
        
    Columns("B:B").Delete shift:=xlToRight
    
End With

End Sub

 

[villy]

Try:

Sub ins()
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
With WorksheetFunction.Application
For Each cell In ws2.Range("A1:A10")
    If IsError(.Index(ws1.Range("A1:A10"), .Match(cell, ws1.Range("A1:A10"), 0), 1)) Then
        ws1.Range("A" & ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1).Value = cell
    End If
Next
End With
End Sub