Key Word Macro or formula

[Welsh Mark3]

Is there a Macro or formula that will highlight or allow me to identify if a keyword exists in column A and B? I am looking to produce something in Column C to quickly match company names without doing it manually. Any solution will be gratefully received

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Desired result or Similar[/TD]
[/TR]
[TR]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]WINFIELD APARMENT PARTNERS LLC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]CANTERBURY VILLAGE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]A AFFORDABLE CARPENTRY INC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]A-AFFORDABLE CARPETING, INC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A AFFORDABLE CARPENTRY INC[/TD]
[/TR]
[TR]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]TOWNVIEW APTS FC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]TOWNVIEW APARTMENTS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TOWNVIEW AP[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="width: 461"] THE BLUE HERON GOLF COURSE[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD="width: 461"]THE BLUE HERON BAR & GRILL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 461"]
<tbody>[TR]
[TD]THE BLUE HERON[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Edwards, Anthony, Mark[/TD]
[TD]Mark Edwards[/TD]
[TD]Edwards Mark[/TD]
[/TR]
</tbody>[/TABLE]

 

[tonyyy]

Welsh Mark3,

The following might get you started...

Sub keyWord_1063727()
Dim r As Long, c As Long, i As Long, j As Long
Dim arr As Variant, arrA As Variant, arrB As Variant
Dim keyWord As String

arr = ActiveSheet.UsedRange.Offset(1, 0)
For r = 1 To UBound(arr)
    arrA = Split(arr(r, 1), " ")
    arrB = Split(arr(r, 2), " ")
    keyWord = ""
    For i = LBound(arrA) To UBound(arrA)
        For j = LBound(arrB) To UBound(arrB)
            If InStr(arr(r, 1), arrB(j)) > 0 And InStr(keyWord, arrB(j)) = 0 Then
                If keyWord <> "" Then
                    keyWord = keyWord & " " & arrB(j)
                Else
                    keyWord = arrB(j)
                End If
            End If
            If InStr(arr(r, 2), arrA(i)) > 0 And InStr(keyWord, arrA(i)) = 0 Then
                If keyWord <> "" Then
                    keyWord = keyWord & " " & arrA(i)
                Else
                    keyWord = arrA(i)
                End If
            End If
        Next j
    Next i
    arr(r, 3) = keyWord
Next r
ActiveSheet.UsedRange.Offset(1, 0).Value = arr
MsgBox "The dishes are done, dude!"
End Sub

The code matches whole words, not partial words.

Cheers,

tonyyy

 

[Peter_SSs]

Is there a Macro or formula that will highlight or allow me to identify if a keyword exists in column A and B?

It is a massive thread to look through (and may be best looking from the end backwards?) but this may be of use to you.