Bank statement categories

[kbarton]

Hi


I am trying to get an Excel template where I can categorise bank statement data. Say I have a bank statement with 1000 entries covering a year of data in Excel I would like to get some kind of lookup formula that would look at the bank statement and then from a list of possible categories in a lookup table then put that data in a monthly income and expenditure list so that I can see at a glance what categories the funds are coming in and going out over a year.

The lookup list needs to extract parts of the description field as the entries are varied, even form the same type of category and can be variable. I would if possible like to do this for multiple accounts so I can see the income and expenditure for personal and business accounts downloaded into Excel automatically rather than have to manually go to each entry.

Example of the data

[TABLE="width: 426"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Account
[/TD]
[TD]Description
[/TD]
[TD]Amount
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]01/12/2015
[/TD]
[TD]123456
[/TD]
[TD]Cash deposit ATM
[/TD]
[TD]5000
[/TD]
[TD]20000
[/TD]
[/TR]
[TR]
[TD]02/12/2015
[/TD]
[TD]123456
[/TD]
[TD]Debit Tesco
[/TD]
[TD]-50
[/TD]
[TD]19950
[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone point me in the right direction?

Many thanks

Kev

 

[BobUmlas]

Pivot table is the way to go, but if the descriptions vary, you'll get multiple lines. It'd be MUCH better to get all your descriptions right.

 

[kbarton]

Pivot table is the way to go, but if the descriptions vary, you'll get multiple lines. It'd be MUCH better to get all your descriptions right.

Thanks Bob. I tried Pvot Tables but there are so many different descriptions for the same type of income or expenditure that it's not useful. For instance there may be 100 different cash withdrawals each from different venues all with different descriptions. I need some type of lookup tables that can list various descriptions and then something that can extract part descriptions say CashAtmLondon01102014 (the cash part). and list income and expense categories into monthly category totals.

Hope that makes sense. Many thanks.

Kev