Cleanout invalid data

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

Thanks Bob

Mary Ann

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)