Bank statement conciliation: find a string match and link the desired sub-category

[aduroche]

Greetings,

This is what I assume to be a common request, but I've searched the web so much thru the past year, without success... Feel free to redirect me to an existing reference if you know any!
I have cobbled a workable option but the layout I must use for my unique character strings is not optimal and the maintenance of the sheet is becoming a little painful after almost a year of usage.
So I thought to myself, why not ask here? Any input or guidance would be greatly appreciated. I'm here to learn!

So this is the typical bank conciliation exercice from a .csv file download. For every budget sub-category that I have, I enter unique character strings derived off the bank statements for given types of expenditures.

I've simplified the most pertinent info below, but in a nutshell:
- The budget table contains a sample of how my data is laid out.
- Column K is what the result should be in Column L, automatically calculated. The long text string in Col. J is to be compared with the unique strings in Col. D-H.
- When a match is found, then we pull the associated sub-category (Col B).
- There typically is more than one possible string for a given sub-cat.
- I left the unused "StringN" cells empty for clarity, but they could be filled with something else if this can simplify the search or formula.
- If there is no match found, I currently put a "To be validated" warning so that I can manually come back to it.

Again -- any input would be appreciated. I've lurked to the usual Index and Match functions but all examples found generally refer to a single input cell that we want to match to, where here things are quite different?

Thanks in advance! --Alain

Budget_2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Cat	Sub-Cat	Budget	String1	String2	String3	Strin4	String5		Statement Entry	Desired Sub-Cat	Calculated Sub-Cat
2	Computers	Hardware	$ 1,000.00	B&H	Amazon					APPLE.COM/BILL 999-542-7113 WY	Software
3	Computers	Software	$ 300.00	Apple.com	Soundly					Amazon.ca Prime Member amazon.ca/prime	Hardware
4	Household	Home Insurance	$ 800.00	Lareau						SOUNDLY PRO MONTHLY OSLO 14.99 USD @ 1.302800000000	Software
5	Household	Electricity	$ 3,000.00	Hydro						COSTCO WHOLESALE W916	Groceries
6	Household	Taxes	$ 5,000.00	MyTown						BENNY & CO TOWN X	Restaurant
7	Car	Gaz	$ 1,000.00	Esso						CORNELI RESTAURANT OTHERTOWN	Restaurant
8	Car	Maintenance	$ 1,000.00	Garage						ALLSTATE INSURANCE 34732	Car Insurance
9	Car	Car Insurance	$ 400.00	Allstate						LA MOISSON THAT TOWN PA	Groceries
10	Food	Groceries	$ 6,000.00	La Moisson	IGA	Costco				GARAGE J LOCKWOOD & SON	Maintenance
11	Food	Restaurant	$ 500.00	Benny	Corneli					IGA #1299 BOSTON MA	Groceries
12										SHELL E049619 JAY PEAK, VT	Gaz
13										B&H NY NY	Hardware
14										EXXON MOBILE #4412	To be validated
MrExcel

 

[mmhill]

Maybe there's a way to do this with a formula, but I didn't see it. I think you have to use a loop as it is looking for the String1, String2 text inside the Statement Entry text. A custom function works nicely. In a VBA module ... either in the Budget workbook or the Personal.xlsx put this function

For each Statement Entry, the function loops thru to see if the String1, String2, etc. values are found within the Statement Entry. Once a match is found, it gets the sub-category associated with that string. It quits looking when a match is found or after it finds all the String1, String2, etc. that are in the range. It reads the string cells top to bottom. left to right and quits looking as soon as it finds a match. So, if Apple.com is in the String1, String2, ... list more than once, it will always fine the first one.

Public Function SubCat(strEntry As Range, rngMatch As Range, rngCats As Range) As String
Dim ay, ayB%, t%, element, i%, R%
    ay = rngMatch: ayB = UBound(ay, 1)
    t = rngMatch.Cells.Count - Application.WorksheetFunction.CountBlank(rngMatch)
    For Each element In ay
        i = i + 1
        If Len(element) > 0 And InStr(1, strEntry.Value, element, vbTextCompare) Then 'vbTextCompare is not case sensitive
            R = i Mod ayB: If R = 0 Then R = ayB ' Mod gets the remainder of a division.  e.g., 6 Mod 5 = 1, 22 Mod 10 = 2. This gets the sub-cat row we want.
            Exit For
        End If
        If Len(element) > 0 Then t = t - 1: If t = 0 Then Exit For
    Next element
    If R = 0 Then SubCat = "NONE" Else SubCat = rngCats(R, 1)
