Unique Filter Question

[rockproper]

I have a long list of websites. They are sorted alphebetically.

Out of this list I am trying to pull the first two occurrences of these websites into a separate list.

For example:

List A
http://www.websiteA.com/
http://www.websiteA.com/123
http://www.websiteA.com/456
http://www.websiteA.com/789
http://www.websiteB.com/
http://www.websiteB.com/987
http://www.websiteB.com/456
http://www.websiteC.com/
http://www.websiteC.com/123
http://www.websiteC.com/456
http://www.websiteC.com/789

Would become

List B
http://www.websiteA.com/
http://www.websiteA.com/123
http://www.websiteB.com/
http://www.websiteB.com/456
http://www.websiteC.com/
http://www.websiteC.com/123

To put this another way I need to get the root url and a sample webpage into another list.

I believe some combo of these functions would do it but I cant make it work.

Potential Helpful Functions:

=LEFT(A1,SEARCH("/",A1,8))

=If(D1=D2, "", D1)


The first one recoginzes the root url

the second one checks for duplicates.

I am thinking it would be great to create a column that gave a * for the first two occurances (or perhaps even numbered the occurances 1, 2, 3)

I could then filter and find the first two.

I hope this makes sense. Thanks for reading and being up to the challenge!!

Much appreciated,

Casey

[benishiryo]

hi Casey, i wasn't able to think of a solution just using 1 column. but here's 1 that uses 2 columns assuming your data starts from A2:
in B2:
=REPLACE(A2,FIND(".com",A2),255,"")
in C2:
=COUNTIF($B$2:B2,B2)

Column C would be the occurances. anything 3 & above would be unwanted. you can then filter just for 1 & 2

[rockproper]

Thanks so much for your reply!

It seems to be working for some urls but not on others.

Check out the screenshot attached.

Sites like http://4do.org/ are causing an error in the b column

Also http://askalocal.co.za/

As I am writing this I am realizing that these are not ".com" site.

Any idea on how to fix?

Thanks!

[benishiryo]

found a formula while googling. modified it to better suit your needs. try this for B2 instead:
=LEFT(A1,FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))