# Looking for a VBA code to compare 3 different sheets

#### kfg1287

##### New Member
Hello,

I am looking for a VBA code to compare 3 columns in 3 different sheets (sheet1,sheet2,sheet3).

Below is my formula that I am using but its just not efficient.
IF(ISNA(VLOOKUP(sheet1!B:B,sheet2!D:D,sheet3!C:C,1,FALSE)),sheet1!B:B,"")

My goal here is to compare the 3 sheets and if any results do not match sheet1 , paste them into column A sheet 4

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### kfg1287

##### New Member
Edit

here is the solution

Option Explicit

Sub NoMatches() 'Excel vba to remove duplicates.
Dim dic As Object
Dim ar As Variant
Dim ar1 As Variant
Dim var As Variant
Dim i As Long
Dim n As Long

Set dic=Createobject("Scripting.Dictionary")
dic.CompareMode=1
ar=Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
var=Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
ReDim ar1(1 To UBound(var), 1 To 1)

'Loop through ar and add to Dictionary.

For i=1 To UBound(ar)

If Not dic.exists(ar(i, 1)) Then

End If
Next i

'Identify non Matches

For i=1 To UBound(var)

If Not dic.exists(var(i, 1)) Then

n=n + 1
ar1(n, 1)=var(i, 1)
End If
Next i

'Output Results Remove any Duplication
Sheet3.Range("E2:E" & UBound(var)).Value=ar1
Range("E2:E" & UBound(var)).RemoveDuplicates 1
End Sub

Replies
3
Views
131
Replies
3
Views
108
Replies
22
Views
353
Replies
2
Views
88
Replies
8
Views
237

1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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

### Which adblocker are you using?

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

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