Find duplicate URLs.

[ahsanali32]

I have got a Google spreadsheet with URLs in rows. Examples:

shelikesfood.com/30-easy-vegetarian-one-pot-dinner-recipes/

asaucykitchen.com/gluten-and-dairy-free-vegetarian/

vegetarians.co.nz/vegetarian-recipes/

yummyaddiction.com/september-fruits-and-vegetables/

shelikesfood.com/vegetarian-recipes/

I would like to create a formula that checks URLs before "." and highlights them. In the above case, only shelikesfood will be highlighted.

Hope it makes sense.

 

[ouvay]

Try this

Private Sub MarkDuplicates()
    On Error Resume Next
   Dim Cl As Range
   Dim ValU As String
   Dim rng As Range
   Dim Col As String



   Col = "A" ''''''''''''''''''' This assumes that your links are in column A ... if you would like to change A to whatever you like
   With CreateObject("scripting.dictionary")
      For Each Cl In Range(Col & "2", Range(Col & Rows.Count).End(xlUp))
         ValU = Left(Cl.Value, FindPeriod(Cl.Value) - 1)
         If Not .exists(ValU) Then
            .Add ValU, Nothing
         Else
            If rng Is Nothing Then
               Set rng = Cl
            Else
               Set rng = Union(rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not rng Is Nothing Then rng.Interior.ColorIndex = 3
End Sub
Private Function FindPeriod(Str As String) As Long
    Dim i As Long, CH As String
    FindPeriod = 0
    For i = 1 To Len(Str)
        If Mid(Str, i, 1) Like "[.]" Or Mid(Str, i, 1) Like "[.]" Then
            FindPeriod = i
            Exit Function
        End If
    Next i
End Function