Deleting duplicates in Excel

[Susan Woods]

I know that to delete duplicates in a spreadsheet I use Advance Filter-unique
records only. However, some of the lists I use requires that both of the
items in that list need to be deleted.
For instance, I have a list of all our patrons. We have a separate list of
patrons that already purchased tickets to the next performance. We need to
send an invitation to our patrons to attend this performance. Obviously, we
don't want to send invitations to those who have already purchased tickets to
this.
Is there a way to do this other than manually deleting both entries?

 

[Pete_UK]

You could use a MATCH formula to see if a patron is listed as already
having bought a ticket and return, say, Yes or No. Then you can filter
for the Yes values and delete them all in one operation.

Hope this helps.

Pete

 

[Gary''s Student]

Say the data in column A is:

data
1
1
2
2
3
3
4
5
6
7
7
8
9
10

Put a label in B1 and in B2:
=COUNTIF(A:A,A2) and copy down. We see:

data mark
1 3
1 3
2 2
2 2
3 2
3 2
4 1
5 1
6 1
7 2
7 2
8 1
9 1
10 1

Just put an AutoFilter on column B to view only the 1's:

data mark
4 1
5 1
6 1
8 1
9 1
10 1

 

[Gaurav]

Assuming your list of patrons is in A1:A100 and the list of the ones who
have purchased tickets is in column B. Select A1>go to format>conditional
formatting>select Formula Is>and then enter this formula

=COUNTIF($B$1:$B$100,A1)>0

now click Format>Patterns tab>select a color(red for example)>OK

Now copy A1>select the entire column A>right click>paste special>formats.

Now send invitations to the ones that are not Red.

Hope that helps.

 

[Dan R.]

Or via code:

Sub test()
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lrow To 1 Step -1
Set found = Range("B1:B1000").Find( _
What:=Cells(i, 1).Value, _
LookIn:=xlValues)
If Not found Is Nothing Then
Cells(i, 1).Delete
End If
Next i
End Sub