Vba to find duplicates in a cell

[Chefsohail]

Hi Team,

I need your expertise with a code that can highlight all cells in a row if the cell contains any duplicate statement. The duplication has to be within the cell.

Eg:

I am not sure if this is practically possible in excel. In case if it isn't at least the below alternative will help.

Alternative: A code that can highlight all cells in a row if the cell contains any duplicate words except for articles, prepositions and conjunctions. The duplication has to be within the cell.

I hope the examples above illustrates the requirement. I do not need the remarks column. Only the cell needs to be highlighted.

 

[Chefsohail]

Try this version

Sub Highlight_Dupes_Min_Letters_v2()
  Dim RX As Object, d As Object
  Dim i As Long
  Dim a As Variant, itm As Variant
  Dim s As String, Pat1 As String, Pat2 As String
 
  Const MinLetters As Long = 2  '<- Edit as required
  Const IgnoreWords As String = "is|the|an|a|while|wherever|where|whenever|when|though|that|than|so that|since|only|once|lest|if|even|because|as|although" _
                                & "|so|yet|or|but|Nor|And|For|up|under|toward|to|till|through|over|outside|of|onto|on|off|next to|near|into|inside|in|from" _
                                & "|for|during|down|by|between|beside|beneath|below|behind|before|away from|at|around|among|along|against|after|across|above"
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set RX = CreateObject("VBScript.RegExP")
  RX.Global = True
  Pat1 = "\b(" & IgnoreWords & ")\b"
  Pat2 = "\b[^ ]{" & MinLetters & ",}\b"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      d.RemoveAll
      s = a(i, 1)
      RX.Pattern = Pat1
      s = RX.Replace(s, "")
      RX.Pattern = Pat2
      For Each itm In RX.Execute(s)
        If d.exists(CStr(itm)) Then
          .Cells(i).Interior.Color = vbYellow
          Exit For
        Else
          d(CStr(itm)) = 1
        End If
      Next itm
    Next i
  End With
End Sub

My small sample data and results:

Chefsohail.xlsm
	A
1	Data
2	elephant next to rhino next to hippo
3	book paper book
4	books paper books
5	the cat sat on the mat
6	one elephant chased another elephant
7	red train, red car, blue train
8	M.M Cyprus
9	Peter is excellent at macro codes. He is a master in Excel and VBA
10	Peter is hopeless at cooking and hopeless as a debater
Sheet2

Awesome.. Thanx Peter...
Will try and test.

Do you conduct macro classes?

Will you teach me? My Macro Guru.

 

[Peter_SSs]

Awesome.. Thanx Peter...
Will try and test.

Hope it works from you. ?
As I mentioned before, there may be some issues because some of your exclusions are multiple words rather than single words but let's see how it goes.

Do you conduct macro classes?

No

Will you teach me?

Only by possibly answering specific questions (if they interest me) here in the forum.