software

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 quot;Jonesquot; 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

Perhaps something like this:

With your list in Cells A34000
and list headings in cells A33 (Title, FName, Lname, Location)

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

Select your list (A34000)
Datagt;Filtergt;Advanced 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-Proquot;Daniellequot; wrote:

gt; Hi
gt;
gt; I am running v.2000 and need some help. I have an excel sheet containing
gt; multiple columns and 1000s of rows.
gt;
gt; I know how to delete rows which are completely identical (advanced filter
gt; etc) but I need to know the following:
gt;
gt; If there are two rows which are generally different (and so won't be deleted
gt; by the advanced filter method), but each have an identical value in column D,
gt; how would I get rid of the entire row containing the duplicate cell?
gt;
gt; So for example if I had three rows as follows:
gt;
gt; Mr | Joe | Bloggs | London
gt; Mr | Tom | Jones | Glasgow
gt; Mr | Ben | Jones | Essex
gt;
gt; None of these would count as traditional duplicates via the advanced filter
gt; method.
gt;
gt; But what if I wanted to get rid of duplicate surnames - in the above example
gt; I would ideally want to be left with just one of the quot;Jonesquot; rows - how can I
gt; do this?
gt;
gt; It is worth noting that I have only basic experience with excel and so am
gt; not familiar with macros etc - I tried one macro tutorial that didnt work :-S
gt;
gt; Hope someone can help!
gt;
gt; Thanks
gt; Danielle

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

quot;Ron Coderrequot; wrote:

gt; Perhaps something like this:
gt;
gt; With your list in Cells A34000
gt; and list headings in cells A33 (Title, FName, Lname, Location)
gt;
gt; F1: DupTest
gt; F2: =COUNTIF(C$3:C4,C4)gt;1
gt;
gt; Select your list (A34000)
gt; Datagt;Filtergt;Advanced Filter
gt; List Range: (already selected)
gt; Criteria Range: $F$1:$F$2
gt;
gt; Click the [OK] button to filter the list in place.
gt;
gt; That will hide the first occurrence of a LName and display only the
gt; duplicates.
gt;
gt; Select the visible cells below the column headings
gt; Edit|Delete (you can only delete entire rows in a filtered list)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Daniellequot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt; I am running v.2000 and need some help. I have an excel sheet containing
gt; gt; multiple columns and 1000s of rows.
gt; gt;
gt; gt; I know how to delete rows which are completely identical (advanced filter
gt; gt; etc) but I need to know the following:
gt; gt;
gt; gt; If there are two rows which are generally different (and so won't be deleted
gt; gt; by the advanced filter method), but each have an identical value in column D,
gt; gt; how would I get rid of the entire row containing the duplicate cell?
gt; gt;
gt; gt; So for example if I had three rows as follows:
gt; gt;
gt; gt; Mr | Joe | Bloggs | London
gt; gt; Mr | Tom | Jones | Glasgow
gt; gt; Mr | Ben | Jones | Essex
gt; gt;
gt; gt; None of these would count as traditional duplicates via the advanced filter
gt; gt; method.
gt; gt;
gt; gt; But what if I wanted to get rid of duplicate surnames - in the above example
gt; gt; I would ideally want to be left with just one of the quot;Jonesquot; rows - how can I
gt; gt; do this?
gt; gt;
gt; gt; It is worth noting that I have only basic experience with excel and so am
gt; gt; not familiar with macros etc - I tried one macro tutorial that didnt work :-S
gt; gt;
gt; gt; Hope someone can help!
gt; gt;
gt; gt; Thanks
gt; gt; Danielle

OK

Regarding the assumptions and criteria:
gt;gt;With your list in Cells A34000
gt;gt;and list headings in cells A33 (Title, FName, Lname, Location)
gt;gt;F1: DupTest
gt;gt;F2: =COUNTIF(C$3:C4,C4)gt;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 A34000 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)gt;1
Testing cell C5, the formula will be: =COUNTIF(C$3:C5,C5)gt;1
Testing cell C6, the formula will be: =COUNTIF(C$3:C6,C6)gt;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-Proquot;Daniellequot; wrote:

