Need to compare two string columns based on similarities and return a value

[Rlazer]

Hi I have two worksheets.
I want to compare cell B2 in worksheet 'applications' - with cells $B$2:$B$16 in worksheet 'grants' such that if a cell in worksheet 'grants' has more than three similar words to cell B2 then the agreement ID correlating to that cell in worksheet 'grants' is reflected in worksheet 'applications' next to cell B2 under the heading 'Awarded'.

please help!

[nikki333]

I'm not an expert, but how about using the INSTR function: Instr([start], string, substring, [compare])

Assuming that the strings in 'grants' are comma-separated,...maybe something in that direction:

Dim i as long, j as long
Dim numCounter as long: numCounter = 0
Dim rng as range: set rng = Worksheets("grants").range("B2:B16")
Dim arr as variant: arr = rng
Dim arrSplit as variant
Dim str as string: str = Worksheets("applciations").range("B2").text

For i = Lbound(arr,1) to Ubound(arr, 1)

   Redim arrSplit = Split(arr(i,1), " , ")

   For j = Lbound(arrSplit,1) to Ubound(arrSplit, 1)

      If Instr(1, arrSplit(j,1), str, vbTextCompare) > 0 Then

         numCounter = numCounter + 1

      End if 

   next j

   If numCounter = 3 then

      Worksheets("applications").Range("C2") = rng.cells(i,2)

      numCounter = 0

   End if 

   Exit for 

Next i