Need help finding non-duplicates email addresses...

  • Thread starter Thread starter gamouning
  • Start date Start date
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
 
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 address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
Nam (e-mail address removed)
(e-mail address removed)
Richard (e-mail address removed)
Robert (e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

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

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

and save ONLY the non-duplicate email addresses:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
Nam (e-mail address removed)
(e-mail address removed)
Richard (e-mail address removed)
Robert (e-mail address removed)
(e-mail address 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 address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
Nam (e-mail address removed)
(e-mail address removed)
Richard (e-mail address removed)
Robert (e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

Whereas I want the following results:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
Nam (e-mail address removed)
(e-mail address removed)
Richard (e-mail address removed)
Robert (e-mail address removed)
(e-mail address removed)

The major difference is I don't the following addresses to appear:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address 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
Click to expand...

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

---
 
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
 
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
 
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
 
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
 
Diva said:
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!
Click to expand...

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.

---
 
You're welcome, Greg ! Thanks for the feedback.
See also Peo's advanced filter set-up which provides an alternative
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help finding non-duplicate email addresses... 16
AOL email scam 2
Email Bounce Error 3
Column data comparison - what has been deleted and what has been added 8
Emailing from cells 1
How do I invoice a cost to a custom built MS Access Accounting Database? 0
Find Mismatched Data 5
Duplicate Entries 3

Back
Top