Excel Geeks

[timmyg]

Without having read the earlier bit properly, sounds like a SUMIF before the lookup would be combining totals for multiple VLOOKUP results.

Might have read it wrong though!

[Bewster]

Sumif is an excellent tool but I am not sure it would work in this case as the vlookup comes first and you will have multiple variables.

NMH - Going back to the bank  ref - if the parents paid two amounts do those two amounts have the same references on the bank statement ?

[chalkies_shorts]

As soon as you start talking about V or h lookups then you're in database territory.

[Bewster]

My thinking is this - if the two payments have the same ref then you could do a pivot table on the bank data and then a vlookup on the pivot table.

If the bank reference is the same month on month then spend the time to set up everybodies name with a specific code (the payment ref) next to it and do as Timmy suggested using a sumif on this code.

[Nomoreheroes]

Unfortunately, looking more closely, I can see that the payment details can be named 'slightly' differently.

[Simon Pieman]

It sounds like you should make use of the text to columns function before using any formulae

[@MacPhlea]

As soon as you start talking about V or h lookups then you're in database territory.

^^^ this ^^^

Can you imagine what google would be like if it used vlookups and hlookups?

[Nomoreheroes]

It sounds like you should make use of the text to columns function before using any formulae

I'd taken a simple manual spreadsheet over from the wife and am trying to automate things as much as possible. At the moment I'm just trying to get the low hanging fruit.

I had this idea that I could simply cut and paste 2 columns of a downloaded csv into a sheet and magic would happen to check what had been paid each month against what was supposed to be paid.

I'm now seeing that its not that easy!

I've managed to sort a few problems but a few remain. As you can tell, I'm really an Excel novice and am learning lots from the TEF collective!

[Nomoreheroes]

^^^ this ^^^

Can you imagine what google would be like if it used vlookups and hlookups?

Yeah, I know. But unfortunately I can just about understand the basics of Excel, but am really struggling to get started with Access. I'm sort of hoping that as my Excel sheet evolves, it will morph into a format that will form the basis of a database. (Or it may well be that the club will buy Hytek Business Manager which will make my spreadsheet redundant)

[Bewster]

Unfortunately, looking more closely, I can see that the payment details can be named 'slightly' differently.

OK, for vlookups or sumifs to work you have to know what to lookup or sum.

You say the payment refs are different - so I am assuming "Smith12345" and "Smith54321" say ?

Will the payments have the same refs next month ? What I am trying to find is if there are any constants with which we can work with

[Nomoreheroes]

Next thing I am working on is:

I have some families that have more than one swimmer. Some pay with multiple standing orders while others pay with a single standing order.

The way I have got around this was by creating an extra column in my master page where (for the families that pay one standing order) I manually added up what their total should be and put it against their first swimmer. I then change the other swimmers to zero. That enables me to compare their family's total fees against their single standing order. For example, for swimmer A, B & C Smith:

Total Fees Due    Total Fees Due (Adjusted)   Credit Amount   Difference
£86.50           £222.50                           £212.50           £10.00
£67.50           £0                                   £0.00                   £0.00
£58.50           £0                                   £0.00                   £0.00

The negative for me is that when I get my output it doesn't truly reflect the cost or payment for each individual swimmer, which is ideally what the treasurer wants. (The easy option would be to go back to the family and tell them to create individual SOs for each swimmer with either a distinguishing ref number or individual name in each SO - But I'm not able to do that!)

What should I do?

[Nomoreheroes]

OK, for vlookups or sumifs to work you have to know what to lookup or sum.

You say the payment refs are different - so I am assuming "Smith12345" and "Smith54321" say ?

Will the payments have the same refs next month ? What I am trying to find is if there are any constants with which we can work with

This is the sort of thing I am faced with

SMITH DL+F MARK+MARTIN FEE RP4673263496165300    15
SMITH RG&PV SCOTT&JAMES SMITH 61485245567775000N    35
SMITH RG&PV SCOTTJAMES SMITH 264551441220579000N    5

The first Smith is from one family. The second and third are from another family, who's total should have been £40 for the month.

The big long alphanumeric string changes with each transaction. The rest of the string can potentially be amended manually each month.

This doesn't happen often and I can live with checking the source data manually if the VLOOKUP and conditional formatting flags up a discrepancy - But its just annoying!

[@MacPhlea]

Next thing I am working on is:

I have some families that have more than one swimmer. Some pay with multiple standing orders while others pay with a single standing order.

The way I have got around this was by creating an extra column in my master page where (for the families that pay one standing order) I manually added up what their total should be and put it against their first swimmer. I then change the other swimmers to zero. That enables me to compare their family's total fees against their single standing order. For example, for swimmer A, B & C Smith:

Total Fees Due    Total Fees Due (Adjusted)   Credit Amount   Difference
£86.50           £222.50                           £212.50           £10.00
£67.50           £0                                   £0.00                   £0.00
£58.50           £0                                   £0.00                   £0.00

The negative for me is that when I get my output it doesn't truly reflect the cost or payment for each individual swimmer, which is ideally what the treasurer wants. (The easy option would be to go back to the family and tell them to create individual SOs for each swimmer with either a distinguishing ref number or individual name in each SO - But I'm not able to do that!)

What should I do?

This is where the DB would manage things a lot better... You'd have a master table of members and, hanging off of a single ID from that you have two related tables - one for the dues and one for the payments... You'd then know exactly how much each member owes, how much they pay (through each SO) and how much is outstanding...  

[@MacPhlea]

This is the sort of thing I am faced with

SMITH DL+F MARK+MARTIN FEE RP4673263496165300    15
SMITH RG&PV SCOTT&JAMES SMITH 61485245567775000N    35
SMITH RG&PV SCOTTJAMES SMITH 264551441220579000N    5

The first Smith is from one family. The second and third are from another family, who's total should have been £40 for the month.

The big long alphanumeric string changes with each transaction. The rest of the string can potentially be amended manually each month.

This doesn't happen often and I can live with checking the source data manually if the VLOOKUP and conditional formatting flags up a discrepancy - But its just annoying!

When you say it changes... There is normally a portion that identifies the direct debit itself and then a part that is the transaction number - are you able to compare a single members monthly payments to see if you can identify the breakdown of this?

[@MacPhlea]

Yeah, I know. But unfortunately I can just about understand the basics of Excel, but am really struggling to get started with Access. I'm sort of hoping that as my Excel sheet evolves, it will morph into a format that will form the basis of a database. (Or it may well be that the club will buy Hytek Business Manager which will make my spreadsheet redundant)

To be honest I think you will end up in a bigger tangle... Tell them it's too difficult and get them to splash the dosh...