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

 

[aduroche]

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

Bingo... I'm sooo very thankful. Keeping this very preciously, lots to chew on and learn from. Thank you mmhill!

 

[aduroche]

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.

bebo021999, thank you! Another great option indeed. Very generous of you, thanks for taking the time to help me out. I'll dissect this formula with great pleasure and learn from it. Cheers!

 

[aduroche]

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!

I just want to mention that your formula approach works wonders as well, I don't think I had mentioned it.
I just ran a short test in my workbook and all is well.
Unsure after how many rows things will bog down, but as you wrote, the copy/paste option will solve that.

So thanks to you, I have 2 solid options to work with.

Thanks again - cheers!