Automating totals for keywords

[optimystery]

Hi, I am relatively new to Excel. I use it mainly for accounting, and realised that I was spending a lot of time doing repetitive highlighting of cells and copy/paste. I know the basics of programming so I'm prepared to dive in at the deep end.

What I would like to do is the following:

• search an annual .csv I export from my online banking
• separate results by month (format I get is 01/07/2014)
• find keywords within a string and create a group total (Sainsburys & Waitrose goes to a total for 'food', LUL & First Capital Connect goes to a total for 'travel' etc)
• I would then copy and paste this into another spreadsheet, but if it would be possible to import the totals to specific fields that would be even better.

Is any of the above possible and feasible?

 

[Asala42]

VBA could certainly do this, but the code involved requires detailed understanding of the actual export and how it pulls into Excel.
When you do this manually and bring in the csv data, does it pull in as a clean table? Are there junk rows/columns? Does the date come in formatted as text? etc

There may be some quick non-VBA alternatives. A formula to identify the month and something like a VLOOKUP could be used to classify specific entries as "Food", others as "Utilities", etc. Then you could create a pivot table that would give a solid summary by month. Even if the export has a lot of junk in it, you can usually put I a formula to identify the records you care about only and include that in the pivot criteria.

 

[optimystery]

I could attach a .xlsx file but there doesnt seem to be an option to do that?
It looks like this..
The Totals section is something I added to show how the script might output totals.


[TABLE="width: 697"]
<colgroup><col span="4"><col><col span="3"></colgroup><tbody>[TR]
[TD]Transaction Date[/TD]
[TD]Transaction Type[/TD]
[TD]Sort Code[/TD]
[TD]Account Number[/TD]
[TD]Transaction Description[/TD]
[TD]Debit Amount[/TD]
[TD]Credit Amount[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD="align: right"]04/08/2014[/TD]
[TD]DEB[/TD]
[TD]xx-xx-xx[/TD]
[TD]xxxx[/TD]
[TD]POST OFFICE COUNTE CD xxxx[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2014[/TD]
[TD]DEB[/TD]
[TD]xx-xx-xx[/TD]
[TD]xxxx[/TD]
[TD]SAINSBURYS S/MKTS CD xxxx[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD="align: right"]28/07/2014[/TD]
[TD]DEB[/TD]
[TD]xx-xx-xx[/TD]
[TD]xxxx[/TD]
[TD]WAITROSE CD xxxx[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Postal / Expenses[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Food Shopping[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]