I want to import data into a spreadsheet and then be able to add a column that will automatically tag or label that column based on the text in another column. For instance If B2 has the words "Shell" then D2 has the word "Gasoline". If B3 has the words "McDonalds" then D3 displays the word "Fast Food". Once an association is chosen, then it makes the same choice throughout the sheet.
Something like VLOOKUP maybe?
Thanks
Edit: Or is there a bookkeeping template that has this in it ?
-- Edited by Soar on Thursday 22nd of August 2013 09:25:07 AM
I don't use openoffice so you will need to find the equivalent but personally I would :
- Set up a macro to grab another spreadsheet and import it into a worksheet.
- The same Macro should copy the column with the information that you need to column B of the working spreadsheet.
- A seperate worksheet should hold your Vlookup database
- Column D should be your Vlookups.
- You may need to do a refresh (shift F9) on completion of the Macro to populate column D.
I would also be prone to tieing the macro to a custom button in the spreadsheet. Possibly also make the shift F9 part of the macro rather than separate (just use the record function to build our macro in the first place).
In future you will always need to import data into the same feeder spreadsheet in order for the Macro to work so it may also be a good idea to get the Macro to save a backup of the original spreadsheet as part of the process. (#1)
Play around with the above (espechially the save path) as necessary but just wanted to show how to auto add the date to the backup without changing the macro.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
stuck again.. I can make vlookup work if it is searching for an exact complete phrase but how to make the vlookup search for a phrase found anywhere in a cell?
So it will pick out Tescos from "Birmingham Tescos 8.50 etc etc"
the your lookup database is of course your normal Vlookup bit.
HTH,
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
=VLOOKUP("*"&Tescos&"*",your database, col index, range)
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
but how do i do this for multiple phrases, i want to search thru each transaction description, and categorise it, so need to do it for tescos, mortgage, petrol etc..
atm i am using this
=VLOOKUP(C6,$M$2:$O$20,3,0)
but only works if i list in the M column the exact and complete transaction description in the C column.
surely each phrase is going to need its own column.
Of course, taking that approach what would you do where you have an entry where (for instance) you buy petrol from Tesco? The information would appear in two columns.
Do you want to send your spreadsheet as is to shamus.bkn@virginmedia.com so that I am able to visualise the issues better (doesn't sound as though the spreadsheets too big).
Just take the company / individuals name off the version that you send me.
I'm a bit busy at the mo but would certainly be able to turn it around this evening.
kind regards,
Shaun.
p.s. for anyone else. once we have a fix I will post the answer back on here again (without the data of course).
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
