Macro to compare to sheets

Viperlx

New Member
Joined
Mar 23, 2011
Messages
17
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Banned user
Joined
Nov 4, 2010
Messages
2,075
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,862
Latest member
lbisacca
Top