End Function

You can use this function one of several ways. The easiest way is to use it in a formula. In the cell where you want the Calculated Sub-Cat, enter ...

=subcat(J2,$D$2:$F$11,$B$2:$B$11)

J2 is the statement entry, $D$2:$F$11 are the string cells, $B$2:$B$11 are the Sub-Cat cells. Copy this cell down the whole list. It will populate with the calculated Sub-Cat.

The upside here is it's simple & flexible. The downside is if you have many of these formulas, it could slow down your spreadsheet calcs. 50-100+ uses won't matterbut solve this by copy/pastespecial values once you have them.

The next way is to use it as a macro. One option is ranges that are hard coded in. Paste code below in the module. To use it, select the range (L2:L14) and run the macro. Note that the "Statement Entries" must be 2 columns to the left and there are specific ranges in the code. This is simple but not flexible. Good for if your worksheet never changes.

Public Sub hardSubCat()
Dim element As Range
    If Selection.Areas.Count > 1 Then Beep: Exit Sub
    For Each element In Selection.Cells
        element.Value = SubCat(element.Offset(0, -2), Sheet1.Range("D2:H11"), Sheet1.Range("B2:B11"))
    Next element
End Sub

The other macro option is to use Areas. Paste code below into your module. In the worksheet, select 4 areas ... hold down the CTRL key. First select the cells where you want to put the Calculated Sub-Cat. Then (holding CTRL) select the cells with the Statement Entries (these first two areas MUST have the same number of rows) then select the String1,String2, etc cells, then the Sub-Cat cells. With this selection, run the sub. It is important to select these areas in the correct order.

Public Sub varSubCat()
Dim i%
    With Selection
        If .Areas.Count <> 4 Or .Areas(1).Rows.Count <> .Areas(2).Rows.Count Then Beep: Exit Sub
        For i = 1 To .Areas(1).Cells.Count
            .Areas(1).Cells(i).Value = SubCat(.Areas(2).Cells(i), .Areas(3), .Areas(4))
        Next i
    End With
End Sub

 

[Alex Blakenburg]

You might also want to look into using Power Query's Fuzzy Matching.
A couple of references:

Fuzzy Match with Power Query - Excel University

 

[aduroche]

Maybe there's a way to do this with a formula, but I didn't see it. I think you have to use a loop as it is looking for the String1, String2 text inside the Statement Entry text. A custom function works nicely. In a VBA module ... either in the Budget workbook or the Personal.xlsx put this function

For each Statement Entry, the function loops thru to see if the String1, String2, etc. values are found within the Statement Entry. Once a match is found, it gets the sub-category associated with that string. It quits looking when a match is found or after it finds all the String1, String2, etc. that are in the range. It reads the string cells top to bottom. left to right and quits looking as soon as it finds a match. So, if Apple.com is in the String1, String2, ... list more than once, it will always fine the first one.

Public Function SubCat(strEntry As Range, rngMatch As Range, rngCats As Range) As String
Dim ay, ayB%, t%, element, i%, R%
    ay = rngMatch: ayB = UBound(ay, 1)
    t = rngMatch.Cells.Count - Application.WorksheetFunction.CountBlank(rngMatch)
    For Each element In ay
        i = i + 1
        If Len(element) > 0 And InStr(1, strEntry.Value, element, vbTextCompare) Then 'vbTextCompare is not case sensitive
            R = i Mod ayB: If R = 0 Then R = ayB ' Mod gets the remainder of a division.  e.g., 6 Mod 5 = 1, 22 Mod 10 = 2. This gets the sub-cat row we want.
            Exit For
        End If
        If Len(element) > 0 Then t = t - 1: If t = 0 Then Exit For
    Next element
    If R = 0 Then SubCat = "NONE" Else SubCat = rngCats(R, 1)
End Function

You can use this function one of several ways. The easiest way is to use it in a formula. In the cell where you want the Calculated Sub-Cat, enter ...

=subcat(J2,$D$2:$F$11,$B$2:$B$11)

J2 is the statement entry, $D$2:$F$11 are the string cells, $B$2:$B$11 are the Sub-Cat cells. Copy this cell down the whole list. It will populate with the calculated Sub-Cat.

The upside here is it's simple & flexible. The downside is if you have many of these formulas, it could slow down your spreadsheet calcs. 50-100+ uses won't matterbut solve this by copy/pastespecial values once you have them.

