Excel Formula/VBA to search partial strings in oth

问题:

I am having names in two cells of sheet1 (e.g. : B1 (Gina Williams) & B2 (Patrick Rafter)) and the corresponding bank statement narratives are in sheet 2 (column C) e.g: "Deposit from Gina towards rent for connaught place apt".

Now I need to search all the four partial texts available in cells B1 & B2 of sheet 1 (ie. "Gina", "Williams", "Patrick", "Rafter" in the entire column B of sheet 2. if there is a match i need to capture the corresponding column B & D value for the matching row.

SHEET1

Column A      Column B            Column C                         Column D
   1        GINA WILLIAMS     OUTPUT (matching col b of sheet2)  OUTPUT (matching col D of sheet2)
   2        PATRICK RAFTER    OUTPUT (matching col b of sheet2)  OUTPUT (matching col D of sheet2) 

SHEET2

Column A   Column B     Column C                                              Column D
    1     12/7/2015   Deposit from Gina towards rent for connaught place apt        320 

    2     13/7/2015   Deposit from Rafter towards rent for connaught place apt          720

I have tried with vlookup, find, match (along with left, right, mid functions) functions.

回答1:

You could use VBA to achieve this, but if you've not done VBA before, this might not be a good idea.

I would favour adding another column to sheet 2 when you manually enter the name from sheet 1 into each cell. In every cell of this new column, you can give the user a drop down list of all names that can be entered by using the excel ribbon>Data>Data Tools>DataValidation option.

This solution will work - so long as your bank statement is not enormous! If it is then you might want to do it differently. It also gets around the issue of two people on sheet1 having the same forename or surname, and is probably something you will be able to do quite quickly.

Once the above is done, you can simply use VLOOKUP in sheet 1 to fin the data on sheet 2.

KISS.

Harvey

回答2:

I got one for you. I already tested the code. It work perfectly for me.

But, not grantee for duplicate naming , means, it can't give right result for duplicate names and duplicate deposit.

Here the code:

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("C" & 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 date
            sh1.Range("C" & tempRow1) = sh2.Range("B" & tempRow2)

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

        End If

        'Increase row
        tempRow1 = tempRow1 + 1

    Loop

End Sub