Sorting entries out

[dangerd]

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

 

[Guest]

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.

 

[www.exciter.gr]

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

 

[dangerd]

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