Hi
I d/L from my bank transactions in csv.
I'll like to have it sorted in categories with totals in the same s/h if possible
similar to the attached format.
It would be handier if it was differed columns showing income & expenses.
or similar to pass on to the accountant.
Thanks for your help.
Last edited by Rocmel; 10-18-2017 at 03:52 AM.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
The non working paper clip is a challenge for us newbies, thank you
It's an irritation to the rest of us - the powers that be have known that it is not working for a very long time, but have failed to fix it. Sorry!
Please explain the attachment: what do you get in the .csv file and how do you want it laying out? It's not clear.
Is that what you want?
(PivotTable)
but this is not VBA
Last edited by sandy666; 10-18-2017 at 04:29 AM.
Thanks Sandy
Yes that is what I want but in 2 sections.
1 for income (positive) say columns E-F-G
1 for Expenses (negative) say columns I-J-K
columns formatted as Date - Description - amount
at the end of transaction columns Grand Totals (just as there are now)
By the way how do you use it
excuse my ignorance
Hi
I would add an extra column.
Head the columns Date, Amount, Source and Trans
In the Trans column have a formula
=IF(B2>0,"Inc","Exp")
Then convert this into a Table, by placing your cursor in any cell and using Insert > Table > check the box my table has headers
One of the great advantages of a table, is that it expands automatically as you add more rows, and any formulae get repeated to any added rows.
Then each month, copy your three columns of data immediately below the last row of the existing table (ensuring that your data copied doesn't include any header),
and the formulae will extend automatically.
Then you can summarize with as Pivot Table, (it doesn't have to be on the same sheet as the data as I have shown in my example)
There are many choices for layout of Pivot tables, and you couls alter to suit our requirement, but the attached is one view.
For more information on Pivot Tables and how to use them there is a fabulous resource from Debra Dalgleish at
http://www.contextures.com/pivottableindex.html
--
Regards
Roger Govier
Microsoft Excel MVP
Re: Sorting & categorizing bank transactions csv - pivot
You can do that like in post#8 or use PowerQuery (add-in for Ex2010) like here:
anyway
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
Thanks Roger
I like the 2 worksheets method.
excuse my ignorance but why is it necessary to duplicate the statement.
Also the "Exp" column seems unnecessary since we know that -ve = Expences or
If its positive then anything else is negative.
Thanks Sandy
The income is fine as is.
I would like the expenses to be grouped with totals for each group.
as in your first post.
Regards
Rocmel
Re: Sorting & categorizing bank transactions csv - pivot
You mean like this?
But you will need PowerPivot and DataModel (PowerPivot free add-in from MS for Ex2010)
Re: Sorting & categorizing bank transactions csv - pivot
Or you can try this one: two pivot tables with the same source but different cache (and I think this is what you need)
If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.
Than you to all
I think Rogers #8 may be the most suitable for me
Thanks for the feedback & rep
so,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks