I am trying to make a homemade version for Autocat in the excel template.
I first copy/pasted the contents of the Autocat tab from a working google sheet.
Then my approach was to insert a XLookup function in the Category column cells in the transaction tab to search the “Description Contains” column (from the Autocat tab in excel, of course) and bring the corresponding category.
But I am not there yet. It only works for exact matches, so it is very fragile and unreliable. I think I need to figure out how to properly insert the logical criteria for the xlookup function to properly address the expected “cell contains” behavior.
I would very much appreciate is someone can shed some light here.
Has anyone cracked this? Am I using the right approach?
Thanks
PS… no VBA solutions please… I would need an extra life to learn how to code in VBA at my age.
There are some good reasons to not use VBA but I doubt that’s one of them.
But anyway, you should be able to add the wildcard character "*" into your lookup to search for additional characters that aren’t specifically called out. Say if you wanted to categorize for "My Favorite Gas Station" but it comes up in the description as "My Favorite Gas Station Store #420", if your search was for "My Favorite Gas Station*" it would match anything that started with that, regardless of what follows.
I don’t want to discourage you, @jorgeelizondom, since AutoCat for Excel would be a wonderful addition to the ecosystem, but the AutoCat processor in the (Google) Tiller Money Feeds add-on a) is implemented with scripts, and b) weighs in at 1,544 lines of code.
It’s really not that complicated of a problem, but I’m skeptical it can be implemented with in-cell formulas.
That is exactly the conclusion I am arriving at.
I have been fiddling with a few concepts and none come even close to being as robust as Autocat in google.
I will call it a day.
VBA learning could indeed be a great COVID project.
One other super powerful excel capability that may be able to make this work is power query. It has some data transformation capabilities that I could see making Autocat work without VBA. Here is a link to some good YouTube videos that wake you through some of the capabilities. https://www.youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac
@jorgeelizondom - Please don’t take @randy 's comments as being negative, but rather realistic.
AutoCat has the “code smell” of something that is initially simple, but continues to spawn edge cases and corner cases as you continue to peel back the complexity of what should be simple, but which has never been coordinated by the 100’s of institutions that made up their own rules.
P.S. I just found one such layer to the onion… my bank annotates a credit with “/ord Credit” as their hint to negate the value in the amount field. Without recognizing that subtlety, my transactions appear to be a double purchase rather than a purchase (appropriately transmitted as negative amount for the purchase) and a return (which should have been a positive amount for the return).
