I'm hoping to build a formula that automatically selects a category based on keywords/phrases from an imported bank statement. This is a very similar question that Ryan Voigt has asked back in Feb which Blind Spot has answered however my spreadsheet is slightly different and I'm afraid my knowledge of google sheets formula is very basic to reuse it. I attached an editable copy of the Google Sheet I'm looking for help with:
Under the 'Description' row, I have my monthly bank statement transactions. I would like to build a formula that can reference the 'Description' (Column C:C) and then auto populate the 'Category' (Column E:E). For Ryans solution, Blind Spot mentions a REGEXMATCH.
For example, based off the first transaction:
If text from Column C:C in 'Description' contains the phrase "TFL travel", auto select category 'Public transport' on E:E
I would like to create a series of these rules to autopopulate as many of these categories/transactions as possible, I'm just not sure how to reuse the REGEXMATCH from the other post to apply to my own.
Here is Blind Spots formula: =TEXTJOIN(", ", TRUE, MAP(categoryName, categoryStr, LAMBDA(name, str, IF(AND(REGEXMATCH(INDEX(D:D,ROW()), str), str<>"", ROW(str)>5), name, IFERROR(1/0)))))
I have tried reusing it and apply to my own document like this: =TEXTJOIN("public transport", "TFL TRAVEL CH ", TRUE, MAP(descriptionName, descriptionStr, LAMBDA(name, str, IF(AND(REGEXMATCH(INDEX(C:C,ROW()), str), str<>"", ROW(str)>5), name, IFERROR(1/0)))))
And I'm getting error: Unknown range name: 'DESCRIPTIONSTR'.
I also tried a new column/cell for the categories without a drop down menu like this: =TEXTJOIN("public transport, TFL TRAVEL CH", MAP(descriptionName, descriptionStr, LAMBDA(name, str, IF(AND(REGEXMATCH(INDEX(C:C,ROW()), str), str<>"", ROW(str)>5), name, IFERROR(1/0)))))
Same error.
In its helpful for reference, here's the link for the original question>