Removing duplicate contacts

[linaeum66]

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @Page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style>[TABLE="width: 261"]
<!--StartFragment--> <colgroup><col width="87" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87, align: left"]A[/TD]
[TD="width: 87, align: left"]B[/TD]
[TD="width: 87, align: left"]C[/TD]
[/TR]
[TR]
[TD="align: left"]LASTNAME[/TD]
[TD="align: left"]EMAIL[/TD]
[TD="align: left"]EMAIL[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


[FONT=&quot]I have a sheet set up as seen above, a contact in A/B and a third column C that is a list of email addresses. Is it possible to make a macro that removes a contact in Column A and B if the same email address in Column B also occurs in a second list of emails in Column C? In this case I am interested in removing (or clearing) both the LASTNAME and EMAIL from column A and B if C=B.[/FONT]
Thanks so much for your help!

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @Page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style>

 

[JackDanIce]

Is this on a row by row basis? e.g. if B2 = C2 then clear A2 and B2?

Does it need to be a macro?

In column D2 enter:

=--(B2=C2)

,

Drag the formula to the last row against column C
Filter columns A:D where column D equals 1
Clear the visible cells in Columns A and B

 

[linaeum66]

Is this on a row by row basis? e.g. if B2 = C2 then clear A2 and B2?

Does it need to be a macro?

In column D2 enter:

=--(B2=C2)

,

Drag the formula to the last row against column C
Filter columns A:D where column D equals 1
Clear the visible cells in Columns A and B

I'm sorry I forgot to mention that it isn't by row. I would like it to clear A/B if B = any value in the C column.

 

[JackDanIce]

My suggestion still works if you change the formula to:

=ISERROR(VLOOKUP(B2,$C$1:$C$500,1,0))

Then filter column D for values of FALSE and clear the visible cells of columns A and B.
Out of curiosity, why does this need to be a macro?

 

[linaeum66]

My suggestion still works if you change the formula to:

=ISERROR(VLOOKUP(B2,$C$1:$C$500,1,0))

Then filter column D for values of FALSE and clear the visible cells of columns A and B.
Out of curiosity, why does this need to be a macro?

Thank you very much for your help. The main reason I am interested in a macro is that there are thousands of contacts in my sheet, and I routinely do this. It would be very helpful to have a macro that can do it in a single click instead of copying and pasting and filtering.

 

[linaeum66]

Removing duplicate emails in adjacent columns

I have a list of emails in Column B and a second list in Column D. Is it possible to make a macro to detect if there is a email that appears BOTH columns (anywhere in the column, not just comparing those in same row) and to clear the cell in Column D if there is a duplicate? I would prefer a macro instead of a formula, because I am doing this routinely and on thousands of address and would greatly prefer the one-click abilities of a macro. Thanks so much!

 

[L. Howard]

Re: Removing duplicate emails in adjacent columns

Try this.

Howard

Option Explicit

Sub Find_My_B_D()
Dim MyB As Range
Dim MyD As Range
Dim c As Range
Dim z As Variant
Dim bRow As Long, dRow As Long

bRow = Cells(Rows.Count, "B").End(xlUp).Row
dRow = Cells(Rows.Count, "D").End(xlUp).Row

Set MyB = Range("B1:B" & bRow)
Set MyD = Range("D1:D" & dRow)

For Each c In MyD

    With MyB

        Set z = .Find(What:=c)

        If Not z Is Nothing Then
        c.ClearContents
        End If
        
    End With

Next c

End Sub

 

[Macropod]

Re: Removing duplicate emails in adjacent columns

linaeum66: As per Rule 12 - http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html - kindly don't start threads on what are essentially duplicates of the same topic.

I have merged the two threads.