Hello BigMe,is there any way to compare text between 2 cells by ignoring upper/lower case, punctuation, not in the same order, min. 2 words same is okay.
Function FindMatch(cell1 As Range, cell2 As Range) As String
Dim text1 As String
Dim text2 As String
text1 = Replace(LCase(cell1.Value), ",", "")
text2 = Replace(LCase(cell2.Value), ",", "")
Dim words1() As String
Dim words2() As String
words1 = Split(text1, " ")
words2 = Split(text2, " ")
Dim matchCount As Integer
matchCount = 0
For Each word1 In words1
For Each word2 In words2
If word1 = word2 Then
matchCount = matchCount + 1
Exit For
End If
Next word2
Next word1
' Check if at least two words matched and return the result
If matchCount >= 2 Then
FindMatch = "Match"
Else
FindMatch = "Not Match"
End If
End Function
Hello BigMe,how to call the function? and please inform me if i try to put data in cell1 and cell2 in column A and column F, not side by side like the sample i gave column A & B
Hello BigMe,i want to make sure that the function and the result must be in one workbook, am i correct? is there any option so that the function saved in BIGME2 but the data source and result in Bigme1?
Sub FindAndDisplayMatches()
Dim SourceWorkbook As Workbook
Dim SourceWorksheet As Worksheet
Dim LastRow As Long
Dim i As Long
Set SourceWorkbook = Workbooks.Open("C:\Users\YOUSUF\Desktop\Forum\BigME\Bigme1(Source).xlsm")
Set SourceWorksheet = SourceWorkbook.Sheets("Source")
LastRow = SourceWorksheet.Cells(SourceWorksheet.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
Dim text1 As String
Dim text2 As String
text1 = Replace(LCase(SourceWorksheet.Cells(i, 1).Value), ",", "")
text2 = Replace(LCase(SourceWorksheet.Cells(i, 6).Value), ",", "")
If Not IsEmpty(SourceWorksheet.Cells(i, 1).Value) And Not IsEmpty(SourceWorksheet.Cells(i, 6).Value) Then
Dim words1() As String
Dim words2() As String
words1 = Split(text1, " ")
words2 = Split(text2, " ")
Dim matchCount As Integer
matchCount = 0
Dim word1 As Variant
Dim word2 As Variant
For Each word1 In words1
For Each word2 In words2
If word1 = word2 Then
matchCount = matchCount + 1
Exit For
End If
Next word2
Next word1
SourceWorksheet.Cells(i, 10).Value = IIf(matchCount >= 2, "Match", "Not Match")
End If
Next i
SourceWorkbook.Close SaveChanges:=True
End Sub
Set SourceWorkbook = Workbooks.Open("C:\Users\YOUSUF\Desktop\Forum\BigME\Bigme1(Source).xlsm")
Set SourceWorksheet = SourceWorkbook.Sheets("Source")
Dear BigMe,wow.... and wow.....
a lots of thank you dear Yousuf....
i am speechless... thank you once more time for your help.
regards,
bigMe