Finding Best Match [search for a match in a part of a cell]

[BM604]

Hi All,

Lets imagine I have two Columns A,B.

A
Bob
Elephant
Brokenwood
Laser

B
Az. Elephant Co
Bob's Market Ltd.
Brokenwood Ultimate Patio
Laserjet Enterprises

Column A has a partial match of a complete cell in column B. How can I use Column A and lookup Column B to pull the best most accurate match.

I.e. Bob would select Bob's Market because it is the best match.

Any help is greatly appreciated. Have a great weekend. Thank you.

<tbody>
</tbody>

 

[JoeMo]

Not sure about "best" match, but this will find the first partial match in the complete list.

Excel Workbook
	A	B	C
1	Partials	Complete	Best Match
2	Bob	Az. Elephant Co	Bob's Market Ltd.
3	Elephant	Bob's Market Ltd.	Az. Elephant Co
4	Brokenwood	Brokenwood Ultimate Patio	Brokenwood Ultimate Patio
5	Laser	Laserjet Enterprises	Laserjet Enterprises
Sheet5

 

[hiker95]

BM604,

You did not say how to display a match?

Here is a macro solution for you to consider that will adjust to the number of raw data rows.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	Word	Company
2	Bob	Az. Elephant Co
3	Elephant	Bob's Market Ltd.
4	Brokenwood	Brokenwood Ultimate Patio
5	Laser	Laserjet Enterprises
6
Sheet1

After the macro:

Excel 2007
	A	B	C
1	Word	Company	best match for Word
2	Bob	Az. Elephant Co	Bob's Market Ltd.
3	Elephant	Bob's Market Ltd.	Az. Elephant Co
4	Brokenwood	Brokenwood Ultimate Patio	Brokenwood Ultimate Patio
5	Laser	Laserjet Enterprises	Laserjet Enterprises
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindBestMatch()
' hiker95, 03/13/2015, ME842256
Dim c As Range, b As Range
With ActiveSheet
  .Columns(3).ClearContents
  With .Cells(1, 3)
    .Value = "best match for Word"
    .HorizontalAlignment = xlCenter
  End With
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set b = .Columns(2).Find("*" & c & "*", LookAt:=xlWhole)
    If Not b Is Nothing Then
      c.Offset(, 2).Value = .Cells(b.Row, 2).Value
    End If
  Next c
  .Columns(3).AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindBestMatch macro.