I had around 20k rows filled with descriptions in column A. The words are delimited with spaces. I needed to find repeated words (not letters) available in column A and paste them in column B as depicted below.
One of my friend helped me to achieve this task.
One of my friend helped me to achieve this task.
Sub FindDuplicates()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim WS As Worksheet
Dim WordArr As Variant
Dim DubStr As String
Dim WordCount As Integer
Set WS = ActiveSheet
'Loop cells
For i = 2 To WS.Cells(Rows.Count, 1).End(xlUp).Row
'Split cell words into array
WordArr = Split(WS.Cells(i, 1).Value, " ")
'Loop through each word in cell
For j = LBound(WordArr) To UBound(WordArr)
WordCount = 0
'Count the occurrences of the word
For k = LBound(WordArr) To UBound(WordArr)
If UCase(WordArr(j)) = UCase(WordArr(k)) Then
WordCount = WordCount + 1
End If
Next k
'Output duplicate words to string
If WordCount > 1 And InStr(1, DubStr, WordArr(j)) = 0 Then
DubStr = DubStr & WordArr(j) & " | "
End If
Next j
'Paste string in column B
WS.Cells(i, 2).Value = Trim(DubStr)
DubStr = ""
Erase WordArr
Next i
End Sub
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim WS As Worksheet
Dim WordArr As Variant
Dim DubStr As String
Dim WordCount As Integer
Set WS = ActiveSheet
'Loop cells
For i = 2 To WS.Cells(Rows.Count, 1).End(xlUp).Row
'Split cell words into array
WordArr = Split(WS.Cells(i, 1).Value, " ")
'Loop through each word in cell
For j = LBound(WordArr) To UBound(WordArr)
WordCount = 0
'Count the occurrences of the word
For k = LBound(WordArr) To UBound(WordArr)
If UCase(WordArr(j)) = UCase(WordArr(k)) Then
WordCount = WordCount + 1
End If
Next k
'Output duplicate words to string
If WordCount > 1 And InStr(1, DubStr, WordArr(j)) = 0 Then
DubStr = DubStr & WordArr(j) & " | "
End If
Next j
'Paste string in column B
WS.Cells(i, 2).Value = Trim(DubStr)
DubStr = ""
Erase WordArr
Next i
End Sub
No comments:
Post a Comment