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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,452
Members
430,549
Latest member
jayjay2022

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?

Disable AdBlock

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
Top