Cleanout invalid data
 

I have a sheet with contact info on it, including email addresses, lets call
this the Contacts sheet. On another sheet I have a list of email addresses
which are no longer valid, lets call this the Addresses sheet. My objective
is to remove the contacts from the Contacts sheet whose addresses are no
longer valid.

So far I have used Find €“ Ive copied each email address from the Addresses
sheet, switched to the Contacts sheet, found the record, replaced with 1 and
then filtered for 1 and deleted the rows.

Is there a quicker way to do this.

I am a sophisticated user of Excel but dont do programming. I am using
Excel 2003.

Cleanout invalid data
 

On the first sheet, add a formula

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",1)

copy this down, then filter on 1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mary Ann" wrote in message
...
I have a sheet with contact info on it, including email addresses, lets
call
this the Contacts sheet. On another sheet I have a list of email
addresses
which are no longer valid, lets call this the Addresses sheet. My
objective
is to remove the contacts from the Contacts sheet whose addresses are no
longer valid.

So far I have used Find - I've copied each email address from the
Addresses
sheet, switched to the Contacts sheet, found the record, replaced with 1
and
then filtered for 1 and deleted the rows.

Is there a quicker way to do this.

I am a sophisticated user of Excel but don't do programming. I am using
Excel 2003.

Cleanout invalid data
 

Thank you Bob. Ive now looked at your suggestion and the Help on Match and
have another idea for working with it and wonder if you would comment on
whether what I am thinking of is likely to give me the same result. It seems
to when I use it, but perhaps there is something I am not seeing.

I have not used the IF function, just the following bit of your formula:
=MATCH(A1,Sheet2!A:A,0)
Then I have done a custom filter for Not Equal To #NA.

Can you see problems with this adaption?


"Bob Phillips" wrote:

On the first sheet, add a formula

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",1)

copy this down, then filter on 1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mary Ann" wrote in message
...
I have a sheet with contact info on it, including email addresses, lets
call
this the Contacts sheet. On another sheet I have a list of email
addresses
which are no longer valid, lets call this the Addresses sheet. My
objective
is to remove the contacts from the Contacts sheet whose addresses are no
longer valid.

So far I have used Find - I've copied each email address from the
Addresses
sheet, switched to the Contacts sheet, found the record, replaced with 1
and
then filtered for 1 and deleted the rows.

Is there a quicker way to do this.

I am a sophisticated user of Excel but don't do programming. I am using
Excel 2003.

Cleanout invalid data
 

Not for what you want. As you see it throws an error on no match, but you
can happily filter on that error value, so no problems.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mary Ann" wrote in message
...
Thank you Bob. I've now looked at your suggestion and the Help on Match
and
have another idea for working with it and wonder if you would comment on
whether what I am thinking of is likely to give me the same result. It
seems
to when I use it, but perhaps there is something I am not seeing.

I have not used the IF function, just the following bit of your formula:
=MATCH(A1,Sheet2!A:A,0)
Then I have done a custom filter for Not Equal To #NA.

Can you see problems with this adaption?


"Bob Phillips" wrote:

On the first sheet, add a formula

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",1)

copy this down, then filter on 1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mary Ann" wrote in message
...
I have a sheet with contact info on it, including email addresses, lets
call
this the Contacts sheet. On another sheet I have a list of email
addresses
which are no longer valid, lets call this the Addresses sheet. My
objective
is to remove the contacts from the Contacts sheet whose addresses are
no
longer valid.

So far I have used Find - I've copied each email address from the
Addresses
sheet, switched to the Contacts sheet, found the record, replaced with
1
and
then filtered for 1 and deleted the rows.

Is there a quicker way to do this.

I am a sophisticated user of Excel but don't do programming. I am
using
Excel 2003.

Cleanout invalid data
 

Thanks Bob

Mary Ann

"Bob Phillips" wrote:

Not for what you want. As you see it throws an error on no match, but you
can happily filter on that error value, so no problems.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mary Ann" wrote in message
...
Thank you Bob. I've now looked at your suggestion and the Help on Match
and
have another idea for working with it and wonder if you would comment on
whether what I am thinking of is likely to give me the same result. It
seems
to when I use it, but perhaps there is something I am not seeing.

I have not used the IF function, just the following bit of your formula:
=MATCH(A1,Sheet2!A:A,0)
Then I have done a custom filter for Not Equal To #NA.

Can you see problems with this adaption?


"Bob Phillips" wrote:

On the first sheet, add a formula

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"",1)

copy this down, then filter on 1

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mary Ann" wrote in message
...
I have a sheet with contact info on it, including email addresses, lets
call
this the Contacts sheet. On another sheet I have a list of email
addresses
which are no longer valid, lets call this the Addresses sheet. My
objective
is to remove the contacts from the Contacts sheet whose addresses are
no
longer valid.

So far I have used Find - I've copied each email address from the
Addresses
sheet, switched to the Contacts sheet, found the record, replaced with
1
and
then filtered for 1 and deleted the rows.

Is there a quicker way to do this.

I am a sophisticated user of Excel but don't do programming. I am
using
Excel 2003.