If statement to match 2 similar text strings

[Bigzippy]

Hi

Using VBA and if statement, having trouble determining if 2 similar strings match. So here is an example of two strings to match:

1) "ISH MSCI INDONESIA INVESTABLE MKT"
2) "ISHARES MSCI INDONESIA INVESTABLE MKT INDEX FUND MorganStanley SmithBarney LLC acted as your agent"
Second text could have random spaces.

Thank You

 

[BrandonWLH]

What do you want to happen?

Do those 2 strings match in your schema or do they not match?

 

[mikerickson]

If the longer string is in A1, perhaps something like this use of wildcards

=IF(ISNUMBER(MATCH("*ISH MSCI INDONESIA INVESTABLE MKT*", A1, 0)), "match", "not")

 

[Bigzippy]

I am looking for the text that matches #2 text to something similar to #1. the text is in different sheets. Finding the data and matching is no problem I just need to determine where #2 is in the table containing #1. so i have a if statement within a do loop that will do it if can match the two strings. Hope that is clear
Thanks

The text comes from other sources one is from a dde link the other is imported from another program so i do not have any control about how it is formatted or presented

 

[BrandonWLH]

I think I gotcha now. Noncontroled data input and you got to figure out which one did they mean and there is no control of how they enter data.

i.e.
ISH MSCI INDONESIA INVESTABLE MKT"
ISH MSCI INDONESIA INVES MKT"
ISH MSCI INDO INVESTABLE MARKET"
ISH INDONESIA INVESTABLE MARK"

They are all the same and you need to recognize it that way. That is a bit complicated and I don't think a base function on the sheet could do that. You would probably need to extract the words out of the input and check for partial matches/substr and then count the number of matches you get against all the others and the one with the most matching words is the winner.

In the future, you might want to get them to use a drop down list so its always the same when they enter data into a cell.

 

[al_b_cnu]

Hi, Maybe FuzzyVlookup, e.g.
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Lookup Value</td><td style="font-weight: bold;;">Best Match Row</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Lookup</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ISH MSCI INDONESIA INVESTABLE MKT</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alpha Beta Gamma</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ISHARES MSCI INDONESIA INVESTABLE MKT INDEX FUND MorganStanley SmithBarney LLC acted as your agent</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Delta Epsilon Zeta</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=fuzzyvlookup(<font color="Blue">A2,E:E,0,,,2</font>)</td></tr></tbody></table></td></tr></table><br />

Link to the code is in my signature

 

[Bigzippy]

WOW Thank You very much!

works great tells me it is in row 15 which is correct.

what does the "0,,,2)" portion of the function do? and how ca i get the address or workshet row # i assume it gives me row # of range i entered but the column will have repeats i need to fin addy of next, which i can do by programatically starting search in different spots any ideas?

Again Thank You

 

[al_b_cnu]

Hi,
The '0' says 'return the relative row number', set it to a positive number and it will return the contents of the corresponding column (as vlookup does).
The '2' is the algorithm to be used - see comments in code for explanation.
To get the 2nd best match, set the 'Rank' parameter to 2, for the 3rd best match set it to 3 etc.

 

[Bigzippy]

I cant figure out how to use this in my vba code to do what i am trying or on the sheet to get the symbol for example

 

[al_b_cnu]

Hi,

With this example:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Lookup Value</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Lookup</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ISH MSCI INDONESIA INVESTABLE MKT</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alpha Beta Gamma</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ISHARES MSCI INDONESIA INVESTABLE MKT INDEX FUND MorganStanley SmithBarney LLC acted as your agent</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Delta Epsilon Zeta</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ISHARES MSCI INDONESIA INVESTABLE MKT INDEX FUND MorganStanley SmithBarney LLC acted as your agent</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">zxcvbn asdfghj</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ISHARES MSCI INDONESIA INVESTABLE MKT INDEX FUND MorganStanley SmithBarney LLC acted as your agent</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

copy FuzzyVLookup code to a module
Copy the following to as seperate module:

Option Explicit

Sub Test()
Dim sLookupValue As String
Dim rTableArray As Range
Dim iRank As Integer
Dim vResult As Variant

With Sheets("Sheet1")
    '-- Get Lookup Value from A2 --
    sLookupValue = CStr(.Range("A2").Value)
    
    Set rTableArray = Intersect(.UsedRange, .Columns("E:E"))
    
    For iRank = 1 To 10
        vResult = FuzzyVLookup(LookupValue:=sLookupValue, _
                               TableArray:=rTableArray, _
                               IndexNum:=0, _
                               NFPercent:=0.5, _
                               Rank:=iRank, _
                               Algorithm:=2, _
                               AdditionalCols:=0, _
                               LookupColOffset:=0, _
                               GroupColOffset:=0, _
                               GroupValue:="")
        MsgBox CStr(vResult)
        If IsNumeric(vResult) = False Then Exit For
    Next iRank
End With

End Sub

All rows with a 50% or greater match will be displayed.