check duplicate numbers

[donlincolnmre2]

Hello

I have 2 sheet in a excel file

sheet 1 contain bunch of phone number and its the master file

sheet 2 contain some number which are duplicate in master file

I'm looking for a macro that will look at all the number in SHEET 1 and if they are found in SHEET 2, then then macro should delete those numbers from SHEET 2

any help would be greatly appreciated.

thanks.

 

[Andrew Poulsom]

Where are the phone numbers in each worksheet?

 

[donlincolnmre2]

Where are the phone numbers in each worksheet?

yeah they are both in COLUMN A.

 

[Ragnar1211]

do you want the whole row deleted, the cell contents deleted and moved, or just clear the contents?

Provide an example of how you would manually deal with the problem please.

 

[donlincolnmre2]

here is some dummy data

SHEET 1 DATA SHEET 2 DATA
1232577354 2516767757
1237591496 1087287081
1087287081 2323453235
1086448902 2435545466
1502428266 1502428266
1529266531 6456565666
1222077134 1222077134
1095476066 3451234134
1092056444 2342135153

 

[Ragnar1211]

Well since you didn't answer my questions, i'm assuming you are just clearing the contents... hear is a first draft for you to play with, I'm sure there are more elegant approaches:

Sub chck()

Dim mster As Variant
Dim rev As Variant
Dim i As Integer
Dim j As Integer
Dim lr1 As Integer
Dim lr2 As Integer
Dim cnt As Integer
Dim sht1 As String
Dim sht2 As String

sht1 = "Master" 'Input sheet name where the master list is contained
sht2 = "Review" 'Input sheet name where the list that requires review is contained

'Get last row for Sht1
    With Worksheets(sht1)
            lr1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
 
'Get last row for Sht2
    With Worksheets(sht2)
            lr2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

'Get data from sht1 and sht2
    mster = Worksheets(sht1).Range("A1").Resize(lr1, 1)
    rev = Worksheets(sht2).Range("A1").Resize(lr2, 1)

'Identify duplicates and clear them
    For i = 1 To lr2 'Cycle through each item in sht2
        cnt = 0
        For j = 1 To lr1
            If mster(j, 1) = rev(i, 1) Then
                cnt = cnt + 1
            End If
            
        Next
    
        If cnt > 0 Then
            Worksheets(sht2).Range("A" & i).ClearContents
        End If
    
    Next



End Sub