1

I'm hoping to build a formula to automatially select a category based on select keywords/phrases from an imported bank statement. I attached an editable copy of the Google Sheet I'm looking for help with:

Under the 'Transactions' sheet, I have imported the first 3 transactions from my monthly bank statement. I would like to build a formula that can reference the 'Description' fields (Column D7 downwards) for select keywords/phrases. I was hoping the formula could populate the 'Category' in Column B automatically, either referencing the drop down menu or Column B in the 'Categories' sheet.

For example, based off the first 2 transactions:

  • If text from Column D in 'Transactions' sheet contains the phrase "Debit Google ADS", select category 'Advertising'.

  • If text from Column D in 'Transactions' sheet contains the keyword "Spotify", select category 'Music'.

I would like to create a series of these rules to autopopulate as many of these categories/transactions as possible, I'm just uncertain of the process or where to begin. Where would I even insert a formula for this type of rule?

I believe I'm looking to do something similar to this question: Categorize text in Google Sheets based on matching partial text in the Category index

2
  • Welcome to Web Applications. While a link to a external resource, in this case a spreadsheet, might be helpful, please bear in mind that question should be self contained. Considering this, pleas describe the relevant parts of your spreadsheet directly into the questionbody, including the country setting as this affect the separator used for formula parameters among other things. Also add some sample data both for input as for the expected result, show what you have tried (directly in the question body). Feb 27, 2023 at 21:41
  • It's great that you have included a link to what you found from this site, please also add why it didn't meet your needs. Feb 27, 2023 at 21:41

1 Answer 1

0

REGEXMATCH

Use REGEXMATCH to a predefined list of strings to see if they exist in the description string from your bank statement.

Named Range

Name Range Description
categoryName Categories!B:B Column with category names
categoryStr Categories!D:D Column with strings to match

Formula

The formula should be copied down the Category column in your sheet labeled Transactions. While a single formula could be adapted to populate all the rows, the current approach means that the data can be in any order and can be easily manipulated (e.g. custom sort).

The formula filters the "Categories Name" column Categories!B:B where there is a match with the strings on the same row of the "Bank Statement Keywords/Phrase" column Categories!D:D and your "Description" column Transactions!D6:Dx. Any resulting rows (values) are returned after being joined into a single string delimited by ", " (comma + space). Wrapping the formula in TEXTJOIN insures a single value is returned and not an array. If you explicitly, or accidentally, introduce multiple matches, you will immediately see that in the result: "<category>, <category>" instead of "<category>".

=TEXTJOIN(", ", TRUE, 
    MAP(categoryName, categoryStr, LAMBDA(name, str, 
       IF(AND(REGEXMATCH(INDEX(D:D,ROW()), str), str<>"", ROW(str)>5),
          name, IFERROR(1/0)))))

Formula


Named Ranges

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.