Finding if a word is part of any cell or cells?

[tonywatsonhelp]

Hi everyone,

Please help if you can,

I have a list of businesses in coulnm H

and a list of Names in Column AA

now the Names in column AA area unique but can be more than one word like "Boots Ltd"
The words in Column H are full company names so are more complex like "Southend High Street Boots"

Now what would be ideal is if i could get a macro to check each line in H for a match in column AA and give the column it matched with and the type of match
For example
in H
""Southend High Street Boots Ltd" is an exact match to "Boots Ltd" so in column I i get the row number so say 17 and in Column J i get Matched
but if it said
""Southend High Street Boots" is not an exact match to "Boots Ltd" so in column I i get the row number so say 17 and in Column J i get Part Matched

i dont mind if this is a acro or formula, it is a one off run so can be slow, just need the help thanks

Tony

 

[onlyadrafter]

Hello,

have only tried this on a very small sample data

Sub FIND_MATCH()
    Columns("AA:AA").Copy Range("AB1")
    Application.CutCopyMode = False
    Columns("AB").TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    For MY_SOURCE_ROWS = 1 To Range("AA" & Rows.Count).End(xlUp).Row
        For MY_SOURCE_COLUMNS = 27 To Cells(MY_SOURCE_ROWS, Columns.Count).End(xlToLeft).Column
            MY_MATCH = Cells(MY_SOURCE_ROWS, MY_SOURCE_COLUMNS).Value
                For MY_DEST_ROWS = 1 To Range("H" & Rows.Count).End(xlUp).Row
                    If Range("J" & MY_DEST_ROWS).Value <> "Matched" Then
                        If Len(Range("H" & MY_DEST_ROWS).Value) <> Len(Replace(Range("H" & MY_DEST_ROWS).Value, MY_MATCH, "")) Then
                            If MY_SOURCE_COLUMNS = 27 Then
                                Range("I" & MY_DEST_ROWS).Value = MY_SOURCE_ROWS
                                Range("J" & MY_DEST_ROWS).Value = "Matched"
                            Else
                                Range("I" & MY_DEST_ROWS).Value = MY_SOURCE_ROWS
                                Range("J" & MY_DEST_ROWS).Value = "Part matched"
                            End If
                        End If
                    End If
                Next MY_DEST_ROWS
            Next MY_SOURCE_COLUMNS
    Next MY_SOURCE_ROWS
    Columns("AB:AC").ClearContentsEnd Sub

is it close at all?