Solved

Excel - auto analyze/organize Bank statement csv

Posted on 2010-09-23
4
2,576 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:rpelfrey
  • 2
4 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33744778
Sounds like you can use the sumproduct formula to do this.

=SUMPRODUCT(($B$2:$B$20=$H2)*(D$2:D$20))


See attached example.


Book2.xls
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 33744819
Here's a more specific example from what you attached:

=SUMPRODUCT((bankstatement!$B$9:$B$23='Summary of Expenditures'!B4)*(bankstatement!$C$9:$C$23))

SampleBnkStmnt.xls
0
 

Author Comment

by:rpelfrey
ID: 33744878
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.  
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Route Recommendation 18 31
Office 2016 without internet 6 36
Excel - Data Validation 3 26
Excel Question 17 15
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question