Remove duplicate phone numbers on same row

Jaffey · Apr 19th 2015

I have a table with a large number of customer ID's in column A and associated customer data in columns to the right.
10 to 15 columns will contain phone numbers. The problem is the same phone number will appear in more than one column.
I would like to delete the duplicates keeping just the first occurrence of the number. (closest to column A).
All of the columns have labels and columns containing phone numbers have "Phone" somewhere in the label. (the column order changes sometimes so I can't specify by column number)

Any help greatly appreciated!! (sample data attached)

forum.ozgrid.com/index.php?attachment/64842/

**Batman** · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

Hi Jaffey,

Your solution might depend on how frequently you want to do this. If it's a one-off, you can create a new set of columns to the right of your existing data and use this formula for the first phone number:

=IF(COUNTIF($D$3:D3,D3)=1,D3,"")

Fill this to the right for 7 columns, and then down as many rows as necessary. Then copy and paste special / values over the original.

Jaffey · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

Thank you for responding! Unfortunately I have to do this every day and there 10's of 1000's of rows to deal with in multiple list, plus there are many other columns of various customer data inserted between the phone number columns. Nice formula though, works very nicely

jindon · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

See if this works

Code

Sub test()
    Dim a, x, i As Long, txt As String
    With Cells(1).CurrentRegion
        a = .Value
        ReDim x(1 To .Rows.Count)
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.+" & Chr(2) & ")?(\d+)" & Chr(2) & _
            "(.+" & Chr(2) & ")?(\2)(" & Chr(2) & ".*)?"
            For i = 1 To UBound(a, 1)
                txt = Join(Application.Index(a, i, 0), Chr(2))
                Do While .test(txt)
                    txt = .Replace(txt, "$1$2" & Chr(2) & "$3$5")
                Loop
                x(i) = Split(txt, Chr(2))
            Next
        End With
        .Value = Application.Index(x, 0, 0)
    End With
End Sub

Display More

Jaffey · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

Thank you so much Jindon. Unfortunately I am getting a type mismatch error on the following line when I use it on real data:

txt = Join(Application.Index(a, i, 0), Chr(2))

The data is in Excel table format. Would that make a difference? I tried converting it to a range but that didn't help.

When I try it with the test data I provided it's working but I noticed that with one of the phone numbers it truncated the area code of the firs occurrence of the number.

i.e. 905747899 in Cell H4 becomes 7477899 after I run it.

jindon · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

I need to see your workbook then...

Jaffey · Apr 19th 2015

Re: Remove duplicate phone numbers on same row

When I run it on the raw csv files it runs fine. I have another macro that converts the csv files to tables upon opening but when I turn that off it elimiates the type mismatch error. The only issue remaining is that it seems to be corrupting some of the phone numbers. Are you seeing that with the test data provided? When I run it on the test data the phone number in H4 gets shortened.

jindon · Apr 20th 2015

Re: Remove duplicate phone numbers on same row

Yep, it should be like this

Code

Sub test()
    Dim a, x, i As Long, txt As String
    With Cells(1).CurrentRegion
        a = .Value
        ReDim x(1 To .Rows.Count)
        With CreateObject("VBScript.RegExp")
            .Pattern = "(.+)?(" & Chr(2) & "(\d+)" & Chr(2) & ")(.*)(\3)(.*)"
            For i = 1 To UBound(a, 1)
                txt = Join(Application.Index(a, i, 0), Chr(2))
                Do While .test(txt)
                    txt = .Replace(txt, "$1$2$4$6")
                Loop
                x(i) = Split(txt, Chr(2))
            Next
        End With
        .Value = Application.Index(x, 0, 0)
    End With
End Sub

Display More

Jaffey · May 10th 2015

Re: Remove duplicate phone numbers on same row

Hi Jindon, I've been using your code and noticed that it is truncating some of the phone numbers. It also seems to be editing columns that do not contain phone numbers. I've attached some sample data if you would be so kind to take a look.forum.ozgrid.com/index.php?attachment/65156/

jindon · May 10th 2015

Re: Remove duplicate phone numbers on same row

Now you are asking totally different question...

See if this works.

Code

Sub test()
    With Cells(1).CurrentRegion
        x = Filter(Evaluate("if(isnumber(search(""phone""," & .Rows(1).Address & _
        ")),column(" & .Rows(1).Address & "),char(2))"), Chr(2), 0)
        a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), x)
        With CreateObject("Scripting.Dictionary")
            For i = 2 To UBound(a, 1)
                .RemoveAll
                For ii = 1 To UBound(a, 2)
                    If a(i, ii) <> "" Then
                        If Not .exists(a(i, ii)) Then
                            .Item(a(i, ii)) = Empty
                        Else
                            a(i, ii) = Empty
                        End If
                    End If
                Next
            Next
        End With
        For i = LBound(x) To UBound(x)
            .Columns(Val(x(i))).Value = Application.Index(a, 0, i + 1)
        Next
    End With
End Sub

Display More

Jaffey · May 10th 2015

Re: Remove duplicate phone numbers on same row

Working perfectly!! Thank you so much!! (please note I had to add "Dim a, x, i,ii As Long, txt As String" at the beginning)

Remove duplicate phone numbers on same row

Participate now!

Share

Users Viewing This Thread