The next way is to use it as a macro. One option is ranges that are hard coded in. Paste code below in the module. To use it, select the range (L2:L14) and run the macro. Note that the "Statement Entries" must be 2 columns to the left and there are specific ranges in the code. This is simple but not flexible. Good for if your worksheet never changes.

Public Sub hardSubCat()
Dim element As Range
    If Selection.Areas.Count > 1 Then Beep: Exit Sub
    For Each element In Selection.Cells
        element.Value = SubCat(element.Offset(0, -2), Sheet1.Range("D2:H11"), Sheet1.Range("B2:B11"))
    Next element
End Sub

The other macro option is to use Areas. Paste code below into your module. In the worksheet, select 4 areas ... hold down the CTRL key. First select the cells where you want to put the Calculated Sub-Cat. Then (holding CTRL) select the cells with the Statement Entries (these first two areas MUST have the same number of rows) then select the String1,String2, etc cells, then the Sub-Cat cells. With this selection, run the sub. It is important to select these areas in the correct order.

Public Sub varSubCat()
Dim i%
    With Selection
        If .Areas.Count <> 4 Or .Areas(1).Rows.Count <> .Areas(2).Rows.Count Then Beep: Exit Sub
        For i = 1 To .Areas(1).Cells.Count
            .Areas(1).Cells(i).Value = SubCat(.Areas(2).Cells(i), .Areas(3), .Areas(4))
        Next i
    End With
End Sub

mmhill... WOW! This is super generous of you, thanks so much. I was not expecting this much at all...

I will give it a whirl.
I'm quite green to VBA, but will test it out in my "real" spreadsheet, which may turn to be tricky since the budget and the statement are on two different sheets?
From the looks of it, I would be tempted to use the "Areas" macro approach.

I'll make sure to report back here when I have time to try this out.

Thanks again!

 

[aduroche]

You might also want to look into using Power Query's Fuzzy Matching.
A couple of references:

Fuzzy Match with Power Query - Excel University

Alex, very useful... Both articles are very informative, thanks!
Yet again, another feature of Excel that I'm not familiar with!

I will give it a go as well, I'll report back when I've had time to familiarize myself with these.
MyOnlineTrainingHub's video is a little fast, but from what I read, Excel University's step-by-step article will likely be more appropriate for me as a first experience with this tool.

Thanks again!

 

[aduroche]

mmhill... WOW! This is super generous of you, thanks so much. I was not expecting this much at all...

I will give it a whirl.
I'm quite green to VBA, but will test it out in my "real" spreadsheet, which may turn to be tricky since the budget and the statement are on two different sheets?
From the looks of it, I would be tempted to use the "Areas" macro approach.

I'll make sure to report back here when I have time to try this out.

Thanks again!

mmhill... I'm absolutely thrilled to report the Areas Macro approach wins big time... even though I don't have any VBA experience

It works perfectly in the downsized sample I posted here, but unfortunately does not work in the "real" workbook, where the bank statement is one sheet ("Statements") and the budget is another one ("Budget").
As a result, the first 2 areas are on "Statements", and the other 2 are on "Budget".

The macro does not seem to "pick-up" the multiple selection across the sheets.
I assume this would require sheet name hardcoding?

I could combine the 2 sheets into one, that's not a big deal.

Thanks again for your amazing help!

 

[mmhill]

I’ll look at it when I have a chance. Shouldn’t be a difficult fix. In the meantime, try the formula approach. After it calculates, copy and pastespecial values. That will get rid of the formula.

 

[mmhill]

mmhill... I'm absolutely thrilled to report the Areas Macro approach wins big time... even though I don't have any VBA experience

It works perfectly in the downsized sample I posted here, but unfortunately does not work in the "real" workbook, where the bank statement is one sheet ("Statements") and the budget is another one ("Budget").
As a result, the first 2 areas are on "Statements", and the other 2 are on "Budget".

The macro does not seem to "pick-up" the multiple selection across the sheets.
I assume this would require sheet name hardcoding?

I could combine the 2 sheets into one, that's not a big deal.

Thanks again for your amazing help!

Thinking about it, I don’t think you can have an area over 2 sheets. If that’s the reason, the fix is simple - instead of 1 range with 4 areas, it needs 2 ranges of 2 areas each. Not a hard fix.

 

[mmhill]

The macro below will work across multiple sheets. The SubCat functio is the same as before I just included it here.

Unlike the last one where you select a 4 area and then run the macro, this one you run the macro - it asks you to select the Result and Statement Entry Ranges andthen the String and SubCat ranges. This way we have 2 ranges with 2 areas each. This macro also does some simple validation.

