OK

Regarding the assumptions and criteria:
With your list in Cells A3:D4000
and list headings in cells A3:D3 (Title, FName, Lname, Location)
F1: DupTest
F2: =COUNTIF(C$3:C4,C4)1

1)As you know, for Advanced Filters to work, they need column headings on
the list. Hence the: Title, FName, Lname, Location

2)The range A3:D4000 is just as an example, but I made sure the data rows of
the list (under the column headings) would be under the criteria rows so they
wouldn't be deleted in the duplicate removal.

3)Since I used a formulaic criteria, I couldn't use a list column heading. I
could have left cell F1 blank, but instead i just used a descriptive heading
that didn't match any of the list headings.

4)Notice the single dollar sign ($) in the criteria forumula. That formula
will play out this way:

Testing cell C4, the formula will be: =COUNTIF(C$3:C4,C4)1
Testing cell C5, the formula will be: =COUNTIF(C$3:C5,C5)1
Testing cell C6, the formula will be: =COUNTIF(C$3:C6,C6)1
etc

Consequently, the first time the formula encounters a value, its count will
be 1. All other instances will have a count of greater than 1.

The Advanced filter only displays records where the item count is greater
than 1....the duplicates.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Danielle" wrote:

Hi Ron

Thanks for your answer. I am afraid I dont understand some parts of your
reply, though - can you clarify for me?:

How would the list be in D4000 when there are only 3 items (in the example)?
Or is this based on there being 4000 records?

What is F1 and F2 referring to? What do I have to do with them?

If you can let me know and I can try it again :-)

Thanks
Danielle

"Ron Coderre" wrote:

Perhaps something like this:

With your list in Cells A3:D4000
and list headings in cells A3:D3 (Title, FName, Lname, Location)

F1: DupTest
F2: =COUNTIF(C$3:C4,C4)1

Select your list (A3:D4000)
DataFilterAdvanced Filter
List Range: (already selected)
Criteria Range: $F$1:$F$2

Click the [OK] button to filter the list in place.

That will hide the first occurrence of a LName and display only the
duplicates.

Select the visible cells below the column headings
Edit|Delete (you can only delete entire rows in a filtered list)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Danielle" wrote:

Hi

I am running v.2000 and need some help. I have an excel sheet containing
multiple columns and 1000s of rows.

I know how to delete rows which are completely identical (advanced filter
etc) but I need to know the following:

If there are two rows which are generally different (and so won't be deleted
by the advanced filter method), but each have an identical value in column D,
how would I get rid of the entire row containing the duplicate cell?

So for example if I had three rows as follows:

Mr | Joe | Bloggs | London
Mr | Tom | Jones | Glasgow
Mr | Ben | Jones | Essex

None of these would count as traditional duplicates via the advanced filter
method.

But what if I wanted to get rid of duplicate surnames - in the above example
I would ideally want to be left with just one of the "Jones" rows - how can I
do this?

It is worth noting that I have only basic experience with excel and so am
not familiar with macros etc - I tried one macro tutorial that didnt work :-S

Hope someone can help!

Thanks
Danielle