Find and highlight duplicate in Excel 2003 sheet

Question

QUESTION: sir i have a huge list of websites, but i am unable to find the duplication in my list of websites, kindly solve my this problem and tell me what i do to find duplicate of websites in my ms-excellist. for example adeex.co.uk adeex.in/job-hunting/more-ads/guaranteed-income-free-groceries-free-holidays-and-more-8 adeptr.com/classifieds/index.php?page=view_ad&id=1773 adeptr.com/classifieds/index.php?page=view_ad&id=678 adeum.com/EN/offers/jobs/other/000040936.html adforindia.com adforindia.com/ad17916.htm

i need to find duplicatelike adeex, adeptr that are repated many times in my list.

ANSWER: If you only want to check against the first string to the right of the period, then (assuming your data starts in A1) in B1 put in

=Left(A1,find(".",A1)-1) and drag fill it down the columns.

C1 put in =If(countif($B$1:B1,B1)>1,"Duplicate","")

then drag fill that down column C. that should mark the duplicates (the 2nd and beyond occurance).

---------- FOLLOW-UP ----------

QUESTION: thanks for help me but my problem is that websites save like as:

http://www.lahore.olx.com.pk olx.com.pk classified.olx.com.pk http://atlanta.classifieds4me.com/ http://dehli.classifieds4me.com/ http://gujrat.classifieds4me.com/

but i need just highlight the"olx" and "classifieds4me" any where in website example above,

Answer

If you only have those to words to look for, then you can replace the formula in B1:

=Left(A1,find(".",A1)-1

with =if(countif(A1,"*olx*")>0,"olx",if(countif(A1,"*classifieds4me*")>0,"classifieds4me",""))

then the formula in c1 would be the same

I suspect that there are more than two words. If so, you would probably need a macro. There is no way for me to know what words you are interested in. There is no way I would have picked classifieds4me as one of the words rather than atlanta or dehli, especially after you examples in your first question. So there isn't much more I can offer at this point since I don't know all about what you want to do.

Question

sir i have a huge list of websites, but i am unable to find the duplication in my list of websites, kindly solve my this problem and tell me what i do to find duplicate of websites in my ms-excellist. for example adeex.co.uk adeex.in/job-hunting/more-ads/guaranteed-income-free-groceries-free-holidays-and-more-8 adeptr.com/classifieds/index.php?page=view_ad&id=1773 adeptr.com/classifieds/index.php?page=view_ad&id=678 adeum.com/EN/offers/jobs/other/000040936.html adforindia.com adforindia.com/ad17916.htm

i need to find duplicatelike adeex, adeptr that are repated many times in my list.

Answer

Suppose your list is in A1:A500 In B1 enter: =LEFT(A1,FIND(".",A1)-1) and fill to B500. Select Filter/Advanced Filter, use B1:B500 as your list range, and select the Unique Items checkbox. Then select Column A, then press Alt/semi-colon (Alt/;), then Copy, then paste to a new location and you'll only have the unique guys.

HTH