Need help finding non-duplicates email addresses...

G

gamouning

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
 
D

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
 
D

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
 
G

gamouning

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
[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]

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

[email protected]
[email protected]
[email protected]
[email protected]

and save ONLY the non-duplicate email addresses:

[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]

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
 
D

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
 
G

gamouning

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:

[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]

Whereas I want the following results:
[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]

The major difference is I don't the following addresses to appear:
[email protected]
[email protected]
[email protected]
[email protected]

-Greg
 
K

kletcho

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

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
 
M

Max

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

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).
 
G

gamouning

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
 
G

gamouning

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
 
P

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
 
D

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
 
Top