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?

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

On May 9, 3:31*pm, Susan Woods
wrote:
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?

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


--
Gary''s Student - gsnu200785


"Susan Woods" wrote:

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?

Assuming your list of patrons is in A1:A100 and the list of the ones who
have purchased tickets is in column B. Select A1go to formatconditional
formattingselect Formula Isand then enter this formula

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

now click FormatPatterns tabselect a color(red for example)OK

Now copy A1select the entire column Aright clickpaste specialformats.

Now send invitations to the ones that are not Red.

Hope that helps.


"Susan Woods" wrote in message
...
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?

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

--
Dan

On May 9, 9:31*am, Susan Woods
wrote:
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?