0

I have the same problem as mention in the post How do I use Excel to categorize bank transactions into easily understandable categories to see where my money is going?

I've tried the formula given but it doesn't always work.

This is when it won't work, text search:

"Restaurant Baton Rouge"
"Baton Rouge DRUMMONDVILLE"

I want those 2 sentences to be categorised as "Restaurant". But the formula is searching for the line number that match with EACH word found in the "Text search" colomn. So if the category "Restaurant" has been found on line 20 and "Baton Rouge" on line 5, it will return the value of the line 25 (20+5).

Is it possible to stop searching when excel find the first match ?

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73
Lucky Luke
  • 1
  • 2
  • I use a spreadsheet with Data Validation in a "Category" column. As the transactions are loaded in, I just click the drop down and select the category. Would that work for your sheet? It's not automated, but is pretty easy to use. I keep it in Dropbox so I can just add transactions on the fly. I've since switched to a Google Sheets document, because that's even easier to edit on my phone, but the principle is the same. – jrichall Mar 09 '18 at 21:00
  • Thanks for the answer. I already have the dropdown box with value but I want Something automated because I have a lot of transaction by month to categorise. – Lucky Luke Mar 10 '18 at 20:39

1 Answers1

1

For a simple but effective solution, you could try a VLOOKUP function.

Taking the example from your original question:

Hopefully you'll see an example screenshot here

I'm not sure how it would work with a substring contained in a cell but you could just create a table of categories where you contain many possibilities of the same cell value which would return the right category.

EG:
Restaurant Baton Rouge = Restaurant
Baton Rouge DRUMMONDVILLE = Restaurant

This approach has its advantages, for example, when you have many Paypal transactions. A transaction description often has "PAYPAL *vendor", so you'd retrieve the wrong categories if you were just searching for the "PAYPAL" substring.

The syntax of a VLOOKUP function is =VLOOKUP(lookup value, lookup range, column number for return value, match type). You can refine this to suit your needs. For example, my real-life version uses names and table references but does the exact same thing as above. =VLOOKUP([@[Transaction Description]],tblCounterparts,2,FALSE)

Considering that you want to keep adding to the list of categories list and modifying it, you could investigate named ranges and the offset function vary its size.

Stephen Rauch
  • 3,091
  • 10
  • 23
  • 26
KMinnieA
  • 36
  • 3