Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting entries out
Hi there,
I am trying to analyse my expenses (to see where am I going wrong!). I am exporting my online bank statement to excel and it is comes out like this: 18/10/2007 20-67-** 6******* -42.75 PAYMENT GOURMET BURGER KIT REF 289 There are hundreds of lines, each representing a transaction. What I want to do is to take each line and copy it to a designated worksheet based on what kind of transaction it is. For instance the entry above contains the words 'gourmet' and 'burger', as such it should be copied into the 'Food' worksheet. What I want to be able to do is to have an editable list of words which categorise each workbook (i.e. for food, any transactions including the words tescos, burger, sainsburies...etc) Is there an XL wizzard out there who could answer my prayers??? Duncs ps. I know some very very basic VB |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting entries out
this isn't going to be a magical program that will be able to determine which
transactions arre foods which are gas which are home accesories. You are asking for tooooooooo much. Yes, still VBA can help! I would recommend creating code that would move the items to differrent worksheets with manual assistance. 1) Create a worksheet changge function. Type a letter in the last column (differrent lettter for each category). The wolrksheet change would recognize the letter and then move the row to the correct worksheet. or 2) Create some shortcut keys (Shft - Cntl - key) that excel would recoginize and move the lines to the correct worksheet. "dangerd" wrote: Hi there, I am trying to analyse my expenses (to see where am I going wrong!). I am exporting my online bank statement to excel and it is comes out like this: 18/10/2007 20-67-** 6******* -42.75 PAYMENT GOURMET BURGER KIT REF 289 There are hundreds of lines, each representing a transaction. What I want to do is to take each line and copy it to a designated worksheet based on what kind of transaction it is. For instance the entry above contains the words 'gourmet' and 'burger', as such it should be copied into the 'Food' worksheet. What I want to be able to do is to have an editable list of words which categorise each workbook (i.e. for food, any transactions including the words tescos, burger, sainsburies...etc) Is there an XL wizzard out there who could answer my prayers??? Duncs ps. I know some very very basic VB |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting entries out
If you can categorize each line, meaning that you will need to insert
at least a character for each line in a new column (F for food, C for clothes, etc) i can give you directions on how to code with VBA or even with plain excel formulas. Best On Oct 27, 10:24 pm, dangerd wrote: Hi there, I am trying to analyse my expenses (to see where am I going wrong!). I am exporting my online bank statement to excel and it is comes out like this: 18/10/2007 20-67-** 6******* -42.75 PAYMENT GOURMET BURGER KIT REF 289 There are hundreds of lines, each representing a transaction. What I want to do is to take each line and copy it to a designated worksheet based on what kind of transaction it is. For instance the entry above contains the words 'gourmet' and 'burger', as such it should be copied into the 'Food' worksheet. What I want to be able to do is to have an editable list of words which categorise each workbook (i.e. for food, any transactions including the words tescos, burger, sainsburies...etc) Is there an XL wizzard out there who could answer my prayers??? Duncs ps. I know some very very basic VB |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting entries out
Thanks for your responses,
Perhaps I did not make it very clear but there are many of the same transactions in the list. For instance I went to Tescos 20 times over the last couple of months, as a result the word 'Tesco' is going to appear within a string in the description column. Can I not have a master list of words (defined by myself) and have the program perform the following function: 1- Lookup the list of words for the food worksheet 2- In the Transaction Description column, look for any matching string by seing if within the cell there is a string that is the exact match to the pre-defined list. 3- When there is a match, copy the row to the appropriate worksheet (food in this instance) 4- Carry on moving down the list In essence, it is a bit like having an extra column with classification (A, B, C...etc) like Exciter suggested with the only difference being that there is an extra step to generate the classification. Many Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting numeric and alpha entries | Excel Discussion (Misc queries) | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
sorting columns with more than 2 text entries | Excel Discussion (Misc queries) | |||
Sorting lists to remove double entries | Excel Discussion (Misc queries) | |||
How do I change multi-line entries to single line entries in Exce. | Excel Worksheet Functions |