ScoobyJawa

Hi all

Hoping someone is a bit more versed in excel than me!!

I have a list of email addresses in an Excel file.

I have an export of Outlook Contacts in another Excel file (inc name, address etc).

What I need to do is remove the contacts listed in the email addresses file from the Outlook Export and then re-import it......

In other words I have a list of about 1000 dead email addresses that I need to remove from my Outlook Contacts/Address Book.

Anyone got any ideas how to do it??

Thanks in anticipation!
Neil

HankScorpio

How I would do it...

OL contacts in one sheet
Dead addressed in col in next sheet.
Do a VLOOKUP in new column on OL sheet of email addy against dead list.
Sort OL sheet based on VLOOKUP results which will make it easy to:
Delete dross.
Delete extra column in OL sheet
Save data in original format.
Re-import to clean outlook.

Ta-daaaaaa!

bioforger

iTrader: (1)

Yea what Hank said, you just need to do a comparison between the 2 lists n delete the ununsed ones, which is what vlookup will achieve.

HankScorpio

How bored am I?

Take your excel export of contacts...
Add a new sheet called "Dead" and add all the dead emails in column A.
In the export sheet, add a new column at the beginning (so new column A).
Place this formula in A2 and drag all the way down:

(BG2 on my sheet contains the email addy, edit if yours is different.)
Select all and Sort by column A on that sheet
Delete all the rows beginning with Dead
Delete column A.

bioforger

iTrader: (1)

Cmon Hank you're slacking, create a vba macro to do the copying n then filter on the dead rows and delete them n the column, autofit n format, macronise it!

HankScorpio

I did a vba course about 4 years ago, walked out thinking "this is great, so many things I can do"
Three weeks later and I couldn't remember a thing!

I see myself as an 8 track compared to a hdd multimedia player...

I still write shell scripts with resource kit tools as well.

ScoobyJawa

LOL!!!!

Sorry guys really thick!

I have sheet 1 is the outlook export, sheet 2 the dead emails.

I inserted a new column (A) in the outlook export

VLOOKUP asks for Lookup_value, Table_array and Col_index_num

How do I factor that in to the sheets I have?

Yes I'm very stoopid lol!

Thanks guys - your help most appreciated

The Zohan

I came here thinking i might help, i read the above amd now retreat with my tale between my legs now knowing i know little about excel.

Happy new year Neil!

HankScorpio

Just paste in that line above, as long as your export sheet has the email addresses in column BG and your second sheet is called "Dead", you'll be fine...

ScoobyJawa

Hank you are a star mate Thanks very much

Happy new year to you

Hiya Paul - happy new year to you too mate - hope you're well?

HankScorpio

No problem, I like the challenge of a nested formula

Glad it worked for you