check duplicate numbers

donlincolnmre2

Board Regular
Joined
Dec 23, 2006
Messages
142
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top