gt; Hi Ron
gt;
gt; Thanks for your answer. I am afraid I dont understand some parts of your
gt; reply, though - can you clarify for me?:
gt;
gt; How would the list be in D4000 when there are only 3 items (in the example)?
gt; Or is this based on there being 4000 records?
gt;
gt; What is F1 and F2 referring to? What do I have to do with them?
gt;
gt; If you can let me know and I can try it again :-)
gt;
gt; Thanks
gt; Danielle
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Perhaps something like this:
gt; gt;
gt; gt; With your list in Cells A34000
gt; gt; and list headings in cells A33 (Title, FName, Lname, Location)
gt; gt;
gt; gt; F1: DupTest
gt; gt; F2: =COUNTIF(C$3:C4,C4)gt;1
gt; gt;
gt; gt; Select your list (A34000)
gt; gt; Datagt;Filtergt;Advanced Filter
gt; gt; List Range: (already selected)
gt; gt; Criteria Range: $F$1:$F$2
gt; gt;
gt; gt; Click the [OK] button to filter the list in place.
gt; gt;
gt; gt; That will hide the first occurrence of a LName and display only the
gt; gt; duplicates.
gt; gt;
gt; gt; Select the visible cells below the column headings
gt; gt; Edit|Delete (you can only delete entire rows in a filtered list)
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Daniellequot; wrote:
gt; gt;
gt; gt; gt; Hi
gt; gt; gt;
gt; gt; gt; I am running v.2000 and need some help. I have an excel sheet containing
gt; gt; gt; multiple columns and 1000s of rows.
gt; gt; gt;
gt; gt; gt; I know how to delete rows which are completely identical (advanced filter
gt; gt; gt; etc) but I need to know the following:
gt; gt; gt;
gt; gt; gt; If there are two rows which are generally different (and so won't be deleted
gt; gt; gt; by the advanced filter method), but each have an identical value in column D,
gt; gt; gt; how would I get rid of the entire row containing the duplicate cell?
gt; gt; gt;
gt; gt; gt; So for example if I had three rows as follows:
gt; gt; gt;
gt; gt; gt; Mr | Joe | Bloggs | London
gt; gt; gt; Mr | Tom | Jones | Glasgow
gt; gt; gt; Mr | Ben | Jones | Essex
gt; gt; gt;
gt; gt; gt; None of these would count as traditional duplicates via the advanced filter
gt; gt; gt; method.
gt; gt; gt;
gt; gt; gt; But what if I wanted to get rid of duplicate surnames - in the above example
gt; gt; gt; I would ideally want to be left with just one of the quot;Jonesquot; rows - how can I
gt; gt; gt; do this?
gt; gt; gt;
gt; gt; gt; It is worth noting that I have only basic experience with excel and so am
gt; gt; gt; not familiar with macros etc - I tried one macro tutorial that didnt work :-S
gt; gt; gt;
gt; gt; gt; Hope someone can help!
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; Danielle

Hello again,

Thanks - it's much clearer now and I understand what you mean.

However I still dont seem to be getting the right result

I am basically writing the TestDup in the header, then putting the formula
into the F2 column and the copying it down for each record.

I have tried various methods including selecting and not selecting the F
colum, choosing in-place or copy to another location, and choosing and not
choosing unique fields only, but dont seem to be getting anywhere.

Its worth noting that for the F fields with the formula in, it comes back
with either a FALSE or TRUE value, but only a couple of my records are
showing true, and they arent duplicates. The ducplicates that are there still
say False. Could this be a problem?

Sorry to be such a difficult one!

Danielle

quot;Ron Coderrequot; wrote:

