Excel Forumla

[unmlobo]

We are looking for a formula that will sort through a column of email address and remove email address that are in there only one time. Leaving us with only the emails that are listed more then once.

 

[xlhelp]

Use countif to count the email addresses. Filter all the counts that are more than 1 and you will have your duplicate addresses.

Member- AAAA Association Against Acronym Abusers

 

[unmlobo]

Thanks for the help but how do you do the filtering part?

 

[xlhelp]

Once you have used countif, you will have a column with count of e-mail addresses. Some will have number 1 beside it and the others with 2,3,4 etc. Since you want get rid of all e-mail addresses that are single, you would go to Data, Filter, AutoFilter. Use the drop down arrow in the count coulmn. Choose custom and you want "is greater than" and in the right hand box you would want to select 1. This will give you the addresses that you want to keep.

Hit F5 on your keyboard, click special and select visible cells. Copy and paste to a different area or spreadsheet.

Member- AAAA Association Against Acronym Abusers

 

[yogia]

Hi unmlobo:

Using the strategy suggested by xlhelp, you can also use AdvancedFilter by using a computed criteion -- this way you don't have to use an additional column for the COUNTIF. See the following illustration ...

  |A               B
--|-------------------------
 1|ITEM		   [highlight]      [/highlight]
 2|email_1		[highlight] TRUE [/highlight]
 3|email_2		
 4|email_3		
 5|email_1		
 6|email_5		
 7|email_6		
 8|email_7		
 9|email_8		
10|email_8		
11|email_10		
12|email_8		
13|email_12		
14|email_13		
15|email_14

for computed criterion, the formula in cell B2 is ...
=COUNTIF($A:$A,$A2)>1

Now when you AdvanceFilter records in column A using the criterion range B1:B2, you get the following result ...

  |A              
--|----------
 1|ITEM		   
 2|email_1		
 5|email_1		
 9|email_8		
10|email_8		
12|email_8

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com