remove duplicates

  • Thread starter Thread starter asol
  • Start date Start date
A

asol

hi all

i have in raw A phone list in raw B we have customer that ask to be removed
from call list
what can i do to have clean list in raw C
 
If you delete those records, they are gone forever. Maybe you just want to
hide them. Put the word 'Hide' in Column C, in the rows of clients that are
not supposed to be called, and run this:
Sub HideRows()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For rr = 1 To nLastRow
b = False
For cl = 3 To Columns.Count
If Cells(rr, cl).Text = "Hide" Then
b = True
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub
 
Hello:

Here is the code to do your copying. If a phone number is found in column
B, it is not copied to Column C:

Option Explicit

Public Sub LastRow()
Dim intLastRowColumnA As Long
Dim intLastRowColumnB As Long
Dim intColumnAPtr As Long
Dim intColumnBPtr As Long
Dim intColumnCPtr As Long
Dim MatchFound As Boolean

intColumnCPtr = 0

' **********************************
' Locate Last Row for Column A and B
' **********************************
intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row
intLastRowColumnB = Cells(Rows.Count, "B").End(xlUp).Row

' *****************************************************
' If A Phone Number Has A Match, Don't Copy To Column C
' *****************************************************
For intColumnAPtr = 1 To intLastRowColumnA
MatchFound = False
For intColumnBPtr = 1 To intLastRowColumnB
If Cells(intColumnAPtr, 1).Value = Cells(intColumnBPtr, 2) Then
MatchFound = True
Exit For
End If
Next intColumnBPtr
If Not MatchFound Then
intColumnCPtr = intColumnCPtr + 1
Cells(intColumnCPtr, 3).Value = Cells(intColumnAPtr, 1).Value
End If

Next intColumnAPtr

End Sub
 
Hello:
I think I misunderstood your question in my last post. This version will
see if there is a name in Column B, and if there is a name, it won't copy the
phone number to Column C.

Option Explicit

Public Sub LastRow()
Dim intLastRowColumnA As Long
Dim intColumnAPtr As Long
Dim intColumnCPtr As Long

intColumnCPtr = 0

' **********************************
' Locate Last Row for Column A
' **********************************
intLastRowColumnA = Cells(Rows.Count, "A").End(xlUp).Row

' ********************************************************
' If Column B Has A Name, Don't Copy The Phone Number to C
' ********************************************************
For intColumnAPtr = 1 To intLastRowColumnA
If Cells(intColumnAPtr, 2).Value = "" Then
intColumnCPtr = intColumnCPtr + 1
Cells(intColumnCPtr, 3).Value = Cells(intColumnAPtr, 1).Value
End If
Next intColumnAPtr

End Sub
 
The OP did not define the type of data that is in Col A or Col B. It is
difficult to tell if Col A consists of names and numbers or just name or
just numbers. It is inferred that Col B consists of names but it could be a
number or it could be both. In neither case does it define what data is
located where within the cell, nor how it is delimited if there is a
combination of names and numbers. So, IMHO, it is futile to offer any code
for accomplishing the task.
 
Back
Top