Matching word phrases over different cells

Solved/Closed
localeman - May 27, 2011 at 08:54 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 27, 2011 at 10:21 AM
Hello,

I receive batches of text and need to weed through the duplicates. I have figured out how to find exact matches like "Good Food"repeated the same over many cells... but how do you get excel to also find "Food Good"?

"good food" and "food good" are in separate cells in the same column, if that helps. Please help :)

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2011 at 09:00 AM
I think for that you have to code to do a fuzzy search. A lot depends on how the search should go. As you said "Good Food" and "Food Good" is a match. Then is "Food is Good" a match too. or "Good Mood" is a match ? depends
0
I understand how both of those examples could get tagged. I am looking for Excel to scan through and find the exact phrases and be able to swap the exact phrase around to find matches. That being the case "Food is Good" and Good Mood" would not be tagged if looking for "good food" and the swap "food good". Hope this narrows it down a little...
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2011 at 09:30 AM
In worse case, how many words can a phrase have?. If two, then life would be so easier and you can swap the words aroudn to do the test. One way could be that you take your search phrase and split into words. Then for each word, do a find and replace it with null. Keep a count if replacement occured. If by end of last word, you have a null string and all words were used in replacement, then you know that both contain same words.
0
Most of the time its 2 but at time 3... I understand what you mean. I will give this a run and see. I am starting to think i need to find another program or get something coded to get exactly what i want. Thank you for your help!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2011 at 10:21 AM
You can try some thing like this if it helps
Sample Call : =isCommon("Food Good", "Good Food")

Public Function isCommon(ByVal sFindThis As String, ByVal sFindIn As String) As Boolean

   Dim sPreReplace         As String
   Dim arrSplitFind        As Variant
   Dim iWord               As Variant
   Dim sWord               As String
   Dim iRepCount           As Integer
   
   If (sFindThis = sFindIn) _
   Then
      isCommon = True
      GoTo isCommon_Exit
   End If
   sFindThis = Trim(sFindThis)
   Do
      sPreReplace = sFindIn
      sFindIn = Replace(sFindIn, "  ", " ")
   Loop While (sPreReplace <> sFindIn)
   
   Do
      sPreReplace = sFindThis
      sFindThis = Replace(sFindThis, "  ", " ")
   Loop While (sPreReplace <> sFindThis)
   
   arrSplitFind = Split(sFindThis, " ")
   For iWord = LBound(arrSplitFind) To UBound(arrSplitFind)
      sWord = Trim(arrSplitFind(iWord))
      If (sWord <> vbNullString) _
      Then
         sFindIn = " " & Trim(sFindIn) & " "
         sPreReplace = sFindIn
         sFindIn = Replace(sFindIn & " ", " " & sWord & " ", vbNullString, 1, 1)
         sFindIn = " " & Trim(sFindIn) & " "
         If (sPreReplace = sFindIn) _
         Then
            isCommon = False
            GoTo isCommon_Exit
         End If
      End If
Next_iWord:
   Next iWord
   isCommon = (Trim(sFindIn) = vbNullString)

isCommon_Exit:
End Function
0