compare text between 2 cells
- Thread starter bigme
- Start date
Hello BigMe,
Glad to hear from you again. Thank you for the thorough explanation of the issue.
To answer your question, the below VBA function will compare texts between 2 cells ignoring such cases. Here is the VBA code:
Code:
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- Copy the code to a module and enter the below function in C2:
=FindMatch(A2,B2)
As a result, we obtain the match or not match outcomes.
I am attaching the desired workbook. Please, follow accordingly and let us know your feedback.
See the below article to compare between sheets:
Regards,
Yousuf Shovon
Good Morning,
thank you dear Yousuf for your help, but i forgot to inform that i want to copy this function in a file and use it to process data in other file? 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, thank you.
regards,
bigMe
thank you dear Yousuf for your help, but i forgot to inform that i want to copy this function in a file and use it to process data in other file? 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, thank you.
regards,
bigMe
Hello BigMe,
Glad to hear from you again. I understand you want to call the previous VBA function from another file. Also, you want to process data of columns A & F.
Fortunately, the range of the VBA function we have created is dynamic which means you can input any range manually and get the match or not match output accordingly.
Based on your requirements, I have created 2 workbooks. The Bigme1(Source) file has the source data and BIGME2(Solved) has the formula output with the VBA code. Here, we will use the function from the second file to process data from the first file. Follow the steps carefully:
- Open the file with the source data.
- Now, copy the VBA code and save it in the BIGME2(Solved) workbook's module.
- Now, in the same workbook, enter the below VBA function in B2 >> switch to source file >> select the range you want to check with a comma between them.
=FindMatch(
- As a result, you get the match or not match output. Remember to do the same for each row. You can not use the AutoFill handler for this VBA function.
I am attaching the mentioned workbooks here. Let us know if you have further queries.
Regards,
Yousuf Shovon
Dear Yousuf,
big thank you for your attention, after i try to understand the method, 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? so i run the function from BIGME2 to process data in Bigme1 and the result also in Bigme1 in column J, I'm sorry if I ask too many questions and it's troublesome, thank you.
regards,
bigMe
big thank you for your attention, after i try to understand the method, 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? so i run the function from BIGME2 to process data in Bigme1 and the result also in Bigme1 in column J, I'm sorry if I ask too many questions and it's troublesome, thank you.
regards,
bigMe
Hello BigMe,
Certainly! You can find the Match or Not Match and get output in Source sheet of Bigme1(Source) workbook and show output using a VBA code from BIGME2(Code) workbook.
In BIGME2(Code) workbook, save the below VBA code and run it.
Code:
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
Note: Remember to change the name of the workbook path and worksheet name in the following code line:
Code:
Set SourceWorkbook = Workbooks.Open("C:\Users\YOUSUF\Desktop\Forum\BigME\Bigme1(Source).xlsm")
Set SourceWorksheet = SourceWorkbook.Sheets("Source")I have attached the workbooks here. I hope this works for you. Feel free to ask any questions regarding this. Not troublesome at all!
Regards,
Yousuf Shovon