|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I need some help with a macro or something in Excel.
I have a xls mail list. Col A Name, Col B, Last Name, Col C Email
Address.
We just sent out our Newsletter and over half the 2000 email addresses
are invalid. SOO. How can I take a list of the successfull email
addresses only (txt file with just the addresses in a long list) and
have them matched aganist the master list and delete all the rest ?
Is this possible ?
Is this the right place to ask ?
:) TIA
|
|
Phil
The easiest way...
Import the text file into Excel. Open your current full email workbook and
alongside the email addresses in the full list workbook (column D) enter a
VLOOKUP formula
=VLOOKUP(A1,Select_Your_Correct_Email_Addresses_To_Get_Range_Here,1,FALSE)
Copy this down. This should result in a list of email addresses in this new
column and lots of #N/A errors. These are the unmatchables. Sort by this
column, all the errors will be together, delete them and then delete your
'helper' column.
Hopefully that makes sense...
The formula if the addresses are in Book1 on Sheet1 range A1:A500 should
look like this. (Make sure the $$ are there as this makes the range you are
looking up 'absolute')
=VLOOKUP(A1,[Book1]Sheet1!$A$1:$A$500,1,FALSE)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS
"Phil McKraken" <tingtejs[ at ]hotmail.com> wrote in message
news:fvv8n1hqjac9c1a6naj636f818mb08hbog[ at ]4ax.com...
[Quoted Text] > I need some help with a macro or something in Excel.
>
> I have a xls mail list. Col A Name, Col B, Last Name, Col C Email
> Address.
>
> We just sent out our Newsletter and over half the 2000 email addresses
> are invalid. SOO. How can I take a list of the successfull email
> addresses only (txt file with just the addresses in a long list) and
> have them matched aganist the master list and delete all the rest ?
>
> Is this possible ?
> Is this the right place to ask ?
>
> :) TIA
|
|
On Fri, 11 Nov 2005 17:43:04 -0000, "Nick Hodge"
<nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS> wrote:
[Quoted Text] >Phil
>
>The easiest way...
>Hopefully that makes sense...
Not even a little bit. :) sorry I forgot to mention Im a dumbass when
it comes to excel. I can barley make a calculating table with it much
less this.
My master list (with all the bad addresses) has 1897 rows, and 3
columns, A (Name) B (Last Name) C (email address).
My successful sent list has 1 column, A(email addresses only) and 928
rows. The master list IS an excel file (xml extension) the successful
list is a simple txt file with the emails in one column. When I
imported it, excel called it successful.txt but VLOOKUP would not take
the file name.
I'm sure the syntax is wrong but I am REAL confused even though I had
occasion to use a sheet with this function a few months ago. I just
can't figure it out.
TIA
|
|
Phil
OK...step-by-step (Complete list = File A, Successful = File B)
1) Open File A (I presume this is .xls NOT .xml as you stated)
2) Open File B
2a) This will require you from Excel going to... File>Open... In the dialog
select 'Files of type' All files *.* and select File B. This may fire a text
import wizard, if the file has just one column click finish.
3) Double click the sheet tab and change the name to Sheet1
4) In file B go to File>Save as... Change the 'File save as' to Excel
workbook and name it Test (Doesn't matter where you save it, desktop will
do, as we will delete it at the end).
5) Go to File A and in D2 (I'm presuming the totals are in row 1) enter
=VLOOKUP(C2,[Test.xls]Sheet1!$A$1:$A$928,1,FALSE)
This will either result in an email address or #N/A
6) Click back on cell D2 and you will see a small square in the bottom right
hand corner, click on this and drag down the 1897 rows of File A. Let go the
mouse and you should see either an email address or #N/A against each
address.
7) In File A, highlight the range A1:D1897 and go to Data>Sort... and select
column D as the column to sort on, you should now have all the #N/As
together.
8) By clicking on the row number by the top #N/A and dragging down to the
last, you will select the entire rows for all the #N/As
9) Go to Edit>Delete and the rows will delete
10) Highlight all the data in File A range D1:D928. (This should now cover
the data left in File A)
11) Go to Edit>Copy, then without doing anything else go to Edit>Paste
special... and select values in the dialog. (This 'kills' the VLOOKUP
formula and breaks the link to File B)
12) Save file A and close
13) Close File B, no need to save and delete it
You should now have file A with only authenticated email addresses
Hope it makes sense now. If not email me the two files and I'll get it done
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS
"Phil McKraken" <tingtejs[ at ]hotmail.com> wrote in message
news:317an15q8cks435g0k0d7cp8ivol8rk4j9[ at ]4ax.com...
[Quoted Text] > On Fri, 11 Nov 2005 17:43:04 -0000, "Nick Hodge"
> <nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS> wrote:
>
>>Phil
>>
>>The easiest way...
>
>>Hopefully that makes sense...
>
>
> Not even a little bit. :) sorry I forgot to mention Im a dumbass when
> it comes to excel. I can barley make a calculating table with it much
> less this.
>
> My master list (with all the bad addresses) has 1897 rows, and 3
> columns, A (Name) B (Last Name) C (email address).
> My successful sent list has 1 column, A(email addresses only) and 928
> rows. The master list IS an excel file (xml extension) the successful
> list is a simple txt file with the emails in one column. When I
> imported it, excel called it successful.txt but VLOOKUP would not take
> the file name.
>
> I'm sure the syntax is wrong but I am REAL confused even though I had
> occasion to use a sheet with this function a few months ago. I just
> can't figure it out.
>
> TIA
|
|
|