software

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:
gt; Hi,
gt;
gt; I have a list of email addresses which I would like to find only the
gt; new email addresses within the list. Inside of my Excel worksheet I
gt; have a row of e-mail addresses. Some of the names are duplicated which
gt; tells me they already exist somewhere in the row. I wish to display
gt; only the new names. Can you tell me how this can be done in Excel?
gt;
gt; Thanks in advance for your assistance,
gt; 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

quot;Paul Lautmanquot; wrote:

gt;
gt; Are you saying that you have 2 lists and you wish to find what items from
gt; one list do not exist in the other one?
gt;
gt; If so are both these lists horizontal?Greg wrote:
gt; Hi Paul,
gt;
gt; Thanks for your reply. No the lists are vertical but can be horizonal
gt; if that helps. And it is currently one list but I can make them into
gt; two list as well.
gt;
gt; -Greg
gt;
gt; quot;Paul Lautmanquot; wrote:
gt;
gt;gt;
gt;gt; Are you saying that you have 2 lists and you wish to find what items
gt;gt; from one list do not exist in the other one?
gt;gt;
gt;gt; If so are both these lists horizontal?

If the lists are vertical, what is the quot;row of e-mail addressesquot; 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.

quot;Paul Lautmanquot; wrote:

gt; Greg wrote:
gt; gt; Hi Paul,
gt; gt;
gt; gt; Thanks for your reply. No the lists are vertical but can be horizonal
gt; gt; if that helps. And it is currently one list but I can make them into
gt; gt; two list as well.
gt; gt;
gt; gt; -Greg
gt; gt;
gt; gt; quot;Paul Lautmanquot; wrote:
gt; gt;
gt; gt;gt;
gt; gt;gt; Are you saying that you have 2 lists and you wish to find what items
gt; gt;gt; from one list do not exist in the other one?
gt; gt;gt;
gt; gt;gt; If so are both these lists horizontal?
gt;
gt; If the lists are vertical, what is the quot;row of e-mail addressesquot; of which
gt; you speak?
gt;
gt;
gt;

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:
www.contextures.com/xladvfilter01.html#FilterUR

And Chip Pearson has some techniques at:
www.cpearson.com/excel/duplicat.htmGreg wrote:
gt;
gt; It might be easier to show you what I have:
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt; Nam
gt;
gt; Richard
gt; Robert
gt;
gt;
gt;
gt;
gt; I want to eliminate the duplicate addresses:
gt;
gt;
gt;
gt;
gt;
gt; and save the non-duplicate addresses.
gt;
gt; quot;Paul Lautmanquot; wrote:
gt;
gt; gt; Greg wrote:
gt; gt; gt; Hi Paul,
gt; gt; gt;
gt; gt; gt; Thanks for your reply. No the lists are vertical but can be horizonal
gt; gt; gt; if that helps. And it is currently one list but I can make them into
gt; gt; gt; two list as well.
gt; gt; gt;
gt; gt; gt; -Greg
gt; gt; gt;
gt; gt; gt; quot;Paul Lautmanquot; wrote:
gt; gt; gt;
gt; gt; gt;gt;
gt; gt; gt;gt; Are you saying that you have 2 lists and you wish to find what items
gt; gt; gt;gt; from one list do not exist in the other one?
gt; gt; gt;gt;
gt; gt; gt;gt; If so are both these lists horizontal?
gt; gt;
gt; gt; If the lists are vertical, what is the quot;row of e-mail addressesquot; of which
gt; gt; you speak?
gt; gt;
gt; gt;
gt; gt;

--

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

quot;Dave Petersonquot; wrote:

gt; You could use data|filter|advanced filter (unique records only) and put the list
gt; in a different location.
gt;
gt; Then delete the original and keep the filtered list. Make sure you have a
gt; header in your list.
gt;
gt; Debra Dalgleish's instructions:
gt; www.contextures.com/xladvfilter01.html#FilterUR
gt;
gt; And Chip Pearson has some techniques at:
gt; www.cpearson.com/excel/duplicat.htm
gt; --
gt;
gt; Dave Peterson
gt;

gt; Then delete the original and keep the filtered list.Greg wrote:
gt;
gt; Hi Dave,
gt;
gt; Thanks for your reply. I guess I am still not clear on what I want.
gt; Filtering on unique records does eliminate duplicate e-mail addresses but
gt; keeps a copy of the original. I want to eliminate the original and keep only
gt; the non-duplicates.
gt;
gt; Hope this is more clear.
gt;
gt; -Greg
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; You could use data|filter|advanced filter (unique records only) and put the list
gt; gt; in a different location.
gt; gt;
gt; gt; Then delete the original and keep the filtered list. Make sure you have a
gt; gt; header in your list.
gt; gt;
gt; gt; Debra Dalgleish's instructions:
gt; gt; www.contextures.com/xladvfilter01.html#FilterUR
gt; gt;
gt; gt; And Chip Pearson has some techniques at:
gt; gt; www.cpearson.com/excel/duplicat.htm
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

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 ,quot;quot;)}

Enjoy! --
pgeraf
------------------------------------------------------------------------
pgeraf's Profile: www.excelforum.com/member.php...oamp;userid=10067
View this thread: www.excelforum.com/showthread...hreadid=522459Thanks 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 quot;accept the changequot;. When I accept the change the results pasted
are #REF!. Can you tell me what's going on here?

Regards,
Greg

quot;pgerafquot; wrote:

gt;
gt; Hi Greg,
gt; I think what you are looking for is an Array formula. Try this:
gt;
gt; Put this formula in Sheet2!A2 and press Ctrl Shift Enter to convert it
gt; to an array formula. Then copy it all the way down to reproduce the
gt; number of cells where the e-mail addresses are included in Sheet1 (the
gt; e-mail addresses should start at cell A2 in Sheet1). Hopefully, if it
gt; woks as it did with me, it will eliminate all duplicates. This formula
gt; will work with anything you list in Sheet1.
gt;
gt; {=IF(SUM((Sheet1!A2=Sheet2!$A$1:A1)*1)=0;Sheet1!A2 ,quot;quot;)}
gt;
gt; Enjoy!
gt;
gt;
gt; --
gt; pgeraf
gt; ------------------------------------------------------------------------
gt; pgeraf's Profile: www.excelforum.com/member.php...oamp;userid=10067
gt; View this thread: www.excelforum.com/showthread...hreadid=522459
gt;
gt;