Public Sub varSubCat2()
Dim i%, msg$, rngSel As Range, booTest As Boolean, ayRng(1 To 4) As Range
    
    For i = 1 To 3 Step 2
        msg = Choose(i, "Select Result and Entry ranges", , "Select Strings and Sub-Category ranges")
        Set rngSel = Application.InputBox(msg, "Get Ranges", Type:=8)
        
        GoSub TestRange
        If booTest Then
            Set ayRng(i) = rngSel.Areas(1): Set ayRng(i + 1) = rngSel.Areas(2)
        Else
            i = i - 2
        End If
    Next i
    
    For i = 1 To ayRng(1).Cells.Count
        ayRng(1).Cells(i).Value = SubCat(ayRng(2).Cells(i), ayRng(3), ayRng(4))
    Next i
    
Exit Sub

TestRange:
    With rngSel
        booTest = .Areas.Count = 2
        If booTest Then booTest = .Areas(1).Rows.Count = .Areas(2).Rows.Count And .Areas(2).Columns.Count = 1
        If booTest And i = 1 Then booTest = .Areas(1).Columns.Count = 1
    End With
    If booTest = False Then tmp = MsgBox("That doesn't work. Try again.", vbOKCancel + vbCritical, "Selection Error")
    If booTest = True Or tmp = vbOK Then Return Else Exit Sub
End Sub

Public Function SubCat(strEntry As Range, rngMatch As Range, rngCats As Range) As String
Dim ay, ayB%, element, i%, R%
    ay = rngMatch: ayB = UBound(ay, 1)
    t = rngMatch.Cells.Count - Application.WorksheetFunction.CountBlank(rngMatch)
    For Each element In ay
        i = i + 1
        If Len(element) > 0 And InStr(1, strEntry.Value, element, vbTextCompare) Then
            R = i Mod ayB: If R = 0 Then R = ayB
            Exit For
        End If
        If Len(element) > 0 Then t = t - 1: If t = 0 Then Exit For
    Next element
    If R = 0 Then SubCat = "NONE" Else SubCat = rngCats(R, 1)
End Function

 

[bebo021999]

Formula based option:

VD.xlsx
	A	B	C	D	E	F	G	H
1	Cat	Sub-Cat	Budget	String1	String2	String3	Strin4	String5
2	Computers	Hardware	1000	B&H	Amazon
3	Computers	Software	300	Apple.com	Soundly
4	Household	Home Insurance	800	Lareau
5	Household	Electricity	3000	Hydro
6	Household	Taxes	5000	MyTown
7	Car	Gaz	1000	Esso
8	Car	Maintenance	1000	Garage
9	Car	Car Insurance	400	Allstate
10	Food	Groceries	6000	La Moisson	IGA	Costco
11	Food	Restaurant	500	Benny	Corneli
Budget

VD.xlsx
	A	B	C	D
1	Statement Entry	Desired Sub-Cat	Output
2	APPLE.COM/BILL 999-542-7113 WY	Software	Software
3	Amazon.ca Prime Member amazon.ca/prime	Hardware	Hardware
4	SOUNDLY PRO MONTHLY OSLO 14.99 USD @ 1.302800000000	Software	Software
5	COSTCO WHOLESALE W916	Groceries	Groceries
6	BENNY & CO TOWN X	Restaurant	Restaurant
7	CORNELI RESTAURANT OTHERTOWN	Restaurant	Restaurant
8	ALLSTATE INSURANCE 34732	Car Insurance	Car Insurance
9	LA MOISSON THAT TOWN PA	Groceries	Groceries
10	GARAGE J LOCKWOOD & SON	Maintenance	Maintenance
11	IGA #1299 BOSTON MA	Groceries	Groceries
12	SHELL E049619 JAY PEAK, VT	Gaz	To be validated	Should not "Gaz", since no "SHELL" found in budget
13	B&H NY NY	Hardware	Hardware
14	EXXON MOBILE #4412	To be validated	To be validated
Statements

Cell Formulas
Range	Formula
C2:C14	C2	=IFERROR(INDEX(Budget!$B:$B,AGGREGATE(15,6,ROW(Budget!$A$2:$A$11)/ISNUMBER(SEARCH(IF(Budget!$D$2:$H$11<>"",Budget!$D$2:$H$11,"$|_"),A2)),1)),"To be validated")
Press CTRL+SHIFT+ENTER to enter array formulas.