How can I do this using MS Excel ?

  • Thread starter Thread starter Valerie Smith
  • Start date Start date
V

Valerie Smith

How can I do this using MS Excel ?
*********************************

I am NOT technical.


Mail Merging / Demerging capabilities...

a). I need to add to my list of email addressses from time to
time. I need to make sure that I do not send an email to a person
that I sent an email to earlier.

b). So I am hoping that Excel is capable of merging new lists
with the old lists and I can highlight the duplicates
in the NEW list so I do not send these folks emails again.

c). After this, I am hoping that Excel is capable of DE-MERGING so
I can send emails only to the folks in the new list that have not been
contacted earlier in my earlier lists.

How can I do this using Excel ?

Is there any quick way to mark the entire old list as "sent"
collectively without having to individually go to each email and
typing in sent in a column next to it.

Can anyone PLEASE HELP me.

Once again I am not technical.

Thanks
Valerie
 
How can I do this using MS Excel ?
*********************************

I am NOT technical.
Click to expand...

Given your emphasis on that point, it may be better to stay away from
a technical (VBA) solution. You can do all of this without resorting
to it anyway. Here's how. (Don't be worried by the length of this; I'm
just explaining everything in detail as we go. The process itself is
fairly quick and not that complicated if you follow the instructions):

- Let's say that your ORIGINAL list runs down column A. Copy the
NEW list onto the worksheet. (Actually it can be on a separate sheet
if you want, but for the purposes of this illustration we'll assume
that it's on the same one.) Let's say that you put the new list in
column D, starting from row 2. (With row 1 as a heading. Put the
heading "Sent before?" in E1.)

- In cell E2, put the formula:
=IF(ISNA(VLOOKUP(D2,$A$2:$A$9,1,FALSE)),"No","Yes")

Substitute the range of your ORIGINAL list for $A$2:$A$9, but make
sure that the range reference is ABSOLUTE; that is, that you have $
signs before each column and row. (In fact a named range would be
preferable if you know how to do that.)

What that formula does is this:
- The function VLOOKUP(D2,$A$2:$A$9,1,FALSE) tells Excel to look for
the value in D2 (the first entry in your NEW list) in the range
$A$2:$A$9 (or whatever the range of your ORIGINAL list is). The 1 says
to look in the first (and indeed only) column of that range. The False
tells it to find only exact matches.

- If the value is not found, the function will return an #N/A error.
the ISNA function will detect such an error.

- If that error IS returned by the VLOOKUP function, it follows that
the address shown in the new range does NOT appear in the original
range. Accordingly the IF function returns "No" (as in, No, you
HAVEN'T sent to this person before), otherwise it returns "Yes" (as in
Yes, you HAVE sent to this person before).

Copy that formula down so that there is a formula in column E for each
address in column D (your new list).

When you recalculate, all of the people that you haven't sent to
previously will have "No" in column E. You presumably want to filter
those out. There are two ways:
- You could sort the list (though that may mess up your original
list); or
- You could use autofilter.

To do the latter, put your cursor in cell E1, and go to Data -> Filter
-> Autofilter. In the dropdown that appears in E1, select "No" as the
value that you want.

Select the new list by pressing [Ctrl] + [*] (hold down the [Ctrl] key
and press the asterisk; that will select the entire new list).

Now go to Edit -> Goto ... -> Special and choose the Visible Cells
Only button. Copy that list by pressing [Ctrl] + [C]. That will copy
ONLY the entries that you haven't sent to before. You can paste that
list into a blank sheet if you wish, then when you're done sending
those e-mails you can just copy that list and paste it onto the end of
your original one.
 
Back
Top