Search bank data for more than one word in order to categorize expenditures

[sbselby]

Hello,

I import my bank data into XL.
It looks something like this:

A1= POS PURCHASE POS25471236 9856320 BLUE HAVEN MA BLUE HAVEN MA

A2= POS PURCHASE POS320021025 258413 BLUE HAVEN MA BLUE HAVEN MA

A3= CHECK CARD PURCHASE XXXXX5907 MOUNTAIN RESORT XXXXX8411 CO

A4= ACH CREDIT XXXXX939X JPMORGAN CHASE EXT TRNSFR

A5= CHECK CARD PURCHASE XXXXX1259 DUNKIN #998563 Q35 DALLAS TX

A6= CHECK CARD PURCHASE XXXXX1259 FISH MOTORS XXXXX2001 OR

A7= CHECK CARD PURCHASE XXXXX1259 MCDONALD'S F2951 DALLAS TX

A8= CHECK CARD PURCHASE XXXXX1259 MOUNTAIN RESORT XXXXX4811 CO

A9= ONLINE TRANSFER TO XXXXX1259

A10= CHECK 985 3663212

<tbody>
</tbody>

My goal is to sort my bank data into categories such as "gas", "food", "gym", etc., by labeling it in the B column next to each line of bank data.


To tell it what to search for and how to label the above data, I have category names in one cell, and the tag-words in an adjacent cell. For example:

A25 = food B25 = mcdonald's, dunkin, antonio's, etc.
A26 = gas B26 = exon, tedesco, shell, etc.

When any one of the words in B25 is found in A5 above for example, it will label that bank data row "food", in the B column next to it.

I have actually figured out how to do that much with the user defined function below. The problem is, I don't really understand the code for the function. I just copied and pasted it and it works great searching for single words. But I need to be able to enter multiple words in my tag words. I would like to be able to search not just for individual words like "mcdonald's", and "dunkin", but also for "burger king" or "antonio's italian pizzaria".

Help?


UDF I am currently using:



<code>Function DupeWord(str1 As String, str2 As String) As String
Dim vArr1
Dim vArr2
Dim vTest
Dim lngCnt As Long
vArr1 = Split(Replace(str1, " ", vbNullString), ",")
vArr2 = Split(Replace(str2, " ", vbNullString), ",")
On Error GoTo strExit

For lngCnt = LBound(vArr1) To UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
If Not IsError(vTest) Then DupeWord = DupeWord & vArr1(lngCnt) & ", "
Next lngCnt
If Len(DupeWord) > 0 Then
DupeWord = Left$(DupeWord, Len(DupeWord) - 2)
Else
strExit:
DupeWord = "No Matches!"
End If

End Function

</code></pre><code>


Thanks.
</code>

 

[mumps]

Try:

 Option Compare Text
Sub Test()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim MyArray1 As Variant
    MyArray1 = Split(Range("B25"), ",")
    Dim MyArray2 As Variant
    MyArray2 = Split(Range("B26"), ",")
    Dim i As Long
    For Each rng In Range("A1:A" & bottomA)
        For i = LBound(MyArray1) To UBound(MyArray1)
            If rng Like "*" & MyArray1(i) & "*" Then
                rng.Offset(0, 1) = Range("A25")
            End If
        Next i
        For i = LBound(MyArray2) To UBound(MyArray2)
            If rng Like "*" & MyArray2(i) & "*" Then
                rng.Offset(0, 1) = Range("A26")
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[sbselby]

Mumps,

Thank you so much for replying so quickly and for all the time you have put into this. Unfortunately, I am pretty new at XL and don't really understand macros and the like. Can you tell me where/how to enter this?

Thank you very much.
Sbselby

 

[mumps]

Not a problem. Do the following: Hold down the ALT key and press the F11 key to open up the Visual Basic Editor. On the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. You can run the macro from here by pressing the F5 key or by clicking 'Run' on the menu and then clicking 'Run' again in the drop down list. Close the window to return to your sheet. You can run the macro by assigning a short cut key to it or by inserting a button on your sheet and assigning the macro to it. Then all you have to do is click the button.

 

[sbselby]

Thanks. I'll give this a try!

 

[shyy]

Did you want only VBA solution?

If not, you could create a range listing all the names of the food. Do a search so if its found, to put in the category food. Create multiple ranges for different category that would have to be expanded when adding new names.