Need help finding non-duplicates email addresses...

[gamo...@gmail.com]

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

[Diva]

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

[Diva]

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

[gamo...@gmail.com]

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
Anthony....@yahoo.com
Arthur...@gmail.com
Bud....@yahoo.com
Curt....@hotmail.com
Curt....@hotmail.com
Dana....@aol.com
Darren....@aol.com
Darren....@aol.com
Dennis...@hotmail.com
Don.K...@actors.com
Don.K...@actors.com
Edna....@aol.com
Frankli...@yahoo.com
Jack...@gmail.com
Lou....@actors.com
Nam June...@comcast.net
Phil....@comcast.net
Richard De.An...@gmail.com
Robert L.S...@actors.com
William...@comcast.net
Yao.W...@hotmail.com
Yao.W...@hotmail.com

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

Curt....@hotmail.com
Darren....@aol.com
Don.K...@actors.com
Yao.W...@hotmail.com

and save ONLY the non-duplicate email addresses:

Anthony....@yahoo.com
Arthur...@gmail.com
Bud....@yahoo.com
Dana....@aol.com
Dennis...@hotmail.com
Edna....@aol.com
Frankli...@yahoo.com
Jack...@gmail.com
Lou....@actors.com
Nam June...@comcast.net
Phil....@comcast.net
Richard De.An...@gmail.com
Robert L.S...@actors.com
William...@comcast.net

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

[Diva]

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

[gamo...@gmail.com]

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:

Anthony....@yahoo.com
Arthur...@gmail.com
Bud....@yahoo.com
Curt....@hotmail.com
Dana....@aol.com
Darren....@aol.com
Dennis...@hotmail.com

Don.K...@actors.com
Edna....@aol.com
Frankli...@yahoo.com
Jack...@gmail.com
Lou....@actors.com
Nam June...@comcast.net
Phil....@comcast.net
Richard De.An...@gmail.com
Robert L.S...@actors.com
William...@comcast.net
Yao.W...@hotmail.com
Yao.W...@hotmail.com

Whereas I want the following results:

Anthony....@yahoo.com
Arthur...@gmail.com
Bud....@yahoo.com
Dana....@aol.com
Dennis...@hotmail.com
Edna....@aol.com
Frankli...@yahoo.com
Jack...@gmail.com
Lou....@actors.com
Nam June...@comcast.net
Phil....@comcast.net
Richard De.An...@gmail.com
Robert L.S...@actors.com
William...@comcast.net

The major difference is I don't the following addresses to appear:
Curt....@hotmail.com
Darren....@aol.com
Don.K...@actors.com
Yao.W...@hotmail.com

-Greg

[Max]

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
---
<gamo...@gmail.com> wrote
....

> In an Excel WorkSheet starting with cell A1 I have:
>
> emailaddress
> Anthony....@yahoo.com
> Arthur...@gmail.com
> Bud....@yahoo.com
> Curt....@hotmail.com
> Curt....@hotmail.com
> Dana....@aol.com
> Darren....@aol.com
> Darren....@aol.com
> Dennis...@hotmail.com
> Don.K...@actors.com

> Don.K...@actors.com
> Edna....@aol.com
> Frankli...@yahoo.com
> Jack...@gmail.com
> Lou....@actors.com
> Nam June...@comcast.net
> Phil....@comcast.net
> Richard De.An...@gmail.com
> Robert L.S...@actors.com
> William...@comcast.net
> Yao.W...@hotmail.com
> Yao.W...@hotmail.com
>

> Inside the same WorkSheet I want to eliminate the following duplicate
> email addresses:
>
> Curt....@hotmail.com
> Darren....@aol.com
> Don.K...@actors.com
> Yao.W...@hotmail.com
>
> and save ONLY the non-duplicate email addresses:
>

[Max]

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

[kle...@gmail.com]

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

[Diva]

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

[Max]

<kle...@gmail.com> wrote

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

I'm not sure this will produce the required result here. If you read the
OP's detail post, he wants to retain only those email which occur once in
the list. Email which occur more than once in the list were to be removed
completely (all occurences).

[gamo...@gmail.com]

Hi Max,

Finally! Yes, this is what I want. However, I must admit that I have
not had a chance to test it completely. I did download your file and
the results I requested are in place. Once, I've had a chance to test
your formulas on my more than 500 entries, I'll report back my results
to the newsgroup. In the meantime, this looks like the best example of
what I have been trying to accomplish.

Thank you ever so much!

-Greg

[gamo...@gmail.com]

Hi Max,

Once again your answer is correct! The "Advance Filter with Unique
Record" method does not produce my required results. I wish to delete
all occurences of duplicate email addresses. Using this method retains
one copy of the duplicate email addresses which IS NOT what I want.

Regards,
Greg

[Peo Sjoblom]

Hi Max,

you can still use advanced filter albeit not the way that was proposed,
assume the data are in A4:A50,
in let's say D2 put

=COUNTIF($A$4:$A$50,A4)=1

leave D1 blank

select the table

do data>filter>advanced filter, select $A$3:$A$50 as filter range (assuming
there is a header in A3 otherwise put one there), in the criteria range put
$D$1:$D$2, select copy to another location and click OK

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

"Max" <demec...@yahoo.com> wrote in message
news:%23gTzY7o...@TK2MSFTNGP12.phx.gbl...

[Diva]

Hi,
There is another method which works definitely.
If your addresses are in column A, then enter this formula in B1.
=IF(COUNTIF(A:A,A1)>1,"",A1) and drag. You have what you want!
Divakar

[Max]

Thanks, Peo.
Yes, that's an alternative !

[Max]

"Diva" <divakarp...@yahoo.com> wrote

Yes, the core formula's similar to the one suggested earlier. But the OP
probably wants all the email to be bunched up neatly at the top as the
output, with all intermediate blank rows removed. This is the result
achieved with the earlier formulas set-up / Peo's advanced filter set-up.

[Max]

You're welcome, Greg ! Thanks for the feedback.
See also Peo's advanced filter set-up which provides an alternative

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

<gamo...@gmail.com> wrote in message
news:1142706748....@v46g2000cwv.googlegroups.com...