Macro to check against Columns & Delete Duplicates

NoSparks · #1 12-12-2017, 10:11 AM

Another approach is to use in memory arrays.

jolivanes · #2 12-12-2017, 10:24 AM

My bad.
I misread the first post. I understood it to mean 3 columns on the same row.
My apologies.

jolivanes · #3 12-12-2017, 09:12 PM

Another long winded approach that might work.
Try on a copy of your original first.
Columns A, B and C have phone numbers to be compared to.
Column D has the phone numbers without bracketed area codes and a possible 1 in front
Columns E and F are empty and free to use.

Code:

Sub cjamps()
Dim i As Long, c As Range
Application.ScreenUpdating = False
    With Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        .Offset(, 1).Formula = "=IF(LEN(RC[-1])=13,RIGHT(RC[-1],12),RC[-1])"
        .Offset(, 1).Value = .Offset(, 1).Value
        .Offset(, 2).Formula = "= ""(""&LEFT(RC[-1],3)&"")""&"" ""&MID(RC[-1],5,3)&"" ""&MID(RC[-1],9,4)"
        .Offset(, 2).Value = .Offset(, 2).Value
    End With
    For Each c In Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
            If WorksheetFunction.CountIf(Range(c.Offset(, -5).Address & ":" & c.Offset(, -3).Address), c.Value) <> 0 Then c.Offset(, -2).ClearContents: Exit For
        Next i
    Next c
    Columns("E:F").ClearContents
Application.ScreenUpdating = True
End Sub

cjamps · #4 12-13-2017, 07:20 AM

jolivanes,

I tried running the macro but it didn't delete the duplicates.

jolivanes · #5 12-13-2017, 08:11 AM

For me it deleted the 1's where there were any and it deletes the dups.
I'll have a look later on.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA: Delete duplicates in each row	bandaanders	Excel Programming	2	09-02-2015 08:15 AM
Excel vba to check to check if two columns are empty	subspace3	Excel Programming	5	07-09-2015 04:45 PM
Macro to keep first instance and remove duplicates in certain column	zhead	Excel	2	03-18-2015 10:16 AM
find and delete duplicates	rcVBA	Word VBA	4	05-15-2013 03:08 PM
Deleting Duplicates in Macro	jillapass	Excel Programming	1	01-11-2012 10:02 AM