Need to identify repeated text in a string

[bwiddoes]

Hey All,

I'm thinking I need to go into VBA or macros to get this done, which I don't have a lot of experience in. Here is what I want to identify..

We have long descriptions tied to products, and we have about 50K products we need to review. I want to find where a word is repeated twice, one right after the other. ie..

ID,Long Desc
123456,"This product description has an error error where I need to review the duplicate word"
456789,"This description is fine and has duplicate words, but not words that are repeated one after the other, and so I don't need to review it"

Any assistance is much appreciated. Thank you,

[mikerickson]

Perhaps something like

Dim oneCell as Range
Dim Words As Variant
Dim I As Long

For Each oneCell in Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Words = Split(CStr(oneCell.Value), " ")

    For I = 1 to UBound(Words)
        If LCase(Words(I)) = LCase(Words(I-1)) Then
            oneCell.Interior.ColorIndex = 3
            Exit For
        End If
    Next i
Next oneCell

[bwiddoes]

Perhaps something like

Dim oneCell as Range
Dim Words As Variant
Dim I As Long

For Each oneCell in Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    Words = Split(CStr(oneCell.Value), " ")

    For I = 1 to UBound(Words)
        If LCase(Words(I)) = LCase(Words(I-1)) Then
            oneCell.Interior.ColorIndex = 3
            Exit For
        End If
    Next i
Next oneCell

Thank you for the reply. When I run this one I get an error, Invalid outside proceedure.

[jindon]

Sub tset()
    Dim r As Range, m As Object
    Columns(1).Font.Bold = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Global = True
        .Pattern = "\b(\S+) \1\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Bold = True
            Next
        Next
    End With
End Sub

[bwiddoes]

Sub tset()
    Dim r As Range, m As Object
    Columns(1).Font.Bold = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Global = True
        .Pattern = "\b(\S+) \1\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Bold = True
            Next
        Next
    End With
End Sub

Thanks, this is exactly what I am looking for. After applying this to the data I found that I need an identifier, ideally in another field. I would like to have an identifier to sort or filter by. Is this possible?

Thanks again. This is going to save an enormous amount of work.

[jindon]

Do you mean like this?
Otherwise, need to see how you want it in a sample workbook.

Sub tset()
    Dim r As Range, m As Object
    Columns(1).Font.Bold = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Global = True
        .Pattern = "\b(\S+) \1\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then r(, 2).Value = "Dup"
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Bold = True
            Next
        Next
    End With
End Sub

[bwiddoes]

Do you mean like this?
Otherwise, need to see how you want it in a sample workbook.

Sub tset()
    Dim r As Range, m As Object
    Columns(1).Font.Bold = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Global = True
        .Pattern = "\b(\S+) \1\b"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then r(, 2).Value = "Dup"
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Bold = True
            Next
        Next
    End With
End Sub

Perfecto! This is exactly what I needed. I appreciate all the assistance. Have a great day!

[jindon]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.