如何获取目标列中的重复值：技巧是在每个循环中删除原始单元中所有出现的测试字。
将示例代码的第二部分替换为以下内容：

'temp string for concat results
Dim sResult As String
'temp string for deduplication
Dim sHaystack As String
sHaystack = cellValueA 

For Each word In wordsA
    'erase test word in original string
    sTmp = Replace(sHaytack,word,"")

    'if length reduction is bigger than tested word...
    If (Len(sHaystack) - Len(sTmp)) > Len(word) Then
      'means the word occurs more than once, so saving it
      'concatenating previous result if any
      If sResult <> "" Then
        sResult = ", " & word
      Else
        sResult = word
      End If
    Else
      'if no length reduction or equal to word, do nothing
    End If

  'next loop with a testword-stripped string
  sHaystack = sTmp

Next word
'now paste concatenated result in column C
Cells(i, 3).Value = sResult

您应该使用字典、集合或数组来记录您找到并处理的内容，以供后续处置，而不是立即离开循环。

Sub Repeatwrd()
    Dim lastRow As Long
    Dim i As Long
    Dim cellValueA As String
    Dim cellValueB As String
    Dim wordsA As Variant
    Dim wordsB As Variant
    Dim wordIndex As Long
    Dim word As Variant
    Dim wordCount As Integer
    
    ' Get the last row of data in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row of data in column A and compare with column B
    For i = 1 To lastRow
        ' Get the values in columns A and B for the current row
        cellValueA = Cells(i, 1).Value
        cellValueB = Cells(i, 2).Value
        
        ' Split the strings into words
        wordsA = Split(cellValueA, " ")
        wordsB = Split(cellValueB, " ")
        
        ' Loop through each word in column A
        For wordIndex = 0 To UBound(wordsA)
            ' If the word is not found in column B, highlight it in column A
            If InStr(cellValueB, wordsA(wordIndex)) = 0 Then
                word = wordsA(wordIndex)
                ' Highlight the word in column A
                Cells(i, 1).Characters(InStr(cellValueA, word), Len(word)).Font.ColorIndex = 3 ' Highlight in red
                'Add highlight word in c column
                'Cells(i, 3).Value = Cells(i, 3).Value & " " & word
    
            End If
        Next wordIndex
        
        Dim dict As Object
        Set dict = CreateObject("scripting.dictionary")
        
         ' Loop through each word in the string
        For Each word In wordsA
            ' Count the number of occurrences of the word in the string
            wordCount = UBound(Split(cellValueA, word)) - LBound(Split(cellValueA, word))
            ' If the word occurs more than once, paste it in column C for the current row
            If wordCount > 1 Then
                If Not dict.exists(word) Then
                    'ws.Cells(i, 3).Value = word
    '                 Cells(i, 3).Value = word
                     Cells(i, 3).Value = Cells(i, 3).Value & " " & word
                     dict.Add word, word
'                    Exit For ' Exit the loop once a repeated word is found
                End If
            End If
            ' If the word occurs more than once, paste it in column C for the current row
'            If wordCount > 1 Then
'                'ws.Cells(i, 3).Value = word
'                 Cells(i, 3).Value = word
'                Exit For ' Exit the loop once a repeated word is found
'            End If
        Next word
    Next i

End Sub

在VBA中创建和UDF来检查这个，考虑一个单词在2个空格之间，或者在字符串的开头有尾随空格，或者在字符串的结尾有前导空格。把所有这些案子加起来。如果总和大于0，则为重复。

此UDF需要2个参数：rng1是要检查的字，rng2是包含rng1的重复的范围。请注意，输出排序是不同的，这取决于您如何设置rng1和rng2，但单词是相同的：

Public Function GET_DUP_WORDS(ByVal rng1 As Range, ByVal rng2 As Range) As String
Dim wordArray As Variant
Dim i As Long
Dim test As Long
Dim targetWord As String
wordArray = Split(rng1, " ")

For i = LBound(wordArray) To UBound(wordArray)
    'word is something between 2 blanks
    'or at the end of string with a leading blank
    'or at start of string with a trailing blank
    
    targetWord = wordArray(i)
    
    test = Evaluate(Replace("=SUM(COUNTIF(" & rng2.Address & ",{""* targetWord *"",""targetWord *"",""* targetWord""}))", "targetWord", targetWord))
    
    If test > 0 Then GET_DUP_WORDS = GET_DUP_WORDS & targetWord & ", "
Next i

'get rid of last comma in case there is something
If Len(GET_DUP_WORDS) > 0 Then GET_DUP_WORDS = Left(GET_DUP_WORDS, Len(GET_DUP_WORDS) - 2)

End Function

C列中的公式为：=GET_DUP_WORDS(A1;B1)列D中的公式相反：=GET_DUP_WORDS(B1;A1)
您可以在SUB中合并此UDF以应用全范围。