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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,679
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

have only tried this on a very small sample data

VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,552
Messages
5,596,801
Members
414,103
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top