PDA

View Full Version : Need to compare two string columns based on similarities and return a value



Rlazer
09-19-2018, 05:18 PM
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
09-22-2018, 06:44 AM
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