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

Greg wrote:
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

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?

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

"Paul Lautman" wrote:


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?

Greg wrote:
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

"Paul Lautman" wrote:


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?

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

It might be easier to show you what I have:
















Nam

Richard
Robert




I want to eliminate the duplicate addresses:





and save the non-duplicate addresses.

"Paul Lautman" wrote:

Greg wrote:
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

"Paul Lautman" wrote:


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?

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

You could use data|filter|advanced filter (unique records only) and put the list
in a different location.

Then delete the original and keep the filtered list. Make sure you have a
header in your list.

Debra Dalgleish's instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm


Greg wrote:

It might be easier to show you what I have:
















Nam

Richard
Robert




I want to eliminate the duplicate addresses:





and save the non-duplicate addresses.

"Paul Lautman" wrote:

Greg wrote:
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

"Paul Lautman" wrote:


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?

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




--

Dave Peterson

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

"Dave Peterson" wrote:

You could use data|filter|advanced filter (unique records only) and put the list
in a different location.

Then delete the original and keep the filtered list. Make sure you have a
header in your list.

Debra Dalgleish's instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm
--

Dave Peterson

Then delete the original and keep the filtered list.


Greg wrote:

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

"Dave Peterson" wrote:

You could use data|filter|advanced filter (unique records only) and put the list
in a different location.

Then delete the original and keep the filtered list. Make sure you have a
header in your list.

Debra Dalgleish's instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm
--

Dave Peterson


--

Dave Peterson

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 it
to an array formula. Then copy it all the way down to reproduce the
number of cells where the e-mail addresses are included in Sheet1 (the
e-mail addresses should start at cell A2 in Sheet1). Hopefully, if it
woks as it did with me, it will eliminate all duplicates. This formula
will work with anything you list in Sheet1.

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

Enjoy!


--
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: http://www.excelforum.com/member.php...o&userid=10067
View this thread: http://www.excelforum.com/showthread...hreadid=522459

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

"pgeraf" wrote:


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 it
to an array formula. Then copy it all the way down to reproduce the
number of cells where the e-mail addresses are included in Sheet1 (the
e-mail addresses should start at cell A2 in Sheet1). Hopefully, if it
woks as it did with me, it will eliminate all duplicates. This formula
will work with anything you list in Sheet1.

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

Enjoy!


--
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: http://www.excelforum.com/member.php...o&userid=10067
View this thread: http://www.excelforum.com/showthread...hreadid=522459

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

"Dave Peterson" wrote:

Then delete the original and keep the filtered list.


Greg wrote:

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

"Dave Peterson" wrote:

You could use data|filter|advanced filter (unique records only) and put the list
in a different location.

Then delete the original and keep the filtered list. Make sure you have a
header in your list.

Debra Dalgleish's instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm
--

Dave Peterson


--

Dave Peterson

Take a look at Debra's site again.

It's more of a process than anything else.

Greg wrote:

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

"Dave Peterson" wrote:

Then delete the original and keep the filtered list.


Greg wrote:

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

"Dave Peterson" wrote:

You could use data|filter|advanced filter (unique records only) and put the list
in a different location.

Then delete the original and keep the filtered list. Make sure you have a
header in your list.

Debra Dalgleish's instructions:
http://www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
http://www.cpearson.com/excel/duplicat.htm
--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Greg,

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

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

Hope it helps. :)


--
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: http://www.excelforum.com/member.php...o&userid=10067
View this thread: http://www.excelforum.com/showthread...hreadid=522459

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

"pgeraf" wrote:


Greg,

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

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

Hope it helps. :)


--
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: http://www.excelforum.com/member.php...o&userid=10067
View this thread: http://www.excelforum.com/showthread...hreadid=522459

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

Regards :)


--
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: http://www.excelforum.com/member.php...o&userid=10067
View this thread: http://www.excelforum.com/showthread...hreadid=522459

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/micro...1a3dfe9a785d49

http://groups.google.com/group/micro...b80d8a2e127197

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

-Greg

"Greg" wrote:

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

good sir

i need emails addresses

url:http://www.ureader.com/msg/103431849.aspx