• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Fiancial information between two dates

P

Pete Mccann

Member
As one of my "non-work" activities, I am involved with a small charity. We can claim Gift Aid from the UK government on any monies (membership fees or cash donations) to the charity. We summarise these cash gifts and submit a claim to the UK government. The government then pays us 25% of all donations received and this forms a considerable contribution to our charity. I manage the funds received through a spreadsheet. In the attached file (an extract of a bigger file) we have a list of members names, Membership type and a set of dates when they have either paid their membership fees ("Date membership paid 2018" and the associated amount "Amount of Membership 2018) or donations (#1 to #6) and the associated dates of these donations.
  • Some of our members do not pay yearly fees but may pay donations.
  • Some of our members only pay yearly fees.
  • Some of our members make donations only and do not pay yearly fees.
I would like to summarise all the contributions (membership fees and donations) between two dates X and Y. Ideally I would like to get a table that looks like the table in the "Output" sheet.

Many thanks in advance.
 

Attachments

  • Chandoo_Extract financial information between two dates.xlsm
    69.3 KB · Views: 7
the problem you are going to have with your current set up is that the data is not in a normalized manner. Your data should be set up in the following manner:

Name |PaymentType|PaymentDate|Amount|


With your data input in this manner, you can then run Sumif scenarios or Pivot Tables quickly to get the information you need. Additionally, you can then use the built in Excel Filters.

If you are a bit adventuresome, I would suggest that you convert this to an Access Data Base, but you will need to convert your data to a normalized presentation.
 
Last edited:
I have taken your sample and normalized the data. With the data in this manner, I was then able to complete your target expectation. See Attached.
 

Attachments

  • Chandoo_Extract financial information between two dates.xlsm
    81.5 KB · Views: 11
Hi Alan. After I tried to do a few pivot tables on the data, I realised that the structure was not correct. I hadn't used the normalising function before but that might be one thing I can do in the future. I haven't used Access for many years and the people I share the spread sheet with have probably never even heard of it. However, I think I do need to do something to the bigger file because I keep having to add information to get particular outputs. In the master file, I have one row in the spread sheet for each member and then lots of columns, each one with a particular piece of information (address, email etc). This all works pretty much OK except for when there are things like the donations. If we only had one money transaction per person per year, it would be OK but since we can have many (or none) transactions per person per year, then it gets complicated. Anyway, many thanks for your help. You have given me a solution to the problem.
 
You must log in or register to reply here.
Back
Top