Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.