+ Reply to Thread
Results 1 to 8 of 8

Create Monthly report of Ledger in Excel

  1. #1
    Registered User
    Join Date
    03-07-2004
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    35

    Create Monthly report of Ledger in Excel

    Dear,
    I want to create a report from a Ledger excel data file which contains data about Transactions of payment and invoice received.
    this table has many columns like date of purchase, Date of Payment, supplier name, Payment amount, Invoice received Amount. Balance amount.
    I want to create a monthly report which have these filters to show.
    party name
    previous balance
    Transactions between two given dates
    and this month transaction data.
    Best regards
    (Attached image is just a sample of requirement)
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by ndmalam; 10-08-2021 at 03:53 AM. Reason: uploading data excel file
    I love to learn

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    417

    Re: Create Monthly report of Ledger in Excel

    UPDATED: Not exactly sure what you are looking for...I put in a Pivot Table to summarize the data. You can revise it many ways. You can even reduce the display to 1 line summary for entire period. Pivot tables are very flexible. Check them out! You can rearrange data and many other things. Add info etc Let me know if this has helped you in any way ~ "ADD REPUTATION" would be great!
    Attached Files Attached Files
    Last edited by queuesef; 10-08-2021 at 02:43 PM.

  3. #3
    Registered User
    Join Date
    03-07-2004
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    35

    Re: Create Monthly report of Ledger in Excel

    Quote Originally Posted by queuesef View Post
    Not exactly what you are looking for...I put in a Pivot Table to summarize the data. You can revise it many ways. You can even reduce the display to 1 line summary for entire period. Pivot tables are very flexible. Check them out! You can rearrange data and many other things. Add info etc Let me know if this has helped you in any way ~ "ADD REPUTATION" would be great!
    I think you missed the file to upload, Please upload the file you created as pivot table. best regards.

  4. #4
    Registered User
    Join Date
    03-07-2004
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    35

    Re: Create Monthly report of Ledger in Excel

    Dear,
    As I said Earlier in the post, I want to add Start Date and End Date to Filter the Date , Please help me on this issue.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,684

    Re: Create Monthly report of Ledger in Excel

    Not sure what you want as the picture and the file only partly match, however perhaps this will help:
    1. Select the Date filter in cell A1, select Between and then select the start date and end date
    2. The start date is also displayed in cell K1 using: =SUBTOTAL(5,A2:A1617)
    3. The end date is also displayed in cell N1 using: =SUBTOTAL(4,A2:A1617)
    4. The previous balance is displayed in cell Q1 using: =INDEX(H2:H1617,MATCH(K1,A2:A1617,0)-1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    03-07-2004
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    35

    Re: Create Monthly report of Ledger in Excel

    hi ,
    probably I could not explain my requirements, please view the new attached file in which I manually designed my requirement. I want to filter data to , first filter the supplier Name , then filter transactions with start and end date, so I may see the complete record list of given dates and selected "supplier",
    thanks for your kind attention.
    best regards.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,684

    Re: Create Monthly report of Ledger in Excel

    As there were no manually placed values for the Filterd transactions I am guessing at the results which are obtained by using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As for opening Blance from start Date here and Total Balance Up to Date here, I am going to leave those until we are given the expected results so that we can make sure that our formulas/code yield the correct values.
    Let us know if you have any questions.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-07-2004
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    35

    Re: Create Monthly report of Ledger in Excel

    Quote Originally Posted by JeteMc View Post
    As there were no manually placed values for the Filterd transactions I am guessing at the results which are obtained by using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As for opening Blance from start Date here and Total Balance Up to Date here, I am going to leave those until we are given the expected results so that we can make sure that our formulas/code yield the correct values.
    Let us know if you have any questions.
    yes it works,
    now my next requirement is opening Balance for "Start Date" and Closing Balance for "End Date" and Total Balance over all.
    best regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to create General Ledger in Excel with or without VBA
    By SVongdara in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-17-2014, 10:03 AM
  2. Autopopulating expenses in a monthly checking ledger
    By Centinel87 in forum Excel General
    Replies: 3
    Last Post: 07-22-2013, 09:33 AM
  3. Macro for perfoming same data manipulation to monthly files to create a report
    By newbieexcelgirl in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2012, 09:38 AM
  4. [SOLVED] Tenants ledger report
    By Pupabella in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 03-12-2012, 03:57 PM
  5. Ledger Sheet Report
    By LS Flowers in forum Excel General
    Replies: 3
    Last Post: 11-30-2008, 04:50 AM
  6. [SOLVED] i need monthly ledger that is catagorized by payment due dates
    By j.marie in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-01-2005, 08:05 AM
  7. How do I create a report to monitor monthly utilities
    By domino in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-05-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1