How to delete automatically records?

[Marlies]

Hi,

I have two sheets:
Sheet1: subscribers from an email database (with all information like email, name, address, phone, ...)
Sheet2: 1 column with only email addresses

I want to compare the two sheets. All the records in sheet1 that matches with an email addres from sheet2 should be deleted.

What is the easiest way to do this,

Thanks in advance,
Marlies

[arlu1201]

There are many ways one being to use a macro to do it.

Do you have a sample file that you can upload? Dummy data will do but it should be in the same format as the original.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Marlies]

You can find an example file in attachment.
Normally 3 records should be automatically removed from the sheet "Data".

Thanks for helping me out!

[elayaz]

Hi Marlies,

See if the attached file helps. Learnt this formula today from this forum member

In the file,Use filter and delete the rows.

Hope this helps.


----------------------------------------------------------------------------
If my reply helped, Please add reputation (click on the star below the post)
If the reply solves your problem, Don't forget to mark the thread as "Solved" (Thread Tools->Mark thread as Solved)

[Marlies]

If I copy this formule to my final file, it gives allways "Don't Delete". Even when the email address is a match with the Sheet "Bounces".
My columns are exactly the same as the example file.

Do I have to do something else? I just copied the formule.

[elayaz]

Hi,

In Bounces tab, Make sure to add "delete" in the next column to email list.

Hope it works.

EDIT : Also make sure to copy/paste the formula in a2 of data sheet.

[Marlies]

Ok, it works now but when I try to change the number of rows (because i have more than 23 rows in the bounces sheet) the formule doesn't work anymore... (I get 0)

This is the working formule you gave me:
=IFERROR(LOOKUP(1E+99;SEARCH(Bounces!$A$1:$A$23;D2);Bounces!$B$1:$B$23);"don't delete")

This is my adjusted formule (i have 15109 rows in the bounces sheet and more than 400000 rows in the data scheet)

=IFERROR(LOOKUP(1E+99;SEARCH(Bounces!$A$1:$A$15109;D2);Bounces!$B$1:$B$15109);"don't delete")

[elayaz]

If there is no "delete" text in the next column of Bounces 15109 email list, the formula result will be "0" which is equal to delete.

Please add "delete" text to all the cells adjacent to email list.

[Marlies]

It works!

Thanks for helping me out!

[newdoverman]

This file indicates on both lists which records match.