Need help finding non-duplicate email addresses...

G

Greg

Hi,

I have a list of email addresses which I would like to find only the
new email addresses within the list. Inside of my Excel worksheet I
have a row of e-mail addresses. Some of the names are duplicated which
tells me they already exist somewhere in the row. I wish to display
only the new names. Can you tell me how this can be done in Excel?

Thanks in advance for your assistance,
Greg
 
P

Paul Lautman

Greg said:
Hi,

I have a list of email addresses which I would like to find only the
new email addresses within the list. Inside of my Excel worksheet I
have a row of e-mail addresses. Some of the names are duplicated which
tells me they already exist somewhere in the row. I wish to display
only the new names. Can you tell me how this can be done in Excel?

Thanks in advance for your assistance,
Greg
Click to expand...

Are you saying that you have 2 lists and you wish to find what items from
one list do not exist in the other one?

If so are both these lists horizontal?
 
G

Greg

Hi Paul,

Thanks for your reply. No the lists are vertical but can be horizonal if
that helps. And it is currently one list but I can make them into two list as
well.

-Greg
 
P

Paul Lautman

Greg said:
Hi Paul,

Thanks for your reply. No the lists are vertical but can be horizonal
if that helps. And it is currently one list but I can make them into
two list as well.

-Greg
Click to expand...

If the lists are vertical, what is the "row of e-mail addresses" of which
you speak?
 
G

Greg

It might be easier to show you what I have:

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Nam [email protected]
[email protected]
Richard [email protected]
Robert [email protected]
[email protected]
[email protected]
[email protected]

I want to eliminate the duplicate addresses:
[email protected]
[email protected]
[email protected]
[email protected]

and save the non-duplicate addresses.
 
G

Greg

Hi Dave,

Thanks for your reply. I guess I am still not clear on what I want.
Filtering on unique records does eliminate duplicate e-mail addresses but
keeps a copy of the original. I want to eliminate the original and keep only
the non-duplicates.

Hope this is more clear.

-Greg
 
P

pgeraf

Hi Greg,
I think what you are looking for is an Array formula. Try this:

Put this formula in Sheet2!A2 and press Ctrl+Shift+Enter to convert i
to an array formula. Then copy it all the way down to reproduce th
number of cells where the e-mail addresses are included in Sheet1 (th
e-mail addresses should start at cell A2 in Sheet1). Hopefully, if i
woks as it did with me, it will eliminate all duplicates. This formul
will work with anything you list in Sheet1.

{=IF(SUM((Sheet1!A2=Sheet2!$A$1:A1)*1)=0;Sheet1!A2,"")}

Enjoy! :cool
 
G

Greg

Thanks so much for your response. When using an array formula do I need to
remove the surrounding open and close braces before entering into the cell?
Otherwise, only the formula appears in the cell.

However, if I delete the braces I get an error message with the formula with
a request to "accept the change". When I accept the change the results pasted
are #REF!. Can you tell me what's going on here?

Regards,
Greg
 
G

Greg

Hi Dave,

I guess I'm being a "real dunce case" about this. Can you send me a sample
Excel Spreadsheet describing how to do this process using the data I posted
above? You can send the file to gamouning at yahoo.com.

With appreciation,
Greg
 
D

Dave Peterson

Take a look at Debra's site again.

It's more of a process than anything else.
Hi Dave,

I guess I'm being a "real dunce case" about this. Can you send me a sample
Excel Spreadsheet describing how to do this process using the data I posted
above? You can send the file to gamouning at yahoo.com.

With appreciation,
Greg
Click to expand...
 
P

pgeraf

Greg

When you type in an Array formula you must do it the usal way, *WITHOU
typing in the braces*, since they will appear as soon as you pres
*Ctrl+Shit+Enter*. Accordingly to the example I put in my first reply
for the formula to work you must place the addresses list in Sheet
starting at cell A2 and the array formula in Sheet2 starting at cel
A2

I would suggest you to try this construction just to see if it work
for you.

Hope it helps. :
 
G

Greg

Hi again,

I tried typing the formula as you suggested but I receive an error. Do
think there is a typo somewhere in the formula:

=IF(SUM((Sheet1!A2=Sheet2!$A$1:A1)*1)=0;Sheet1!A2,"")

It seems the semi-colon between parameters ' Sheet2!$A$1:A1)*1)=0' and
'Sheet1!A2,"")' may be causing an error. Excel wants to correct it and when
it does it replaces the semi-colon with a colon. However when this happens I
receive the '#REF!' error message. Any furhter recommendations?

-Greg
 
P

pgeraf

No, Greg,
it seems you are typing in the formula correctly. The only thing
could think of is sending you my own Excel workbook. I just do not kno
if this is allowed by the forum moderator: if so I would ask you whic
e-mail address I should send the file to.

Regards :
 
G

Greg

Hi,

Answer found:

I posted my question to another Excel newsgroup and received the following
reply which I was able to replicate and does resolve my problem:

Source email list is in A1 down

In B1: =IF(A1="","",IF(COUNTIF(A:A,A1)>=2,"",ROW()))

In C1: =IF(ISERROR(SMALL(B:B,ROW())),"",
INDEX(A:A,MATCH(SMALL(B:B,ROW()),B:B,0)))

Select B1:C1, copy down to last email in col A

Col C returns the required list, viz.:

Note: The above solution requires that all preceding and trailing angle
brackets "<>" be removed from the e-mail address.

You can also use the following weblinks to see the answer:

http://groups.google.com/group/microsoft.public.excel/msg/1d1a3dfe9a785d49

http://groups.google.com/group/microsoft.public.excel/msg/2bb80d8a2e127197

I wish to thank everyone who took time to respond and provided alternative
suggestions. Your support is greatly appreciated.

-Greg
 
Top