Macro to compare to sheets

[Viperlx]

Hi,

I am have been trying to figure this out but I can't seem to get it. I am looking to compare 2 sheets and have the missing data inserted into sheet3.

I am looking to compare sheet 1 to sheet 2 and display in sheet 3 anything sheet 2 is missing from sheet 1.

Each sheet will only be using one column which will be A.

Thanks to anyone who can help me out.

 

[Jerry Sullivan]

Hi and Welcome,

You can copy and paste this formula into Cell A1 on Sheet3:
=IF(Sheet1!A1=Sheet2!A1,"",Sheet1!A1)

Then copy and paste the formula down into the cells below it on Column A of Sheet3 for as many rows as you have in Sheet1 and Sheet2.

The formula says: "if the values in this cell on Sheet1 and Sheet2 are the same then display a blank cell, otherwise display the value on Sheet1"

 

[mirabeau]

Would you like a VBA code to do this? Like, it can be useful if Sheet1 data are not in same order as Sheet2 data.

Sub themissing()
Dim lr1 As Long, lr2 As Long
Dim a, b, e, d As Object
lr1 = Sheets("sheet1").Range("A" & Rows.Count).End(3).Row
a = Sheets("sheet1").Range("A1").Resize(lr1)
lr2 = Sheets("sheet2").Range("A" & Rows.Count).End(3).Row
b = Sheets("sheet2").Range("A1").Resize(lr2)
ReDim u(1 To lr1, 1 To 1)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For Each e In b
    d(e) = 1
Next e
For Each e In a
    If d(e) <> 1 Then k = k + 1: u(k, 1) = e
Next e
Sheets("sheet3").Range("A2").Resize(k) = u
Sheets("sheet3").Range("A1") = "Everything sheet2 is missing from sheet1"
End Sub