Cleaning an Email list in Excel 2007

[Deleted member 53208]

I bet this query is so easy to someone who understands Excel 2007, but I am in hell (sitting next to this bloke with a red face and horns) trying to clean duplicate email addresses from a list.

The background is that I send newsletters to my clients, but some of them don't want the newsletters (their loss), so I keep an unsubscribe list as I don't want to upset anyone.

Thes 2 lists (subscribed, and unsubscribe) are just columns in an Excel spreadsheet. I have tried the remove duplicate function in Excel to get rid of duplicates, but it doesn't seem to work. On that score I wonder if it is having a problem recognising some email addresses which have copied as links, as text strings?.

Anyhow can anyone help me with this problem as I've scoured the internet for a solution but haven't been able to come up with an easy way to do it.

I must warn you that I am stupid, but can follow step by step instructions, and I don't mind using a copy of the master spreadsheet if the answer means destroying the unique and duplicate content.

On a slightly different tack, does anyone know of any software that will look at email list A, check email list B and delete any entries on email list B that appear on email list A?

Having done a bit of work with Excel I just can't believe it can be so difficult :|

Thanking you in advance

 

[apeebles]

Probably not the exact advice you are after but why not consider using an email system so that clients can automatically unsubscribe and it also keeps that information for you? They do tend to deduplicate and keep track of unsubscribes and deleted records.

Otherwise I think there is a way you could convert the email list all to text incase there are any unusual characters. It may also be worth going through the list after you have sorted it to see any obvious duplicates and then figuring out why the de dupe function wasn't picking them up. I have used the duplication function in excel and it worked brilliantly for my requirements so it is a good tool.

Good luck

Alistair

 

[philwalker_triarch]

i would do all this in either in:-
excel via a pivot table to identify duplicates - you would have to remove them manually OR if you have
access as you can easily identify the multiple counts of email addresses and if you want to delete the duplicates you can use a delete query

 

[iboxsecurity]

Our email marketing system you can import by excel sheets and we maintain the database and automatically handle subscribes/unsubscribes and clean for duplicates, fake emails and no longer existing emails

 

[SneakSMS]

for the future, try this bit of kit:

http://www.inoutscripts.com/products/inout_mailing_list_manager/

$39 (USD) for the license, and it'll make your life a lot easier.

You'll be able to import your emails from the excel file and manage them much easier.

It can remove invalid addresses from bounced emails and allows people to unsubscribe themselves.

 

[Deleted member 53208]

Has anyone had any experience with Constant Contact email marketing?

 

[philwalker_triarch]

Has anyone had any experience with Constant Contact email marketing?

I looked at them a while back but went for Pointmail

 

[brucegreig]

Not quite clear if you are trying to remove duplicates within a column, or identify duplicates that appear in both columns. If you are trying to remove duplicates within one column, then I would do this:

Assuming your emails are in column A, and column B is blank:
1. Sort column A, containing the emails, alphabetically. Your duplicates will then be adjacent to each other.
2. In adjacent column B enter this formula:

IF(A2=A1,"Dupe","")

(which means if whatever's in A1 is same as A2, i.e. a duplicate, then put "Dupe" next to it, otherwise put "Unique")

Copy the formula all the way down the column, so every email address has the formula next to it, and the word "Dupe" or "Unique".

3. Copy the formula column, then Paste Special in the same place selecting "Values" in the Paste Special list

4. Sort both columns, based on the contents of Column B (Dupe / Unique).

5. You now have all your Dupes at the top (or bottom, depending which way you sorted) and can delete them.

 

[Astaroth]

A database is going to be a much better way of doing this than two lists in a spreadsheet even if it is a case of just using a couple of sheets in a spreadsheet rather than a true db.

The issue you will have as you currently stand is that if someone unsubscribes you delete them from the subscribers list and keep them on the unsubscribers so if they ever resubscribe you will immediately remove them again because they still exist in list B. At the same time you cannot delete them from list B as if you ever buy data you would have to remove anyone who has previously unsubscribed from you before sending.

 

[spreadsheetsdirect]

An Advanced Filter is another way of getting rid of duplicates. If you select Copy to another location, put the range with duplicates in the List Range, in for Copy put a cell ref in where you want to paste the results and then tick Unique records only and you should get a list of unique emails.

 

[Deleted member 53208]

Not quite clear if you are trying to remove duplicates within a column, or identify duplicates that appear in both columns. If you are trying to remove duplicates within one column, then I would do this:

Assuming your emails are in column A, and column B is blank:
1. Sort column A, containing the emails, alphabetically. Your duplicates will then be adjacent to each other.
2. In adjacent column B enter this formula:

IF(A2=A1,"Dupe","")

(which means if whatever's in A1 is same as A2, i.e. a duplicate, then put "Dupe" next to it, otherwise put "Unique")

Copy the formula all the way down the column, so every email address has the formula next to it, and the word "Dupe" or "Unique".

3. Copy the formula column, then Paste Special in the same place selecting "Values" in the Paste Special list

4. Sort both columns, based on the contents of Column B (Dupe / Unique).

5. You now have all your Dupes at the top (or bottom, depending which way you sorted) and can delete them.

Thanks for this Brucegrieg but I can't get it to work.

Having sorted the list alphabetically, I copied the formula from your posting, in the column next to it, placing an = sign in front of it, then using the little cross on the bottom right of the cell (sorry I can't be more technical), I copied it down the full length of the column, but all that happened was all of the cells got "dupe" in them, even ones which weren't duplicated anywhere.

Any ideas where I went wrong?

 

[Deleted member 53208]

Thank you very much, Got it to work, now for the finale. Where I have my list of unsubscribes next to it, is there an easier way than using "find" on one at a time of checking if any addresses in the unsubscribe list are in the main column, and if so either deleting them or highlighting them?

 

[philwalker_triarch]

Thank you very much, Got it to work, now for the finale. Where I have my list of unsubscribes next to it, is there an easier way than using "find" on one at a time of checking if any addresses in the unsubscribe list are in the main column, and if so either deleting them or highlighting them?

do you have microsoft access? If so import your spreadsheet into a table and then select that table in a query and create a cancel query to remove those where those in unsubscribe column are same as original

 

[brucegreig]

Thank you very much, Got it to work, now for the finale. Where I have my list of unsubscribes next to it, is there an easier way than using "find" on one at a time of checking if any addresses in the unsubscribe list are in the main column, and if so either deleting them or highlighting them?

Good. Especially as my formula had a typo, so will have given you "Dupe" or "" (blank cell), not "Dupe" or "Unique". But I guess you figured that out.

You really want to start using a more sophisticated email subscription tool... but, if you want to check whether an item in list A (main column) exists in List B (unsubscribe list), then do enter this formula in each of the cells next to list a:

=VLOOKUP(a1,<<list B>>,1,0)

Where a1 is the first item in List A
<<list B>> is the range where list B is (e.g $L$1:$L$998 if it was in col L, and 998 rows long).

List B will need to be sorted alphabetically.

You'll get an error response if the item does not exist, and the item itself if it does exist. Just delete from the main list those where the VLOOKUP formula has returned an error.

(obviously do some manual checks first, to check your formula is working before you start deleting lots!)