Deleting duplicates in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Susan Woods - 09 May 2008 15:31 GMT

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?

Reply to this Message

Pete_UK - 09 May 2008 15:39 GMT

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 <SusanWo...@discussions.microsoft.com>
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
[quoted text clipped - 5 lines]
> this.
> Is there a way to do this other than manually deleting both entries?

Reply to this Message

Gary''s Student - 09 May 2008 15:44 GMT

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

Signature

Gary''s Student - gsnu200785

Reply to this Message

Gaurav - 09 May 2008 15:47 GMT

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.

>I know that to delete duplicates in a spreadsheet I use Advance
>Filter-unique
[quoted text clipped - 8 lines]
> this.
> Is there a way to do this other than manually deleting both entries?

Reply to this Message

Dan R. - 09 May 2008 15:50 GMT

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 <SusanWo...@discussions.microsoft.com>
wrote:

Reply to this Message