CODEWITHSUNDEEP
vbaexcel
Vinoth Narayan
Vinoth Narayan

Reputation: 275

Matching similar in Excel VBA

I have been trying to find the similar matches to fill the blank Sapcode field in the first screenshot.

The Card Type field in the second screenshot below contains related words to the Card Type column in the first screenshot. E.g. card type and city. The data in the second screenshot also contains the Sapcode for those words.

I would like to find the Sapcode for Card Type values in the first screenshot by matching on the words in the other Card Type column in the second screenshot. Sheet with missing Sapcodes:

Input file i want to fill the sapcode

Sheet with matching rules:

Rule Book Sheet

For example - for the input text visa/20160927/ET-Chennai/FT I can match SAP008 by matching visa and Chennai.

Here is my code so far:

For i = 9 To input_sht.Cells(input_sht.Rows.Count, 1).End(xlUp).Row

input_txt = input_sht.Range("B" & i).Value

For j = 2 To rule_sht.Cells(rule_sht.Rows.Count, 1).End(xlUp).Row

    rulebook_sht = rule_sht.Range("B" & j).Value

    If rulebook_sht <> "" Then

        If InStr(input_txt, rulebook_sht) > 0 Then
         input_sht.Range("C" & i).Value = rule_sht.Range("C" & j).Value
        End If

    End If

Next

Next

The above code works fine if have a field of just visa meaning it will match with visa and give the first searched option but it is not checking the location. How can I update my code to match on all the words?

Upvotes: 1

Views: 1721

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

You can use the Like operator with wildcards to compare two strings. In this case you need to use the * wildcard which matches zero or more other characters. For example, in the VBE immediate window type this and get True:

? "visa/20160927/Chennai/FT" Like "*visa*Chennai*"

So we can use this to iterate over all the inputs (Card Type in first screenshot) and compare to all the 'rules' (Card Type in second screenshot).

Where there is a match given by Like operator you can get the Sapcode and update the blank column. To create a 'wildcarded' version of your rule you can use this code:

strWildcardRule = "*" & Join(Split(strRule, " "), "*") & "*"

Which splits the elements of the 'rule' into an array and rejoins, and surrounds, them with * for the wildcards. So:

visa Chennai

becomes:

*visa*Chennai*

And you can then use the Like operator.

The code will work for your presented test data. I have commented it so you can follow along:

Option Explicit
Option Compare Text

Sub LookupSapcode()

    ' all the required variables
    Dim ws As Worksheet
    Dim rngInput As Range
    Dim rngRules As Range
    Dim rngOutput As Range
    Dim lngInputRow As Long
    Dim lngRuleRow As Long
    Dim strRule As String
    Dim strInput As String
    Dim strOutput As String
    Dim strWildcardRule As String

    ' set references to sheet and ranges to work with
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rngInput = ws.Range("A2:A10")
    Set rngOutput = ws.Range("B2:B10")
    Set rngRules = ws.Range("D2:E10")

    ' iterate rows
    For lngInputRow = 1 To rngInput.Rows.Count
        ' get card type
        strInput = rngInput.Cells(lngInputRow, 1).Value
        ' iterate rules
        For lngRuleRow = 1 To rngRules.Rows.Count
            ' get the rule as a string
            strRule = rngRules.Cells(lngRuleRow, 1).Value
            ' assume a match on this row of rules and get sap code
            strOutput = rngRules.Cells(lngRuleRow, 2).Value
            ' get the rule as a wildcard string
            strWildcardRule = "*" & Join(Split(strRule, " "), "*") & "*"
            ' if the wildcarded rule is like the input string
            If strInput Like strWildcardRule Then
                ' break the loop as we have a match
                ' strOutput will be the sap code for the matching rule
                Exit For
            Else
                ' no match - set this output to empty string
                strOutput = vbNullString
            End If
        Next lngRuleRow

        ' if output string is not empty then update the row for this card type
        If strOutput <> vbNullString Then
            ' set the sapcode in the output range
            rngOutput.Cells(lngInputRow, 1).Value = strOutput
        End If

    ' next card type in input range
    Next lngInputRow

End Sub

Note I use Option Compare Text in case there is a match but with different upper and lower case on either side of the Like. Check the link for Like:

Option Compare Text results in string comparisons based on a case-insensitive, textual sort order determined by your system's locale. When you sort the same characters using Option Compare Text, the following text sort order is produced: A=a) < (À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Upvotes: 2

Related Questions