rpelfrey
asked on
Excel - auto analyze/organize Bank statement csv
I am attempting to analyze my bank statement which I can down load as a csv file. I am not necessarily looking for a "swiss army" knife approach, just simplicity. This does not have to be a finished product, just some directions in how to capture the data and display it.
I would like to drop the raw csv file in one worksheet and in another have it set up to look at the transaction description column and search on key words that are listed in category columns, capture the debt or deposit for that description and then sum up these categories so that it gives a picture of where expenditures are going.
This needs to accommodate varying number rows since the number of transaction can vary month to month. SampleBnkStmnt.xls
I would like to drop the raw csv file in one worksheet and in another have it set up to look at the transaction description column and search on key words that are listed in category columns, capture the debt or deposit for that description and then sum up these categories so that it gives a picture of where expenditures are going.
This needs to accommodate varying number rows since the number of transaction can vary month to month. SampleBnkStmnt.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is a good beginning approach, but doesn't take in account that the description is not that "clean". Those words in your example would appear in the transaction description but additional transaction info is in there like store name and #, trans #, sometimes location address, and or phone number.
=SUMPRODUCT(($B$2:$B$20=$H
See attached example.
Book2.xls