Oh the shame of it, not being able to work Excel 🙁

Anyway, I’ve finally realised that I need to work out what I spend on what, to get some idea of when/if I could retire. I’ve downloaded 3 years transactions from my bank app into a spreadsheet and am trying to categorise the transactions based on the text in column C.

Sample transactions are:
Trafford Council
United Utilities Water
DVLA-UMS841T
WEANS FOOTBALL CLUB FP10/10/2003 686876876446
2677 – EVANS CYCLES LTD, KENDAL
2677 – WIGGLE – 21/2/2004
2677 Bob’s jewellery shop 23/12/2009

I want to categorise into:
Council Tax
Water
DVLA
Football
Bikes
etc

I started going through the transactions manually allocating to a category, which was going to take decades. So then I changed it to do a filter search for all transactions containing “wiggle” and then bulk allocating them to Bikes; then the same with EVANS Cycles, then Trafford Council etc. But that was still going to take years for the 3500 transactions.

So what I need is a formula that looks at each transaction and checks if a particular piece of text in the Category lookup appears in that transaction, and if it does to populate the category field on the transaction sheet.
At first glance it looks like an easy thing to do, but the problems seem to be twofold:
1) I’m not looking up the transaction against the category table, I’m looking for the category within the transaction table. So a vlookup won’t work
2)I’m not looking for an exact match, I need a wildcard match, which limits the options. Eg I want the EVANS lookup to work regardless of which store I purchased from and which date.

I found something pretty close: =OR(COUNTIF(C3,”*”&Lookup!A$1:A$4&”*”)) which basically returns TRUE if the a Category is found, but doesn’t return the category itself. The inner bit of this formula, =COUNTIF(C4,”*”&Lookup!A$1:A$4&”*”), essentially returns an array of all the Categories and a 0 or 1 depending on whether there is a match. eg for the United Utilities row above it returns {0,1,0,0,0} and for the jewellery line it returns {0,0,0,0,0}

At the other end of the problem, I think it’s pretty easy to return the nth value in a range or array by specifying a number. So for example INDEX(C15:C18,2,1) will return the 2nd value in my categories list (assuming they are in column C rows 15 to 18.

But what I need is a version of INDEX which takes the array from the COUNTIF above rather than just a number.

Anyone know a formula that takes an array and uses it to find a value in a range?

Or another way of doing this in Excel?

thanks

I think a simple IFS function with some OR  inside the brackets would do it, if I’ve understood the requirement correctly. Sorry I’m horribly hung over.

This should help explain.

Id use find combined with iserror. Eg =if(iserror(find(“water”,A1))=false,”water”,””)

Just change the cell reference and the search text in the inverted commas.

I do this on our monthly budget spreadsheet and I Too found it difficult to set up rules; an item with “Tesco” in the description might have been a £3.50 meal deal so fall under the lunch/eat out category, or it might be the weekly shop or it could have been petrol for the car…

The way I got round this was to make two columns after the transactions. First column was a drop down list of around 30 common transactions ie;

petrol

broadband

home insurance

groceries

cycling

cash savings

eat/drink out

(you get the idea)

this takes me 5 mins to categorise each month’s transactions. Once I have these labels, they then get lumped together via a VLOOKUP;  mortgage, home insurance and broadband etc go in the “household running” category . Petrol, car tax and insurance goes into the “vehicles” category and so forth.

once these two columns have been populated; one manually and one automatically it then auto fills a pivot table for each month.

Each month I can see trends / when DD’s have changed etc. it only takes around 5 mins to do a months worth of transactions and provides quite a good insight to where I’m squandering money. When I first done it I discovered that all the “I’ll just pick up a snack on my way home from work” actually all added up to a significant sun each month!

It’s doable but a bit of a faff

Create a sheet ‘List’ for your categories then in column A put the keyword and column B put the category eg

Wiggle Bikes
Tesco Groceries
Waitrose Groceries
Admiral Insurance

Then to save maintenance effort define a Name (Formulas, Define Name) – call it Reference and in the “Refers to” box type

=List!$A$1:INDIRECT(“List!$A$”&COUNTA(List!$A:$A))

That just defines a named range that includes the populated values in the List (don’t leave any gaps) for use by the next function, and means you can add categories without breaking anything

Then on your “Statement” sheet, assuming the name to match is in column A, in whatever column you want the category to be in paste

=IFERROR(OFFSET(List!$B$1,MAX(IF(ISERROR(FIND(Reference,Statement!A1)),0,ROW(Reference)))-1,0),”Misc”)

And that should populate the relevant category from the list, or “Misc” if it’s not categorised.

thought about using macros instead? its possible to do with lookups etc, but I find stuff like this easier to tweak and manage when its proper code

Could you put it into power query and group the Tesco ones together then separate as required.

I suspect no matter how you try and lookup the data you’ll be left with a load of odd transactions that’ll need manually allocating. That being the case and there being ‘only’ 3500 transactions my crude approach would be to simply sort by the transaction ref so all the like transactions and repeat transactions are together then categorise them manually.

Easiest answer is to stop buying stuff   🙂

A macro would be my first approach. A while since I’ve written one, but something like:

Get first search term from list

Do search while not having reached the last transaction

If found, move one cell right and paste search term; continue

Loop with next search term

The correct answer is to input your data properly in the first place but since you’ve downloaded it that ship has sailed.

Can you break the text by spaces into individual cells to give data you can easily work with instead of long strings? That’s a doddle if the formatting is correct in the first instance.

Not attempting to answer your Excel question.
But you could try AceMoney Lite (lite is freeware) – which supports the open finance formats which the bank tend to use.
Then either use the data in there or use that as an intermediate step.