Fuzzy Search Function: IF the word "X" is located in adjacent cell then, put "XXX" here

[ChrisOK]

Need a fuzzy search function that will look to the cell at right and if a certain word is detected within that description, then place a category name here...

(Accounting/categorizing hundreds of rows of bank statement transactions)

Example: Using Manual Method[\b]
A B[\b]
-----------------------------------------------------------------------------------
1 IF="ICE" found, | POS PURCHASE/EFT TRANS 5814 / ICE VENDING Choctaw OK
put "Inventory" |
here...... |
--------------------------------------------------------------------------------

Ideally, having a look up table where the formula pasted in A will look to the table and use whichever item matches:[\b]
Example: Using a More Automated Method with LookUp Table[\b]

A B[\b]
------------------------------------------------------------------------------------------------
1 IF="ICE" found, | POS PURCHASE/EFT TRANS xxx / ICE VENDING CITY ST
on table Col G, copy |
"data from H here...... |
-----------------------------------------------------------------------------------------------
1 IF="SHELL" found, | POS PURCHASE/EFT TRANS xxx / SHELL OIL xxxxxx CITY ST
|
here...... |
-------------------------------------------------------------------------------------------------
1 IF="HARDWARE" | POS PURCHASE/EFT TRANS xxx / WESTLAKE HARDWARE CITY ST
found,
|
here...... |
-------------------------------------------------------------------------------------------------

Lookup Table: (this is what the Col A function would look to/scan down the list for a fuzzy match)[\b]

Col G Col H
LOOK FOR............PASTE THIS CATEGORY
ICE.....................Inventory Expense
SHELL.................Gas Expense
HARDWARE...........Repairs Expense

 

[Glove_Man]

This function should see you right:

Function FuzzySearch(StringToTest, rngLookUp As Range, rngReturn As Range)

    Dim i As Integer, Test As Variant

    If rngLookUp.Rows.Count <> rngReturn.Rows.Count Or rngLookUp.Columns.Count <> rngReturn.Columns.Count Then
        FuzzySearch = "ERROR - Lookup and Return ranges are different sizes."
        Exit Function
    End If

    On Error Resume Next

    For i = 1 To rngLookUp.Cells.Count
    
        If IsError(Application.WorksheetFunction.Find(rngLookUp.Cells(i).Value, StringToTest)) Then
    
        Else
            FuzzySearch = rngReturn.Cells(i).Value
            Exit Function
        End If
    Next i

    On Error GoTo 0

    FuzzySearch = ""


End Function

 

[ChrisOK]

Not sure how to implement this... when I mentioned 'function' I meant a formula/function... to paste down Column A and have it look to Col B.
Something like =LOOKUP..blah..blah..blah

I'm familiar with using Sub () frequently in vba, running them from customizing ribbon buttons or via the Macro editor -- but not so much on functions.. if you can expand on running it - I may be able to use this..

 

[DavidCoop]

Not sure how to implement this... when I mentioned 'function' I meant a formula/function... to paste down Column A and have it look to Col B.
Something like =LOOKUP..blah..blah..blah

I'm familiar with using Sub () frequently in vba, running them from customizing ribbon buttons or via the Macro editor -- but not so much on functions.. if you can expand on running it - I may be able to use this..

If I may answer for the Glove_Man, this is what he had in mind.

The VBA creates a User Defined Function (UDF). Just paste it in the normal VBA Module, and enter it as shown.

Excel 2010
	A	B	C	D	E	F	G	H
1	POS PURCHASE/EFT TRANS 5814 / ICE VENDING Choctaw OK	Inventory Expense					LOOK FOR	PASTE THIS CATEGORY
2	POS PURCHASE/EFT TRANS xxx / ICE VENDING CITY ST	Inventory Expense					ICE	Inventory Expense
3	POS PURCHASE/EFT TRANS xxx / SHELL OIL xxxxxx CITY ST	Gas Expense					SHELL	Gas Expense
4	POS PURCHASE/EFT TRANS xxx / WESTLAKE HARDWARE CITY ST	Repairs Expense					HARDWARE	Repairs Expense
Sheet1

Cell Formulas
Range	Formula
B1	=fuzzysearch(A1,$G$1:$G$4,$H$1:$H$4)
B2	=fuzzysearch(A2,$G$1:$G$4,$H$1:$H$4)
B3	=fuzzysearch(A3,$G$1:$G$4,$H$1:$H$4)
B4	=fuzzysearch(A4,$G$1:$G$4,$H$1:$H$4)

 

[Glove_Man]

David - with quality like that you may answer for me at any time!

Chris - sorry, I saw the html tags in your original post and assumed... Have you managed to get the function working?

Once you have placed the function into a module in the VBA editor, you can use the function as you would a standard Excel function, including copying it down the column.

 

[ChrisOK]

OMG - just tested it out and it is AMAAAAAZING! Love it Guys! So kool! You two make a great team!
The example/explanation was fabulous...and of course, the vba function is spot on!

Thanks!
Learned something new and will use it to help relieve some pain for others dealing with thousands of lines of data that need quickly categorized...
Many Thanks! Cheers!

 

[DavidCoop]

OMG - just tested it out and it is AMAAAAAZING! Love it Guys! So kool! You two make a great team!
The example/explanation was fabulous...and of course, the vba function is spot on!

Thanks!
Learned something new and will use it to help relieve some pain for others dealing with thousands of lines of data that need quickly categorized...
Many Thanks! Cheers!

You're welcome Chris - your pleasure brings me pleasure!

 

[Glove_Man]

No worries at all

 

[ChrisOK]

FYI: I clicked "LIKE" on both your posts to help you guys out on your ratings!
Hv a great wk-end!