VBA alternative - Partial Text Matching

[Jqarga]

Hi guys,

Within a workbook I've got a tab(Tab_A) with a list of "Book Tittles" sent it by an external source (20K rows approx). In another tab(Tab_B) I've got my reference list also with "Book Tittles" which I do own.

In theory a relevant % of tittles included in the list provided by the external source should match with the ones I keep in my own list.

The issue here is that not all the tittles are wrote in the same way. Depending of the data quality provided by the external source, the tittle could exclude or include articles, additional words etc...so is not that easy as to run a simple vlookup or index_match function. I need some kind of model/formula that look into the first list and returns next to my list in tab_B, the potential match. Could be a model that returns the tittle from the source list when a few words in common are identified...or something similar.

At the moment I'm using the following formula.

=IFERROR(INDEX('Tab_A'!$A$2:$A$20000,MATCH('Tab_B'!$A2&"*",'Tab_A'!$A$2:$A$20000,0),1),"No Match found")

Is there any VBA alternative that could potentially give me a better/more accurate result?

Many thanks,

J






[TABLE="width: 265"]
<tbody>[TR]
[TD="class: xl64, width: 265"] [TABLE="width: 265"]
<tbody>[TR]
[TD="class: xl64, width: 265"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike Blackman]

Hi,

You can use simple pattern matching in VBA using the Like operator as below.

    If "MyString" Like "My*r?ng" Then
        MsgBox ""
    End If

* - 0 to many wildcard values
? - 1 wildcard value

 

[Jqarga]

Hi Mike,

Thanks a lot for your quick response!

I'm not a 100% familiar VBA. Do I have to key in any particular formula (sort of user defined formula) after building this macro in a module or how does it work exactly?

Many thanks,