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

aduroche

New Member
Joined
Jan 30, 2008
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
1CatSub-CatBudgetString1String2String3Strin4String5Statement EntryDesired Sub-CatCalculated Sub-Cat
2ComputersHardware$ 1,000.00B&HAmazonAPPLE.COM/BILL 999-542-7113 WY Software
3ComputersSoftware$ 300.00Apple.comSoundlyAmazon.ca Prime Member amazon.ca/prime Hardware
4HouseholdHome Insurance$ 800.00LareauSOUNDLY PRO MONTHLY OSLO 14.99 USD @ 1.302800000000Software
5HouseholdElectricity$ 3,000.00HydroCOSTCO WHOLESALE W916 Groceries
6HouseholdTaxes$ 5,000.00MyTownBENNY & CO TOWN XRestaurant
7CarGaz$ 1,000.00EssoCORNELI RESTAURANT OTHERTOWN Restaurant
8CarMaintenance$ 1,000.00GarageALLSTATE INSURANCE 34732 Car Insurance
9Car Car Insurance$ 400.00AllstateLA MOISSON THAT TOWN PA Groceries
10FoodGroceries$ 6,000.00La MoissonIGACostcoGARAGE J LOCKWOOD & SON Maintenance
11FoodRestaurant$ 500.00BennyCorneliIGA #1299 BOSTON MA Groceries
12SHELL E049619 JAY PEAK, VTGaz
13B&H NY NYHardware
14EXXON MOBILE #4412To be validated
MrExcel
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
VBA Code:
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 ...
Excel Formula:
=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.
VBA Code:
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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 ...
Excel Formula:
=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.
VBA Code:
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.
VBA Code:
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!
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Solution
Formula based option:
VD.xlsx
ABCDEFGH
1CatSub-CatBudgetString1String2String3Strin4String5
2ComputersHardware1000B&HAmazon
3ComputersSoftware300Apple.comSoundly
4HouseholdHome Insurance800Lareau
5HouseholdElectricity3000Hydro
6HouseholdTaxes5000MyTown
7CarGaz1000Esso
8CarMaintenance1000Garage
9Car Car Insurance400Allstate
10FoodGroceries6000La MoissonIGACostco
11FoodRestaurant500BennyCorneli
Budget

VD.xlsx
ABCD
1Statement EntryDesired Sub-CatOutput
2APPLE.COM/BILL 999-542-7113 WY SoftwareSoftware
3Amazon.ca Prime Member amazon.ca/prime HardwareHardware
4SOUNDLY PRO MONTHLY OSLO 14.99 USD @ 1.302800000000SoftwareSoftware
5COSTCO WHOLESALE W916 GroceriesGroceries
6BENNY & CO TOWN XRestaurantRestaurant
7CORNELI RESTAURANT OTHERTOWN RestaurantRestaurant
8ALLSTATE INSURANCE 34732 Car InsuranceCar Insurance
9LA MOISSON THAT TOWN PA GroceriesGroceries
10GARAGE J LOCKWOOD & SON MaintenanceMaintenance
11IGA #1299 BOSTON MA GroceriesGroceries
12SHELL E049619 JAY PEAK, VTGazTo be validatedShould not "Gaz", since no "SHELL" found in budget
13B&H NY NYHardwareHardware
14EXXON MOBILE #4412To be validatedTo be validated
Statements
Cell Formulas
RangeFormula
C2:C14C2=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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top