Lookup text in string and return relevant value from reference table

[CKAtWork]

Dear Forum,
I have been looking around various forums, but did not find exactly what I am looking for... VB is not yet my strong point...

Here is my situation/question that I am hoping to find a solution for in Excel and in VBA
Context:
Personal Budget. I export to excel the expenditures from my online account for a given month and I want excel to automatically add a category next to every expense (e.g. entertainment, mortgage, kids, etc), depending on Key words that I list in a reference table. For instance an expense could read: "DEBIT TPV SHELL SERVICE". I would have a keyword SHELL, which for me is category: Fuel.
Setup:
Two tabs:
1) Export - here I paste the exported data
Column A: Date
Column B: name of expense/transfer/etc
Column C: amount
Column D: here want the category to appear as matched with the Reference table in Reference.
2) Reference - this is the reference table with the key words:
Column A: Key word
Column B: Category

The reference table I will elaborate on over time as more and more cases occur. I realise it won't be a perfect coverage (and there could be conflicting key words), but it would surely already cover 70-80% of the expenses (many of them are recurring).

I hope I could express myself clear enough I would be very grateful for some code that you can think of that I can experiment with.

Thanks a lot in advance!
CKAtWork

 

[Chris Mack]

Hi

This is working for me:

Sub Export()
    ex = "Export"
    ref = "Reference"
    n = Sheets(ex).Cells(Rows.Count, 2).End(xlUp).Row
    k = Sheets(ref).Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To k
        For j = 2 To n
            term = CStr(Sheets(ref).Cells(r, 1))
            If InStr(1, Sheets(ex).Cells(j, 2), term) > 0 Then
                If Sheets(ex).Cells(j, 4) <> "" Then
                    Sheets(ex).Cells(j, 4) = "DUPLICATE: Please match manually"
                ElseIf Sheets(ex).Cells(j, 4) = "" Then
                    Sheets(ex).Cells(j, 4) = Sheets(ref).Cells(r, 2)
                End If
            End If
        Next
    Next
End Sub

Assumptions are that your exported transaction start on row 2, and that your reference table begins in cell A1.

Hope this helps,

Chris.

 

[CKAtWork]

Beautiful! I love your feature with the Duplicate - wow. I am so envious - wish I was so at ease with the VBA. Imagine the productivity gains.

Thanks a million
Christian

 

[Smitty]

Welcome to the Board!

Quicken would be easier.

 

[CKAtWork]

There's a thought

Thank you

 

[Chris Mack]

Beautiful! I love your feature with the Duplicate - wow. I am so envious - wish I was so at ease with the VBA. Imagine the productivity gains.

Thanks a million
Christian

You're welcome!