gt; OK
gt;
gt; Regarding the assumptions and criteria:
gt; gt;gt;With your list in Cells A34000
gt; gt;gt;and list headings in cells A33 (Title, FName, Lname, Location)
gt; gt;gt;F1: DupTest
gt; gt;gt;F2: =COUNTIF(C$3:C4,C4)gt;1
gt;
gt; 1)As you know, for Advanced Filters to work, they need column headings on
gt; the list. Hence the: Title, FName, Lname, Location
gt;
gt; 2)The range A34000 is just as an example, but I made sure the data rows of
gt; the list (under the column headings) would be under the criteria rows so they
gt; wouldn't be deleted in the duplicate removal.
gt;
gt; 3)Since I used a formulaic criteria, I couldn't use a list column heading. I
gt; could have left cell F1 blank, but instead i just used a descriptive heading
gt; that didn't match any of the list headings.
gt;
gt; 4)Notice the single dollar sign ($) in the criteria forumula. That formula
gt; will play out this way:
gt;
gt; Testing cell C4, the formula will be: =COUNTIF(C$3:C4,C4)gt;1
gt; Testing cell C5, the formula will be: =COUNTIF(C$3:C5,C5)gt;1
gt; Testing cell C6, the formula will be: =COUNTIF(C$3:C6,C6)gt;1
gt; etc
gt;
gt; Consequently, the first time the formula encounters a value, its count will
gt; be 1. All other instances will have a count of greater than 1.
gt;
gt; The Advanced filter only displays records where the item count is greater
gt; than 1....the duplicates.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Daniellequot; wrote:
gt;
gt; gt; Hi Ron
gt; gt;
gt; gt; Thanks for your answer. I am afraid I dont understand some parts of your
gt; gt; reply, though - can you clarify for me?:
gt; gt;
gt; gt; How would the list be in D4000 when there are only 3 items (in the example)?
gt; gt; Or is this based on there being 4000 records?
gt; gt;
gt; gt; What is F1 and F2 referring to? What do I have to do with them?
gt; gt;
gt; gt; If you can let me know and I can try it again :-)
gt; gt;
gt; gt; Thanks
gt; gt; Danielle
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Perhaps something like this:
gt; gt; gt;
gt; gt; gt; With your list in Cells A34000
gt; gt; gt; and list headings in cells A33 (Title, FName, Lname, Location)
gt; gt; gt;
gt; gt; gt; F1: DupTest
gt; gt; gt; F2: =COUNTIF(C$3:C4,C4)gt;1
gt; gt; gt;
gt; gt; gt; Select your list (A34000)
gt; gt; gt; Datagt;Filtergt;Advanced Filter
gt; gt; gt; List Range: (already selected)
gt; gt; gt; Criteria Range: $F$1:$F$2
gt; gt; gt;
gt; gt; gt; Click the [OK] button to filter the list in place.
gt; gt; gt;
gt; gt; gt; That will hide the first occurrence of a LName and display only the
gt; gt; gt; duplicates.
gt; gt; gt;
gt; gt; gt; Select the visible cells below the column headings
gt; gt; gt; Edit|Delete (you can only delete entire rows in a filtered list)
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Daniellequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi
gt; gt; gt; gt;
gt; gt; gt; gt; I am running v.2000 and need some help. I have an excel sheet containing
gt; gt; gt; gt; multiple columns and 1000s of rows.
gt; gt; gt; gt;
gt; gt; gt; gt; I know how to delete rows which are completely identical (advanced filter
gt; gt; gt; gt; etc) but I need to know the following:
gt; gt; gt; gt;
gt; gt; gt; gt; If there are two rows which are generally different (and so won't be deleted
gt; gt; gt; gt; by the advanced filter method), but each have an identical value in column D,
gt; gt; gt; gt; how would I get rid of the entire row containing the duplicate cell?
gt; gt; gt; gt;
gt; gt; gt; gt; So for example if I had three rows as follows:
gt; gt; gt; gt;
gt; gt; gt; gt; Mr | Joe | Bloggs | London
gt; gt; gt; gt; Mr | Tom | Jones | Glasgow
gt; gt; gt; gt; Mr | Ben | Jones | Essex
gt; gt; gt; gt;
gt; gt; gt; gt; None of these would count as traditional duplicates via the advanced filter
gt; gt; gt; gt; method.
gt; gt; gt; gt;
gt; gt; gt; gt; But what if I wanted to get rid of duplicate surnames - in the above example
gt; gt; gt; gt; I would ideally want to be left with just one of the quot;Jonesquot; rows - how can I
gt; gt; gt; gt; do this?
gt; gt; gt; gt;
gt; gt; gt; gt; It is worth noting that I have only basic experience with excel and so am
gt; gt; gt; gt; not familiar with macros etc - I tried one macro tutorial that didnt work :-S
gt; gt; gt; gt;
gt; gt; gt; gt; Hope someone can help!
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; Danielle

Try just following the instructions exactly as i posted them.

(With the Advanced Filter method I posted, you don't need to copy the
formula down the side of your data. The filter will adjust the formula
quot;internallyquot; as it processes records. Consequently, you'll only need the one
formula in the criterial.
And DON'T put a column heading labelled TestDup in your data or it will
confuse the Advanced Filter.)Does that help?

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

XL2002, WinXP-Proquot;Daniellequot; wrote:

