#16
|
|||
|
|||
Don't know the layout of your sheet but attached seems to work. Might be slow on a very large range though. |
#17
|
|||
|
|||
Yes you are right. It does work. I can't understand why it doesn't work on my spreadsheet. I replaced my columns with yours using your spreadsheet, ran the macro and nothing happened. I would post the columns but the phone numbers are confidential.
|
#18
|
|||
|
|||
Any leading or trailing spaces?
|
#19
|
|||
|
|||
@ cjamps
does this array approach work with the data you can't post or am I wasting my time trying to help ? Code:
Function onlynumbers(ByVal ref As String) ' remove all but digits from string Dim rx As Object Set rx = CreateObject("VBScript.RegExp") With rx .Pattern = "\D" .Global = True onlynumbers = .Replace(ref, "") End With End Function Sub cjamps_Delete_Duplicates() Dim lr As Long, i As Long, j As Long, k As Long, x As Long Dim ws As Worksheet, ray1, ray2 Dim dic As Object Set ws = ThisWorkbook.Sheets("Sheet1") Set dic = CreateObject("scripting.dictionary") Application.ScreenUpdating = False With ws lr = .Columns("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row ray1 = .Range("A2:C" & lr).Value For i = 1 To UBound(ray1, 1) For j = 1 To UBound(ray1, 2) ray1(i, j) = onlynumbers(ray1(i, j)) 'populate dictionary If ray1(i, j) <> "" Then dic(ray1(i, j)) = True Next j Next i ray2 = .Range("D2", ws.Range("D" & Rows.Count).End(xlUp)).Value For i = 1 To UBound(ray2, 1) ray2(i, 1) = onlynumbers(ray2(i, 1)) Next i 'check dictionary for ray2 elements 'MsgBox LBound(ray2) & vbLf & UBound(ray2) For k = 1 To UBound(ray2, 1) If dic.exists(Right(ray2(k, 1), 10)) Then ray2(k, 1) = "" Else dic(Right(ray2(k, 1), 10)) = True ray2(k, 1) = Format(ray2(k, 1), "000-000-0000") End If Next k 'clear original col D .UsedRange.Columns("D").Offset(1).ClearContents 'write ray2 to column D, omitting blanks x = 2 For i = 1 To UBound(ray2, 1) If ray2(i, 1) <> "" Then .Cells(x, 4) = ray2(i, 1) x = x + 1 End If Next i End With Application.ScreenUpdating = True End Sub |
#20
|
|||
|
|||
I think we have to find out why these macros are not working on my code but they are working on the sample.
I don't think that there are leading spaces but how could I find out? Let's take Jolivanes code for example. I am attaching a sample of initiating the debug. Using the Debug, expression c becomes rangerange and expression i goes up to 35 and then goes to the next i. Now what is happening is very strange. Columns E & F are filling up with the same numbers as in column D. [Column E without () such as 999-999-9999 and column F with () such as (999)999-9999] My column D contains 22 cells of numbers. Cells 23 to 33 fill up with (). I did not go through the whole debug but if I would run the macro without the debug, nothing would be changed. |
#21
|
|||
|
|||
Attach your workbook. Change numbers but not the formatting etc.
In an empty column to the right in the first cell, put "=Len(A1)" Drag formula 4 columns to the right and however many rows you have in Columns A to D All numbers should correspond to the amount you need it to be. 14 For Columns A, B and C and 12 For Column D |
#22
|
|||
|
|||
If you do have leading or trailing spaces, this should take care of it as part of the code.
Code:
Sub cjamps_B() Dim i As Long, c As Range, rng As Range Application.ScreenUpdating = False Set rng = Range("A2:D" & Cells(Rows.Count, "D").End(xlUp).Row) '<----- Range with ALL phone numbers rng.Value = Application.Trim(rng) '<---- Trims a range instead of looping cells 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 ActiveSheet.UsedRange.Columns("E:F").Offset(1).ClearContents Application.ScreenUpdating = True End Sub |
#23
|
|||
|
|||
Attached please find the uploaded file. I hope I replaced all the phone numbers to protect the members. I know that there are at least 2 duplicates in column D. Please tell me if the macro works for you.
Thanx |
#24
|
|||
|
|||
The array macro leaves only these of column D
and only one of each 999-999-5977 999-999-3367 999-999-0575 999-999-9429 999-999-2672 999-999-6295 is this correct ? Last edited by NoSparks; 12-15-2017 at 04:25 PM. Reason: included file |
#25
|
|||
|
|||
I think I understand it different now.
If you have doubles, triples or more in Column D, you want to delete the double and triple or more if you have more but leave one. Is that the idea? |
#26
|
|||
|
|||
In your attached workbook in sheet "Tiferes Miriam Contacts" this leaves only unique values in Column D
Code:
Sub Delete_Doubles_Miriam_Contacts_Sheet() Dim i As Long, c As Range, rng As Range, lr As Long lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row Application.ScreenUpdating = False Set rng = Range("A2:D" & lr) rng.Value = Application.Trim(rng) For Each c In Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(2) c.Offset(, 1).Formula = "=IF(LEN(RC[-1])=13,RIGHT(RC[-1],12),RC[-1])" c.Offset(, 1).Value = c.Offset(, 1).Value c.Offset(, 2).Formula = "= ""(""&LEFT(RC[-1],3)&"")""&"" ""&MID(RC[-1],5,3)&"" ""&MID(RC[-1],9,4)" c.Offset(, 2).Value = c.Offset(, 2).Value Next c For i = 3 To Cells(Rows.Count, 4).End(xlUp).Row If Cells(i, 4) <> "" And WorksheetFunction.CountIf(Range("$F$" & i & ":$F" & Cells(Rows.Count, 4).End(xlUp).Row), Cells(i, 6)) > 1 Then Cells(i, 4).ClearContents Next i ActiveSheet.UsedRange.Columns("E:F").Offset(1).ClearContents Application.ScreenUpdating = True End Sub |
#27
|
|||
|
|||
NoSparks,
THANX TONS!!! That's it. You got it. Sorry this took so long, I guess I really did not explain myself properly. I appreciate all your time and effort. Jolivines, Yes. But also if it exists in column a,b or c it should be deleted as well. [ONLY the phone number in column D should be deleted, the first 3 rows must be untouched.] I ran your macro on the attached file and highlighted the duplicates that the macro should remove because they exist in the previous columns. |
#28
|
|||
|
|||
You're welcome, glade I could help. Please log in and use the Forum Tools drop down to mark the thread as Solved, thanks.
Also, read this in case you should be asking for assistance again. It's a requirement of the rules at virtually all the forums including this one and both MrE and EF. |
|
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 |