# Macro to compare to sheets

#### Viperlx

##### New Member
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.

### 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
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
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``````

Replies
9
Views
103
Replies
5
Views
89
Replies
0
Views
40
Replies
15
Views
170
Replies
5
Views
62