Mark cells in column A that contain text from any cell in column B

Question

I have a base set of data in column A and a larger result set in column B that has trash values I don’t need.

ColA	ColB
AAA	NBVAAAYHK
BBB	GHYCCCJOO
CCC	VFBBBHGT
	VFGHUYRCJ

Similar to the example above with about 300 cells in A and 12k in B. In the example, I want B1-3 marked either by a highlight or a value in C. As long as I can sort and separate the trash.

I’ve tried to use VLOOKUP and MATCH but I can’t get them to work consistently or how I expect them to. And I’m sure an answer to this is out there but I haven’t been able to craft a query to find it. I appreciate your help! Happy to elaborate further if needed.

Answer 1

Please, try the next version, too. It will write in C:C the matching string. Comma separated if many such matches exist:

Sub MarkMatches()
 Dim ws As Worksheet, lastRA As Long, lastRB As Long, arrA, arr, i As Long, j As Long
 
 Set ws = ActiveSheet
 lastRA = ws.Range("A" & ws.rows.count).End(xlUp).row
 lastRB = ws.Range("B" & ws.rows.count).End(xlUp).row
 
 ws.Range("C2:C" & lastRB).ClearContents 'clear C:C column if code has previously been run
 arrA = ws.Range("A2:A" & lastRA).Value2
 arr = ws.Range("B2:C" & lastRB).Value2
 
 For i = 1 To UBound(arrA)
    For j = 1 To UBound(arr)
        If InStr(arr(j, 1), arrA(i, 1)) > 0 Then
            If arr(j, 2) = "" Then arr(j, 2) = arrA(i, 1) Else: arr(j, 2) = arr(j, 2) & ", " & arrA(i, 1)
        End If
    Next j
 Next i
 'drop the processed array content, at once:
 ws.Range("B2").Resize(UBound(arr), 2).Value2 = arr
End Sub