Looking for a VBA code to compare 3 different sheets

kfg1287

New Member
Joined
Mar 4, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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

thank you in advance
 

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
Joined
Mar 4, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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

dic.Add ar(i, 1), ar(i, 1)
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
 
Solution

Watch MrExcel Video

Forum statistics

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