VBA search

[Ken123]

Hi Guys, I have a Excel table consist of Customer Name and Account.Example: John Doe (in A1) and 123 (in A2). Then based on bank reference ( 0000100 FT transfer 00100 John 000100 ) i woud like to have a VBA code that will seach John from then bank reference and match it to the table, Once matched, return the Customer full name and account number. hope i make myself clear. and look forward to any discussion and solution. cheers

[mancubus]

welcome to the forum.

upload a sample file to work with.
provide the desired output in a blank sheet.

[Ken123]

welcome to the forum.

upload a sample file to work with.
provide the desired output in a blank sheet.

[Ken123]

hi mancubus, thank you for your reply, have attached as book 1.

[mancubus]

Regarding the input-output, you only need VLOOKUP.


you have a named range called CUST

lookup values are in Column A (starting at A5)
lookup table is CUST
Customer details are in the 3rd column of CUST
Accounts are in the 4th column of CUST

Formula in B5

=VLOOKUP(A5,CUST,3,0) 

Formula in C5

=VLOOKUP(A5,CUST,4,0) 

copied down to desired row.

[Ken123]

Regarding the input-output, you only need VLOOKUP.


you have a named range called CUST

lookup values are in Column A (starting at A5)
lookup table is CUST
Customer details are in the 3rd column of CUST
Accounts are in the 4th column of CUST

Formula in B5

=VLOOKUP(A5,CUST,3,0) 

Formula in C5

=VLOOKUP(A5,CUST,4,0) 

copied down to desired row.

Hi mancubus,

thank you for your responses, and sorry i have make an mistake, there should not be any bank ref column in the name range (cust) thus vlookup is not usable.

i have attached a corrected excel for your easy reference.

[mancubus]

please dont quote previous messages unless you need highlighting a specific bit. in that case just qoute that small bit not the whole message.


that said, you are trying to find partial string from column A in column F.


since there is no common pattern for partian strings, according tome, the best way is to use a helper column and enter corresponding values from column F manually. this enables using VLOOKUP function.

i did it for you in the attached file.

Column D in "task" sheet is the helper column.

if you can set up a pattern then we can provide a either formula or vba solution without using the helper column.

examine the range A21:C29 and its header on "task" sheet.

good luck.

[Ken123]

Hi Mancubus,

Once again, thank you so much for guiding me thru.

Yes the LOOKUP NAMES in the Detail Description is not consistent at their position.

So i am thinking (but not sure if is feasible) is that i will set up a look up table. so everything there is the Detail Description, Example: < BENDIGO BANK 0000000000000000 DELANY ADVERT BAL > then the code will search the look up table one by one till they find < DELANY > so is probably using a loop with 2 exit condition ( when a match is found then return something and when the search finish the whole look up table and return nothing)

[mancubus]

you are welcome.

yes. i oftenly do that.
when the formula returns N/A error in a cell, i understand there new items to add, and update the look up table accordingly.

in that case vlookup will produce the desired results.

[Ken123]

Mancubus,

can u look at this excel and see if my logic is correct and whether is feasible?

thanks alot^^

[mancubus]

keep the table and the named range (CUST) in your file. because the following code uses them.
update this table so that it will cover all the (partial) values in column A.

Sub vbax_59174_find_search_list_in_column_return_corresponding_info()

    Dim srcList
    Dim foundCell As Range
    Dim i As Long
    
    srcList = Range("CUST").Value
    
    With Worksheets("task")
        For i = LBound(srcList) To UBound(srcList)
            Set foundCell = .Columns(1).Find(What:=srcList(i, 1), _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            If Not foundCell Is Nothing Then
                foundCell.Offset(, 1).Value = srcList(i, 2)
                foundCell.Offset(, 2).Value = srcList(i, 3)
            End If
        Next i
End With

End Sub

[Ken123]

Mancubus,

Thank you so much, took me so long to understand your code. its really amazing. will have to study more on VBA.

much appreciated.

Cheers
Ken

[mancubus]

you are welcome.

pls mark the thread as solved from Thread Tools dropdown for future references to the thread.

[Ken123]

Mancubus,

sorry, i have some trouble with the code,

lets say i expand and have DYMOCKS BRISBANE0000000000000000 DY0024 at cell A10, the code will not reflect the result at B10 and C10.

not sure if i fully understand the code. hope u can explain a little to me.

Many Thanks

Cheers
Ken

[mancubus]

so the values in column A are not unique?
there are multiple occurences of a "Detail Description"?

[Ken123]

Yes it will expand. and have duplicates... also in the future, i will expand the look up table as well.. so will have more information.

[mancubus]

this may be called classic to do a 'multiple instance search' as you can find it anywhere..

 Sub vbax_59174_find_search_list_in_column_return_corresponding_info_multi_instance()
      
    Dim srcList
    Dim FoundCell As Range
    Dim i As Long
    Dim FirstAddress As String
    srcList = Range("CUST").Value
     
    With Worksheets("task")
        For i = LBound(srcList) To UBound(srcList)
            Set FoundCell = .UsedRange.Columns(1).Find(What:=srcList(i, 1), _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
            If Not FoundCell Is Nothing Then
                FirstAddress = FoundCell.Address
            End If
        
            Do Until FoundCell Is Nothing
                FoundCell.Offset(, 1).Value = srcList(i, 2)
                FoundCell.Offset(, 2).Value = srcList(i, 3)
                Set FoundCell = .UsedRange.Columns(1).FindNext(After:=FoundCell)
                If FoundCell.Address = FirstAddress Then Exit Do
            Loop
        Next i
    End With
     
 End Sub

[Ken123]

mancubus,

this is so cool!!! works perfectly! thank you so much^^

appreciate your kind assistance.

Cheers
Ken

[mancubus]

welcome.
glad it helped.