gt; Hello again,
gt;
gt; Thanks - it's much clearer now and I understand what you mean.
gt;
gt; However I still dont seem to be getting the right result
gt;
gt; I am basically writing the TestDup in the header, then putting the formula
gt; into the F2 column and the copying it down for each record.
gt;
gt; I have tried various methods including selecting and not selecting the F
gt; colum, choosing in-place or copy to another location, and choosing and not
gt; choosing unique fields only, but dont seem to be getting anywhere.
gt;
gt; Its worth noting that for the F fields with the formula in, it comes back
gt; with either a FALSE or TRUE value, but only a couple of my records are
gt; showing true, and they arent duplicates. The ducplicates that are there still
gt; say False. Could this be a problem?
gt;
gt; Sorry to be such a difficult one!
gt;
gt; Danielle
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; OK
gt; gt;
gt; gt; Regarding the assumptions and criteria:
gt; gt; gt;gt;With your list in Cells A34000
gt; gt; gt;gt;and list headings in cells A33 (Title, FName, Lname, Location)
gt; gt; gt;gt;F1: DupTest
gt; gt; gt;gt;F2: =COUNTIF(C$3:C4,C4)gt;1
gt; gt;
gt; gt; 1)As you know, for Advanced Filters to work, they need column headings on
gt; gt; the list. Hence the: Title, FName, Lname, Location
gt; gt;
gt; gt; 2)The range A34000 is just as an example, but I made sure the data rows of
gt; gt; the list (under the column headings) would be under the criteria rows so they
gt; gt; wouldn't be deleted in the duplicate removal.
gt; gt;
gt; gt; 3)Since I used a formulaic criteria, I couldn't use a list column heading. I
gt; gt; could have left cell F1 blank, but instead i just used a descriptive heading
gt; gt; that didn't match any of the list headings.
gt; gt;
gt; gt; 4)Notice the single dollar sign ($) in the criteria forumula. That formula
gt; gt; will play out this way:
gt; gt;
gt; gt; Testing cell C4, the formula will be: =COUNTIF(C$3:C4,C4)gt;1
gt; gt; Testing cell C5, the formula will be: =COUNTIF(C$3:C5,C5)gt;1
gt; gt; Testing cell C6, the formula will be: =COUNTIF(C$3:C6,C6)gt;1
gt; gt; etc
gt; gt;
gt; gt; Consequently, the first time the formula encounters a value, its count will
gt; gt; be 1. All other instances will have a count of greater than 1.
gt; gt;
gt; gt; The Advanced filter only displays records where the item count is greater
gt; gt; than 1....the duplicates.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Daniellequot; wrote:
gt; gt;
gt; gt; gt; Hi Ron
gt; gt; gt;
gt; gt; gt; Thanks for your answer. I am afraid I dont understand some parts of your
gt; gt; gt; reply, though - can you clarify for me?:
gt; gt; gt;
gt; gt; gt; How would the list be in D4000 when there are only 3 items (in the example)?
gt; gt; gt; Or is this based on there being 4000 records?
gt; gt; gt;
gt; gt; gt; What is F1 and F2 referring to? What do I have to do with them?
gt; gt; gt;
gt; gt; gt; If you can let me know and I can try it again :-)
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; Danielle
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Perhaps something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; With your list in Cells A34000
gt; gt; gt; gt; and list headings in cells A33 (Title, FName, Lname, Location)
gt; gt; gt; gt;
gt; gt; gt; gt; F1: DupTest
gt; gt; gt; gt; F2: =COUNTIF(C$3:C4,C4)gt;1
gt; gt; gt; gt;
gt; gt; gt; gt; Select your list (A34000)
gt; gt; gt; gt; Datagt;Filtergt;Advanced Filter
gt; gt; gt; gt; List Range: (already selected)
gt; gt; gt; gt; Criteria Range: $F$1:$F$2
gt; gt; gt; gt;
gt; gt; gt; gt; Click the [OK] button to filter the list in place.
gt; gt; gt; gt;
gt; gt; gt; gt; That will hide the first occurrence of a LName and display only the
gt; gt; gt; gt; duplicates.
gt; gt; gt; gt;
gt; gt; gt; gt; Select the visible cells below the column headings
gt; gt; gt; gt; Edit|Delete (you can only delete entire rows in a filtered list)
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Daniellequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am running v.2000 and need some help. I have an excel sheet containing
gt; gt; gt; gt; gt; multiple columns and 1000s of rows.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I know how to delete rows which are completely identical (advanced filter
gt; gt; gt; gt; gt; etc) but I need to know the following:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If there are two rows which are generally different (and so won't be deleted
gt; gt; gt; gt; gt; by the advanced filter method), but each have an identical value in column D,
gt; gt; gt; gt; gt; how would I get rid of the entire row containing the duplicate cell?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So for example if I had three rows as follows:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mr | Joe | Bloggs | London
gt; gt; gt; gt; gt; Mr | Tom | Jones | Glasgow
gt; gt; gt; gt; gt; Mr | Ben | Jones | Essex
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; None of these would count as traditional duplicates via the advanced filter
gt; gt; gt; gt; gt; method.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But what if I wanted to get rid of duplicate surnames - in the above example
gt; gt; gt; gt; gt; I would ideally want to be left with just one of the quot;Jonesquot; rows - how can I
gt; gt; gt; gt; gt; do this?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; It is worth noting that I have only basic experience with excel and so am
gt; gt; gt; gt; gt; not familiar with macros etc - I tried one macro tutorial that didnt work :-S
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hope someone can help!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; Danielle