Removing duplicates from one sheet comparing to another

[shahin]

I would like to create a macro which is capable of comparing "Names" and "Scroes" listed in Column "A" and Column "B" in "Sheet2" with those in Column "A" and Column "B" in "Sheet1". If there are duplicate values available, the macro should be able to kick those out from "Sheet2" as "Sheet1" is the main document holder and can't be removed. Once again: whether the document listed in "Sheet2" are themselves unique they may no longer be when compared to "Sheet1" so they should be removed from "Sheet2".

I've tried a macro (fully influenced by Narayan) which is capable of removing duplicates from "Sheet1" but my goal here is to make the macro start it's operation from "Sheet2" and compare its' values with "Sheet1". If any value matches, kick it from "Sheet2".

Here is what I've tried which is capable of removing duplicates from "Sheet1":

Sub RemoveDups()
    Dim lrow&, R&, N&, dict As Object, cval As Variant
   
    lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).row
   
    Set dict = New Scripting.Dictionary
   
    For R = lrow To 2 Step -1
        With Sheet1.Cells(R, 1)
            cval = .Value          ''cval stands for current value
            If dict.Exists(cval) Then
              .EntireRow.Delete
            Else
              N = N + 1: dict.Add cval, N + 1
            End If
        End With
    Next R
End Sub

I'm attaching two "text" files pretending that the "text1" is holding what "Sheet1" holds and "text2" is holding what "Sheet2" does. Thanks in advance.

 

[Marc L]

Better is to attach a source workbook and an expected result workbook …​

 

[shahin]

Attached two workbooks. The workbook named "macro_to_be_applied" contains duplicate values in "Sheet2" which should be removed comparing to "Sheet1". Attached another workbook named "Result" which is how the workbook looks like when the operation is done. Thanks.

 

[Marc L]

According to your last attachment (with a not accurate result),
at beginner level just with Excel basics :​

Sub Demo1()
     Application.ScreenUpdating = False
With Sheet1.UsedRange.Resize(, 3).Columns
    .Item(3).Formula = "=ISNUMBER(MATCH(A1," & Sheet2.UsedRange.Columns(1).Address(External:=True) & ",0))"
    .Cells(3).Clear
    .Sort .Cells(3), xlAscending, Header:=xlYes
     V = Application.Match(True, .Item(3), 0)
     If IsNumeric(V) Then .Rows(V & ":" & .Rows.Count).Clear
    .Item(3).Clear
End With
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[shahin]

Yes it did the job. However, this time I find it very difficult to understand your code.

 

[Marc L]

This code just reproduces what you can yet achieve manually
in less than a minute just with Excel basics : formula & sort !
What you can get yourself just activating the Macro Recorder
like any beginner can (must !) do …
So the easiest way and faster than a code deleting a line at a time,
even operating manually ‼
Before trying to code, better is to learn Excel basics …
Before to code, just ask yourself « What can Excel do for my need ? »
(TEBV rule …)​