I'm wanting to categorize a bank statement from a list of rules in excel. I've tried using a vlookup but I would like to be able to have non exact matches and as far as I know vlookup is not suited to this.
For instance if my statement looked like this and was located in worksheet "Statement"
Date | Transaction desciption | Amount
7/3/2013 | The Home Depot | $345.00
7/4/2013 | McDonald's #27 | $4.50
And I had a list of rules located in worksheet "Rules"
Rule | Category
The Home Depot | Home improvements
McDonald's * | Fast food
Is there a simple way to add another column using vba to the sheet "Statement" called Category that uses the rules to generate categories for each transaction?
Simple, no. I've done something similar in the past, this is how I did it.
1) Setup a rules page, I've called mine 'Patterns'. These patterns are setup with with the A column (from A2 on) being the 'name' and the B column being the regex pattern.
2) Load these into a public variable with the following code (I put a button on the patterns sheet to run this macro and load them into memory.):
Option Explicit
Public Patterns() As String
Sub LoadPatterns()
Dim cell As Range
Dim bRow As Range
Sheets("Patterns").Activate
'select column A, and load into first dimensino
Range("A2", Sheets("Patterns").Range("A" & Sheets("Patterns").Range("A:A").Rows.Count).End(xlUp).Address).Select
ReDim Patterns(Selection.Rows.Count - 1, 1)
For Each cell In Selection
Patterns(cell.Row - 2, 0) = cell.Value
Next
'select column B and load into the second dimension
Range("B2", Sheets("Patterns").Range("A" & Sheets("Patterns").Range("A:A").Rows.Count).End(xlUp).Address).Select
For Each cell In Selection
Patterns(cell.Row - 2, 1) = cell.Value
Next
End Sub
3) Create the following UDF, load the VB regex library as a reference in vba (Microsoft VBScript Regular Expressions 5.5, see http://www.macrostash.com/2011/10/08/simple-regular-expression-tutorial-for-excel-vba/) and call it on your transaction description as a formula after running step 2:
Public Function rxBanking(sName As String)
Dim x As Integer
'Get & load Patterns
Dim regex As New VBScript_RegExp_55.RegExp
Dim match
For x = 0 To UBound(Patterns)
regex.Pattern = Patterns(x, 1)
regex.ignorecase=True
match = regex.Test(sName)
If match Then
rxBanking = Patterns(x, 0)
Exit For
Else
rxBanking = "Unknown"
End If
Next
End Function
so for example, after you've loaded a pattern such as:
Category | RegEx pattern
--------------------------------
Home loan | INTEREST[\s]CHARGED
If your transaction data was in cell D1, then you could categorise it using the formula
=rxBanking(D1)
If you reload your pattern, you will need to re-copy your formulas down on the spreadsheet, as it doesn't automatically recalculate.
For help using regex (which even if you are familiar, you might need) I find a great testing ground is http://regexpal.com/