# 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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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"

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
7
Views
207
Replies
3
Views
273
Replies
5
Views
239
Replies
2
Views
243
Replies
3
Views
150

1,219,830
Messages
6,150,481
Members
450,967
Latest member
itzwinger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back