Delete duplicate entry from Column A if it exists in Column B

[daniejm]

Hi all,

Strangely, Google didn't pop anything up on this. Here's the problem:

I've got a master e-mail list that consists of nearly 64,000 records in Column A and an "unsubscribe" list of about 3,000 records in Column B. The task: to purge our unsubscribe requests (column B) from our master e-mail list (column A).

The two columns need to cross-referenced somehow. If a cell in Column B ("unsubscribe") matches a cell in Column A, the cell in Column A should be deleted. The cross-referencing should not be case sensitive.

What we've tried:
Putting everything into one list and using Remove Duplicates still leaves the original record in Column A, which defeats the purpose. Also, we can't use Conditional Formatting and then a Custom color Filter because the amount of data causes Excel to lock up.

Is there a better way?

Thanks in advance,

Joe

 

[Momentman]

Have you tried using vlookup?

Excel 2010
	A	B	C
1	EMAIL 1-LIST	EMAIl - LIST 2	Helper
2	jackson@yahoo.com	Johnson@yahoo.com	To be retained
3	Johnson@yahoo.com	Jane@hotmail.com	Johnson@yahoo.com
4	jane@hotmail.com		Jane@hotmail.com
5	jasmine@gmail.com		To be retained
6	jacobson@ulive.com		To be retained
Sheet6

Cell Formulas
Range	Formula
C2	=IFERROR(VLOOKUP(A2,$B$2:$B$3,1,0),"To be retained")

Any row that doesn't have "To be retained" can be filtered off and deleted

 

[timfritsch]

Here's a VBA solution that should work:

Sub delete_dups()
  Dim rowLoop As Long, foundRow As Long, test As String
  
  For rowLoop = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    test = Cells(rowLoop, "B")
    
    foundRow = 0
    
    On Error Resume Next
    foundRow = Columns("A").Find(test).Row
    On Error GoTo 0
    
    If Not foundRow = 0 Then
      Cells(foundRow, "A").Delete xlShiftUp
    End If
  Next rowLoop
End Sub

Tim

 

[daniejm]

Have you tried using vlookup?

Excel 2010

	A	B	C
EMAIL 1-LIST	EMAIl - LIST 2	Helper
jackson@yahoo.com	Johnson@yahoo.com	To be retained
Johnson@yahoo.com	Jane@hotmail.com	Johnson@yahoo.com
jane@hotmail.com	Jane@hotmail.com
jasmine@gmail.com	To be retained
jacobson@ulive.com	To be retained

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(A2,$B$2:$B$3,1,0),"To be retained")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Any row that doesn't have "To be retained" can be filtered off and deleted

Thanks for the suggestion, MomentMan, but the sheer volume of data and the sheer crappiness of my computer makes the program hard-lock.

 

[daniejm]

Tim,

This worked like a charm - exactly what we were looking for. Thank you so much!

- Joe

 

[punkasf]

Can someone give a brief explanation of how the code below is seeking duplicates in column A (foundRow = 0)? I am using the code for a similar task as OP. While it does work, I have come across a situation where a number is being deleted from the second column when there is no duplicate in the first column? I am wondering if other aspects are coming into play here that I am not considering.

Here's a VBA solution that should work:

Sub delete_dups()
  Dim rowLoop As Long, foundRow As Long, test As String
  
  For rowLoop = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    test = Cells(rowLoop, "B")
    
    foundRow = 0
    
    On Error Resume Next
    foundRow = Columns("A").Find(test).Row
    On Error GoTo 0
    
    If Not foundRow = 0 Then
      Cells(foundRow, "A").Delete xlShiftUp
    End If
  Next rowLoop
End Sub

Tim

 

[kalak]

Can someone give a brief explanation of how the code below is seeking duplicates in column A (foundRow = 0)? I am using the code for a similar task as OP. While it does work, I have come across a situation where a number is being deleted from the second column when there is no duplicate in the first column? I am wondering if other aspects are coming into play here that I am not considering.

You might try changing the line:

For rowLoop = 1 To Cells(Rows.Count, "B").End(xlUp).Row

to

For rowLoop = Cells(Rows.Count, "B").End(xlUp).Row to 1 Step -1

to avoid a problem that often happens when looping downwards and deleting upwards.


Alternatively, if lots of data, use a faster code such as:

Sub delete_dups_revisited()

Dim b As Boolean, d As Object, c
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1

For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
    d(c) = 1
Next c

For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If d(c.Value) = 1 Then x = x & "," & c.Address(0, 0): b = True
Next c

If b Then Range(Mid(x, 2)).Delete xlUp Else MsgBox "No matching values"

End Sub

This code may give an (easily fixed) error if there's lots, say 100 or more, values to delete.