Matching word phrases over different cells [Solved/Closed]

Report
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
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 :)

6 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
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
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...
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
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.
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!
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
755
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