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

Hi,
Select the row. GoTo conditional formatting, Select Formula-Is option
in first combo, enter =COUNTIF(1:1,A1)>1 in second box. Select format
and select suitable font,colour etc. Click OK. Every repeating item in
that Row will be highlighted with the formating you have selected.
Divakar

Hi,
The above formula works if the items are in first row. Please make
change in row. If you want to check then enter =COUNTIF(2:2,A2)>1.
Divakar

Hi Divakar,

Thank you for your post. Unfortunately, I'm not having much success
using your method. Perhaps it would be simplier to show you "what I
have" and "what I want".

In an Excel WorkSheet starting with cell A1 I have:

emailaddress
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
Nam (E-Mail Removed)
(E-Mail Removed)
Richard (E-Mail Removed)
Robert (E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)

Inside the same WorkSheet I want to eliminate the following duplicate
email addresses:

(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)

and save ONLY the non-duplicate email addresses:

(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
Nam (E-Mail Removed)
(E-Mail Removed)
Richard (E-Mail Removed)
Robert (E-Mail Removed)
(E-Mail Removed)

BTW, it has been suggested that I read Debra Dalgleish's instructions
on the following weblink:

http://www.contextures.com/xladvfilter01.html#FilterUR

Unfortunately, I'm not having very much success with getting my results
using these instructions either. So any support recommendations from
this list is greatly appreciated.

Kind regards,
Greg

Method 1:
Sort column A (ascending). In B2 enter =IF(A1=A2,"",A2). Drag the
formula as you require in column B. Now column B has only unique items.
Copy all items in (not whole column)column B and pastespecial as values
somewhere else and sort descending to remove blanks.
Method 2:
Click on some item in Column A. Column A should not have blank rows in
between.(if there are blanks sort descending to remove blanks). Choose
Data>Filter>Advanced. In popup dialogue, select copy to another
location and fill the list range with proper range to be filtered.
Leave blank the criteria range, select a single cell in copyto box and
select unique records only box. Click OK. Unique items will be copied
to the location specified.
If you still don't get the required result. I will prepare ans send you
a demo workbook.
Divakar

Hi Diva,

I tried Methods 1 & 2 and neither produce results I want. The
duplicates are deleted but not the orignal item. For example, both of
your methods produce the following results:

(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
Nam (E-Mail Removed)
(E-Mail Removed)
Richard (E-Mail Removed)
Robert (E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)

Whereas I want the following results:
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
Nam (E-Mail Removed)
(E-Mail Removed)
Richard (E-Mail Removed)
Robert (E-Mail Removed)
(E-Mail Removed)

The major difference is I don't the following addresses to appear:
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)
(E-Mail Removed)

-Greg

This should work for you (tested ok over here) ..

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.:
> .. ONLY the non-duplicate email addresses
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<(E-Mail Removed)> wrote
.....
> In an Excel WorkSheet starting with cell A1 I have:
>
> emailaddress
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> Nam (E-Mail Removed)
> (E-Mail Removed)
> Richard (E-Mail Removed)
> Robert (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
>
> Inside the same WorkSheet I want to eliminate the following duplicate
> email addresses:
>
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
>
> and save ONLY the non-duplicate email addresses:
>
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> (E-Mail Removed)
> Nam (E-Mail Removed)
> (E-Mail Removed)
> Richard (E-Mail Removed)
> Robert (E-Mail Removed)
> (E-Mail Removed)

A sample implementation is available at:
http://www.savefile.com/files/9998189
Extract only non-duplicates.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You should use an advanced filter and check the unique records button
and select filter list in place.

Note that this method works correctly only if your data begins from
second row and not first.
Sort column A (ascending). If first row is not blank insert one row.
If your data starts from A2 then, in B2 enter
=IF(OR(A2=A1,A2=A3),"",A2). Drag the formula as you require in column
B. Now column B has only unique items you want. Note that if you enter
(or drag) the formula in B1 it will show error!. So I mentioned to keep
first row blank. Copy all items in (not whole column)column B and
pastespecial as values somewhere else and sort descending to remove
blanks.
Divakar