Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Microsoft Excel, I want to filter entries so that only unique domain names appear. Suppose I have 10 records:

 1. www.manish.com
 2. joo.mj.com 
 3. ko.kiop.eu  
 4. mail.manish.com  
 5. kio.manish.eu 
 6. ftp.mj.com  
 7. ftp.kiop.com
 8. www.makil.com 
 9. www.craftio.com 
10. extension.telep.com

My desired result (unique domain names) would be:

 1. www.manish.com
 2. joo.mj.com
 3. ko.kiop.com
 4. www.makil.com 
 5. www.craftio.com 
 6. extension.telep.com
share|improve this question
    
I suppose Developer by Blood does not mind sharing with us at least a relevant piece of his efforts - just in support of What have you tried? – Peter L. Feb 19 '13 at 6:33
    
@PeterL.: Though being new to excel, I have tried as much as I can like searching for a value and deleting corresponding row. Help me on how to extract domain name from a string like just mj from joo.mj.com – Developer by Blood Feb 19 '13 at 6:44
    
It's unclear from question why you select as unique e.g. joo.mj.com - and not ftp.mj.com? what is the criteria? – Peter L. Feb 19 '13 at 6:45
    
It is like the domain name should be unique. I would select anyone from joo.mj.com and ftp.mj.com whichever comes first (like First Come First Serve) and delete the rest. Clear @PeterL.? – Developer by Blood Feb 19 '13 at 6:51
    
I suppose your actual data does not have these leading numbers like 1. ? – Peter L. Feb 19 '13 at 6:54
up vote 0 down vote accepted

Assuming the input conditions and data is placed in column A starting A1, do the following:

  1. Type in B1: =MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1) and autofill down the list - this will return domain name, or more precisely - part of string between 1st and 2nd .
  2. Type in C1: =VLOOKUP("*"&B1&"*",A:A,1,0) and autofill down the list - this will return the first match for the domain name from the array in column A.
  3. Copy and paste special as values entire column C to e.g. column D.
  4. Use Ribbon Data > Remove Duplicates to remain only unique records.

Sample file: https://www.dropbox.com/s/35wtuqsex7u57as/UnikueDomainNames.xlsx

share|improve this answer
    
Unable to find sample file ! – Developer by Blood Feb 19 '13 at 7:15
    
Try again, this works fine unless dropbox access is blocked in your network. In any case formulas are working, do the job on your own. – Peter L. Feb 19 '13 at 7:16
    
Wohooo! The code works great :) Thanks Peter, you're genius in Excel :) P.S.: Will close this after I am done with the project. In case of any assistance, will revert back to you :) – Developer by Blood Feb 19 '13 at 7:24
    
@DeveloperbyBlood Glad to help! As soon as this Q is solved - submit a new one for future cases, but tend to show your efforts first - such way people tend to more willingly to help) Good luck! – Peter L. Feb 19 '13 at 7:26
    
Hey Peter, there is one modification from the superiors. They told I want both i.e. www.google.co.in and www.google.co.uk and in links like www.google.co.in and mail.google.co.in the first come dirst serve should follow. Can you help me with this? – Developer by Blood Feb 19 '13 at 7:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .