Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I have an Excel spreadsheet. It has two columns. Column A contains a list of bank transactions e.g. Tescos, Boots Chemist, Petrol station A, Petrol Station B, B&Q. Column B has a predefined category based on the value in column A. E.g. Tescos - Food Shopping or Petrol Station B - Car Fuel. The categories are currently stored in a list and selected by manually chosing from the drop down list.
Question: Is it possible to autofill column B? Perhaps by using the If function or a macro?
From what I've read it seems quite possible with if column A has a fixed set of values. But I cannot find anything that suggests how to do this if you have to search through a string of text. Any Excel wizzes able to help?
vlookup
Like [url= http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/ ]this[/url]?
Does VLookup work if the Unique Identifier is hidden amongst a load of other text? I.e. lets say the unique identifier is "Tescos" but the cell contains "Tescos 20 January 2014"
Woodlikesbeer - the final parameter (true/false) determines exact match or not. If NOT then you need to use a sorted list and beware it picks the one before you might expect. You could use LEFT() to truncate the fields to try and help - but its all going to be messy...
you may be asking for a level of intelligence that excel wasn't really designed for. There is almost always a way to make it work - but if you are new to vlookup then you probably have a steep curve - is it worth the pain?
Match and Index will do it. Similar to Vlookup but more flexible. You just need to build a lookup table. In the first column of the lookup table are all the unique places (Tesco, Boots etc.) and the second column has all the categories.
Then to autofill column B you use Match to look up the target place from column A in the lookup table (Match returns the row) then use Index to extract the category from the same row in the second column of the lookup table.
If the target place in column A is "Tescos 20 January 2014" then you can use the Left function to extract just the leftmost characters.
Match & index will be functioning in the same way as vlookup for this, why add to the confusion?
Left on it's own won't do it as the text he'll be looking for will be variable length. If cell A2 contains "xxxx 20 January 2014" then formula =LEFT(A2,FIND(" ",A2)-1) in cell B2 will pick up xxxx regardless of the length of xxxx - but only if it's the first piece of text required AND has no space before it AND has one following it.
If the required text is "buried" amongst other text then it gets tricky.
I'd be examining the source data formats and download options to find if there's any standardisation you can exploit.
For example, I would expect the date to originally be in a separate field which would be retained in a csv file opened to Excel. I'd also expect bank transaction narratives to have a max length. If punctuation is included in the format it can be used in the same way to find the length as a space.
On the other hand different banks use different formats, businesses can choose what to include to a limited extent, or their business name may dictate it, and manual transactions by the bank are free text. You may have to accept that only your routine items get identified and leave the rest in the other pot to be manually categorised.
I tried doing all this about 10 years ago, with Excel, Access, and even Money (I think it was called), and it surprised me how there didn't seem to be a satisfactory automated home budgeting solution available at the time. I haven't checked since, but with apps now able to recognise songs, surely there must be something about.
[img] 
[/img]
Rob likes that ? 😀