CODEWITHSUNDEEP
vbaexcelformula
Nik Wong
Nik Wong

Reputation: 1

Partial string match then return value

I'm working on a way to quickly code bank transactions. I have one tab of bank data downloaded (sheet 1) and I want to search the descriptions (column B) for a partial match with sheet 2, column A. Then if match found, return the value from sheet 2, column B to sheet 1 column D; and sheet 2, column C to sheet 1, column E.

Sheet 1

Column A    Column B                           Column C  Column D Column E       
11/1/17     Transfer from Account 60617829-D   276       {acct}   {location}
11/1/17     Transfer from Account 60692022-D   551.46    {acct}   {location}

Sheet 2

Column A     Column B (acct)   Column C (location)
60617829-D   10430             03
60692022-D   10490             09

I was trying to use a solution similar to "Find and Get" described here: Excel Formula/VBA to search partial strings in other sheet

However, the following code returns the first value from sheet 2 to all values on sheet 1 without properly matching them. I think my error is in how I'm trying to use an array when it may not be necessary but I am at a loss.

Sub findAndGet()

Dim sh1, sh2 As Worksheet
Dim tempRow1, tempRow2 As Integer
Dim strList() As String
Dim name As String
Dim index As Integer

'Set sheets
Set sh1 = Sheets("list")
Set sh2 = Sheets("search")

'Set the start row of Sheet1
tempRow1 = 1

'Loop all row from starRow until blank of column A in Sheet1
Do While sh1.Range("A" & tempRow1) <> ""

    'Get name
    name = sh1.Range("B" & tempRow1)

    'Split by space
    strList = Split(Trim(name), " ")

    'Set the start row of Sheet2
    tempRow2 = 1

    'Reset flag
    isFound = False

    'Loop all row from startRow until blank of column A in Sheet2
    Do While sh2.Range("A" & tempRow2) <> ""

        For index = LBound(strList) To UBound(strList)

            'If part of name is found.
            If InStr(UCase(sh2.Range("A" & tempRow2)), UCase(strList(index))) > 0 Then

                'Set true to search flag
                isFound = True

                'exit do loop
                Exit Do

            End If

        Next index

        'Increase row
        tempRow2 = tempRow2 + 1

    Loop

    'If record is found, set output
    If isFound Then

        'set account
        sh1.Range("D" & tempRow1) = sh2.Range("B" & tempRow2)

        'set location
        sh1.Range("E" & tempRow1) = sh2.Range("C" & tempRow2)

    End If

    'Increase row
    tempRow1 = tempRow1 + 1

  Loop
End Sub

Upvotes: 0

Views: 1216

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

If formula solution is acceptable then assuming that data begins on both sheets on row number 2.

In cell D2 of Sheet1 insert following formula and copy down. =LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$3,Sheet1!B2,1),Sheet2!$B$2:$B$3)

In cell E2 of Sheet1 insert following formula and copy down. =LOOKUP(2^15,SEARCH(Sheet2!$A$2:$A$3,Sheet1!B2,1),Sheet2!$C$2:$C$3)

Upvotes: 1

Related Questions