I need help - please:

I have 2 Excel sheets:

1 €“ Contains all contact details (aaprx 900 rows)
2- Contains email addresses which have bounced back over the past 3 months
(approx 80 rows)

Sheet 1 - first_name, last_name, email, company, job_title, street_no, etc,
etc, etc.,
Sheet 2 €“ email_address

How can I write a macro or function to delete the rows in sheet 1 that
contain the bounced email addresses listed in sheet 2

All help appreciated.

Please don't multi-post.
Below is a response to your identical posting in microsoft.public.excel
---
Here's a quick fix you could apply using formulas ..

Source data is assumed in Sheet1, cols A to H (say),
data from row2 down, with email addresses in col C.

Bounced email addresses are assumed listed in Sheet2, in A1 down

In Sheet1,
Using an empty col to the right, say col X

Put in X2:
=ISNUMBER(MATCH(C2,Sheet2!A:A,0))
Copy down to the last row of source data, X900? Then select col X, click
DataFilterAutofilter. Select: TRUE from the autofilter droplist in X1. This
filters out the rows to be deleted. Select all the filtered rows (select all
the blue colored row headers), then right-click Delete Row. That's it.
Remove autofilter, and you would have your residual list, trimmed of all the
bounced email in Sheet2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
I need help - please:

I have 2 Excel sheets:

1 €“ Contains all contact details (aaprx 900 rows)
2- Contains email addresses which have bounced back over the past 3 months
(approx 80 rows)

Sheet 1 - first_name, last_name, email, company, job_title, street_no, etc,
etc, etc.,
Sheet 2 €“ email_address

How can I write a macro or function to delete the rows in sheet 1 that
contain the bounced email addresses listed in sheet 2

All help appreciated.

Thank you Max,

After I copy and pasted, all cells returned FALSE - no tru values at all.
Any suggestions?

Mark



"Max" wrote:

Please don't multi-post.
Below is a response to your identical posting in microsoft.public.excel
---
Here's a quick fix you could apply using formulas ..

Source data is assumed in Sheet1, cols A to H (say),
data from row2 down, with email addresses in col C.

Bounced email addresses are assumed listed in Sheet2, in A1 down

In Sheet1,
Using an empty col to the right, say col X

Put in X2:
=ISNUMBER(MATCH(C2,Sheet2!A:A,0))
Copy down to the last row of source data, X900? Then select col X, click
DataFilterAutofilter. Select: TRUE from the autofilter droplist in X1. This
filters out the rows to be deleted. Select all the filtered rows (select all
the blue colored row headers), then right-click Delete Row. That's it.
Remove autofilter, and you would have your residual list, trimmed of all the
bounced email in Sheet2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
I need help - please:

I have 2 Excel sheets:

1 €“ Contains all contact details (aaprx 900 rows)
2- Contains email addresses which have bounced back over the past 3 months
(approx 80 rows)

Sheet 1 - first_name, last_name, email, company, job_title, street_no, etc,
etc, etc.,
Sheet 2 €“ email_address

How can I write a macro or function to delete the rows in sheet 1 that
contain the bounced email addresses listed in sheet 2

All help appreciated.

It should have worked if the email addresses match.

Try this heavier duty formula ..

In Sheet1,

Put instead in X2:
=IF(C2="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(C2,Sheet2!$A$1:$A$80)))0,"x",""))
Copy down. Then autofilter for "x" in col X, delete these rows.

If the above still doesn't do it, post some samples of the email addresses
in Sheet1's col C and the bounced ones in Sheet2's col A.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Thank you Max,

After I copy and pasted, all cells returned FALSE - no tru values at all.
Any suggestions?

Mark