Thetownend.com

Can anyone help? I'm trying to look at a better way of checking to see that members of club of about 200 are paying the correct fees every month. At present, we have a time consuming process where we manually check through bank statements and standing order payments for each individual.

Can anyone recommend a better more automated way?

P.S. I've got access to Excel. I'm sure there must be a template there that I can use so am looking down that route initially.

Shouldn't there be a way to filter a statement based on the reference? If everyone used the same reference when paying fees, you could filter and then work out who's paid based on a quick vlookup against a record of bank accounts?

Must admit to being a bit of a dunce when it comes to this stuff. What I think I have is the following:

Dataset 1: Name of person, amount they should pay (e.g. John Smith, £52.50)
Dataset 2: Standing order name, amount (e.g Smith_swim, £52.50)

The first problem I have is that the standing order names are different for each individual. So, presumably, I'd have to add an extra field to the first dataset so it could correlate with the second.

The next problem I have is the amount that is charged each month can vary for upto 10% of the membership.

The third problem I face is that I've never used Excel to do anything than add up totals in columns! :-)

This is the sort of thing I help businesses resolve...

if you want something that actively advises you that a payment is overdue or calculates what payment should be made then excel isn't necessarily the best route (its the cheapest to set up but the most costly in terms of time spent managing it.

I recently created a website with membership solution linked to paypal that automatically records and manages club subscriptions and also makes it easier to manage DD/SO/Cash payments. There are also some accounting systems that can do a similar thing with API's (sage, Kashflow etc) which I can do as well.

If the club can fund it, it will make it a lot more efficient in tracking payments...

I've been surfing and looking at some similar website solutions. Unfortunately, being a non profit making club, volunteer time is (allegedly) easier to come by than funding for making processes more efficient.

If you add the standing order names to the first dataset it'll be a piece of piss to use excel to compare two lists.

What do you do if you have this scenario:

Expected:
John Smith £10  Smith_monies
James Smith £10 Smith_monies
Jack smith £10 Smith_monies

Actual
Smith_monies £10

You would definitely need a second field to identify the correct Smith even if that were a sort code or account number. I presume this is being scrape from the club's bank statement so you should have some more information to hand (hopefully).

Yes, scraped from bank statements. However, the example I was trying to get a cross was if there are 3 children in the Smith family and Mum is paying for all 3.

Ah right, then you're screwed :D

You need at least 3 data sets...

Account payer (master data set)

Account members (linked to account payer with amounts for each)

Transaction details (linked to account payer)

Which means you're better off with a simple database...

That Smith family must have money to burn.

In that example does it matter who the money is attributed to? Or can the money vary per child? You can search for a string of text within a cell so you can start to pull out the relevant info but as MacPhlea said its gonna cost you a fair bit of time with tinkering.

Wonder if they want to invest in STFC?

Can't they put a reference number on the payment?

@TDP: So, I have a couple of families that pay a one off fee for multiple kids. For others I have individual payments for each child.

@MacPhlea: Friend of mine just said the same. Think I'll try to get a rudimentary Excel spreadsheet running and then play with Access afterwards.

If I tell you that it has taken me all morning to sort out a vlookup, you'll realise that it might take me a while to do anything complicated! I'm going to concentrate on looking at what format I can get the bank statement info into first - Hopefully csv (but I doubt it)

Its as much as I can do to get them to pay at all! Not keen on asking them to make changes that might jeopardise income for a couple of months.

Grrrrrrrrrrrrrrr !

Spent all last night looking at this and using a vlookup that I thought would work, but it didn't.

I have the following:

Spreadsheet 1:
Column E has the 'bank transaction description'
Column J has the 'total fees due'
I want to put how much has been paid into Column K

Spreadsheet 2 'Data From Bank':
Column A has 'bank transaction description'
Column B has 'amount paid'
I want to put the vlookup into

Pretty please someone put me out of my misery and tell me what the vlookup code should be! I've spent hours getting erroneous results!!

If you don't now either you'd be better off focusing your learning curve on access otherwise you'll have to learn both and excel doesn't easily transfer to access...

The wife has most of the data in Excel at the moment, so should be able to get quicker results with that. Have got a Excel 2007 book and 2 hours watching the kids swim in a minute to try to puzzle that out.

Access is something I've always wanted to learn, but never had a good enough reason to do it. So now I might! This will be a long term replacement for the quick fix in Excel.

Not sure if this is what you mean but I think you want =vlookup(E1,sheet2 A1:B999,2,false)

If she has most of it in excel the transferring it to access before you start playing with vlookups might be a better option - if you want me to tell what data should go into what datasets then I'm happy to do this or you can have a go and send over what you think should go where... 

The general rule of databases is splitting down grouped data into unique chunks that aren't repeated...

For example your paying members will be be unique and would be assigned a unique ID but they may have multiple family members but each are unique so they would go into a separate table and asigned a unique ID with a reference ID to the unique ID of the paying member (relationship).  Likewise their payments would be multiple but unique so again each would have a unique ID and a reference to the paying member ID.

Once you have your database table and datasets you can then start building queries using the relationships (far easier than vlookups!)

Thanks for this. I found out in the end why I had so many problems with this. Turns out that I had to have 'sheet 2' sorted alphabetically before doing the vlookup. When it wasn't sorted, it came up with inconsistent numbers and errors - Piece of shit!