can't compare two columns with similar text

[snypster]

Hi Hoping someone can help.

I'm trying to compare two columns with similar text in each cell i.e. address.

I'm using the VLOOKUP function with TRUE statement but for whatever reason it doesn't seem to be working in all scenerios.

For example
A1 = PO Box 000 MURRAY BRIDGE SA 9999
B1 = Sunnys Prods P/L (0022) PO Box 000 MURRAY BRIDGE SA 9999

as you can see, A1 is contained in B1 but it's not returning a value. In some other instances it is.

Is there an IF statement or similar that I can use to find near matchs? I'm trying to apply this across 20,000 rows.

Any help would be much appreciated so I dont have to look at the records 1 by 1 to find where A1<>B1

Thanks in advance.

 

[Jonmo1]

Welcome to the board.

Try

=VLOOKUP("*"&A1,B:C,FALSE)

Hope that helps.

 

[Greg Truby]

The term you need to search for in lieu of "near match" is "fuzzy match". Try searching for that and you'll find quite a bit. Here's an example of one thread on the subject.

http://www.mrexcel.com/forum/showthread.php?t=195635

<SUP>edit </SUP>I'll be darned, I'd never tried wildcarding VLOOKUP(). I'd definitely give that a try first. . <SUB>/edit</SUB>

 

[snypster]

Thank you for both your prompt responses but wild card didnt work and not sure how to create the "fuzzy" look up i.e. how to insert the script that's developed

<TABLE style="WIDTH: 654pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=871 border=0><COLGROUP><COL style="WIDTH: 326pt; mso-width-source: userset; mso-width-alt: 15872" width=434><COL style="WIDTH: 328pt; mso-width-source: userset; mso-width-alt: 15981" width=437><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 326pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=434 height=20>PO Box 000 MURRAY BRIDGE SA 9999</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 328pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=437>Sunnys Prods P/L (0022) PO Box 000 MURRAY BRIDGE SA 9999</TD></TR></TBODY></TABLE>

Using =Vlookup("*"&A1,B1,False)

 

[Jonmo1]

What do you want to DO if the value matches, and/or if it doesn't?

Are you wanting to look in the entire column for the match?

 

[Greg Truby]

There was a slight syntax error in JonMo's formula. Try this:

• =VLOOKUP("*"&A1,B1,1,FALSE)

 

[snypster]

What do you want to DO if the value matches, and/or if it doesn't?

Are you wanting to look in the entire column for the match?

I'm looking for where there is no matches, and have tried using TRUE as well in the lookup, is it excel that's not recognizing the text?

I'm only looking in the two cells for where address is, just that there is multiple address records.

I have also tried the syntax =vlookup("*"&a1,b1,1,false) with no success.

Thanks for everyone's help so far, Hopefully I can find an answer soon.

 

[Jonmo1]

So you only need to know if the text in A1 exists within the text in B1 ?

Try

=ISNUMBER(SEARCH(A1,B1))

This will give you TRUE if A1 exists within B1, otherwise False

If you need it to be case sensitive, change search to find.


Hope that helps.

 

[snypster]

Thanks Jonmo1,

Seems to have done the trick. Thanks everyone for your tips!

 

[Sektor]

UDF.
sSource is what you search.
sTarget is where you search.

[COLOR="Blue"]Function[/COLOR] NearMatch(sSource [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], sTarget [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Boolean[/COLOR]
    Application.Volatile
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = sSource
        NearMatch = .Execute(sTarget).Count > 0
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]