Thetownend.com

I'm trying to get Excel to help me do some financial reconciliation, but have stumbled across a problem.

I'm using the following formula:

=VLOOKUP(B39,'NMH Data'!A$1:B$147,2)

The thing I am stuck on is that cell B39 only has part of the string that is in NMH Data. The formula only works if B39 is an exact match for a cell in NMH Data.

e.g At present, cell B39 has the following Data in it - No More Heroes 
Somewhere in 'NMH Data' between A1-B147 I have a cell that has - No More Heroes 10031524297940000R

So how can I ensure that 'No More Heroes 10031524297940000R' is found when I ask the formula to search for 'No More Heroes' ?

fuck me that's boring

Change the B39 to:

"*"&B39&"*"

Tested and works in Excel 2010.

Doesn't work in Excel 2007

Upgrade to Excel 2010.

Change the B39 to:

"*"&B39&"*"

;)

Sense of humour failure at this end!

Are you serious that it worked in Excel 2010 or was the whole string a wind up?

Yes. Yes it is. And this is something I am choosing to do in my free time!

You probably want to use some variation of MATCH instead of VLOOKUP, as MATCH accepts wildcards such as *.

It works. I tried it 5 minutes ago.

Actually you need to modify the formula to this:

=VLOOKUP("*"&B39&"*",Sheet2!A$1:B$147,2,FALSE)

If you use "True" instead of "False" it will lookup anything that is similar and not an exact match.

Just tried it with this:

B39&"*"

And it worked!

Thanks matey !!!!!

I initially tried that and it didn't work

Next question is:

I'm finding that when the string oin B39 is not present in 'NMH Data' it comes back and puts a random number into the cell. How do I stop it doing that?

Suggestions for stopping that happening;

A) Throw the machine out of a window
B) Throw yourself out of a window

Is all the data in the lookup range prefixed by "no more heroes"

I use excel for quite a lot of data manipluation and I find that I use functions such as, =left(b39,4), =right(b39,4), or mid(b39, 4) to pick certain parts of a string to lookup on.

As I am not sat in front of the data I am not sure what the issue is but using these may work.

I'll try to explain better.

I'm trying to check monthly swimming club fees.

The first sheet has a cell that has a standing order name. The second sheet is a csv I have downloaded from the bank. It has two columns. The first column is the standing order name and the second is the payment made. I therefore want to check what I think should be paid (from sheet 1) with what has been paid (in sheet 2)

I've found that the standing order name in sheet 2 sometimes has a string of numbers after it that can change monthly. The &"*" solution has sorted that for me.

However, the problem now is that the VLOOKUP goes crazy if the standing order name in the first sheet is not found in the second sheet. It just seems to put a random number into the cell. This screws me up because I then think that someone has actually paid that random number!

Does that make sense?

CONCATENATE is your freind

I am suprised that a vlookup returns random numbers if it can't find it - usually you get "#n/a".

In the standing order ref is the name at the front - such as "SMITH0000000121245463" ?

If so could you insert a column and use =left(cellref,5) this would give you "Smith" then use

=vlookup(b39, 'NMH Data'!B$1:C$147,2,false) - note the change from a:b to b:c and as long as "smith" was the data in B39 it would find it


Failing that email to me with some safe data and I'll give it a go. PM me

Thanks for the info about the =Left. Problem I have there is that some of the names are duplicated. The cell number plus &"*" is working fine.

As for the 'random number'. If I add FALSE to the end of the VLOOKUP it is now showing "#n/a" as you stated. Is there any way to get it to put '0' rather than "#n/a" ?

P.S. Thanks for the offer of looking, but its difficult for me to sanitise the data.

=IF(ISNA(VLOOKUP(B39,'NMH Data'!B$1:C$147,2,FALSE)),0,VLOOKUP(B39,'NMH Data'!B$1:C$147,2,FALSE))

Tried this suggestion. It seems to put 0 for everything.

Got it !!! I have to remove the second FALSE. So its:

=IF(ISNA(VLOOKUP(B39,'NMH Data'!B$1:C$147,2,FALSE)),0,VLOOKUP(B39,'NMH Data'!B$1:C$147,2))

(Just in case this is useful for others)

For ref you can also use :

=IF(ISERROR(VLOOKUP(B39,'NMH Data'!B$1:C$147,2,FALSE)),0,VLOOKUP(B39,'NMH Data'!B$1:C$147,2))


Glad you got there in the end.

You guys are real stars! I was dead chuffed with my spreadsheet before you helped me, but now its 100 time better !

Can I ask for some help with another column?

So, I have two columns. The first shows how much is owed, the second shows how much has been paid. I've create a 3rd column which calculates the difference between the two. So cell C1 has the formula =SUM(A1-B1)

If SUM(A1-B1) is 0 I want to display £0 but have the fill colour light green.
If SUM(A1-B1) is <0 I want to display £0 but have the fill colour bright green.
If SUM(A1-B1) is >0 I want to display £0 but have the fill colour light green.

How do I do that?

You need to use conditional formatting.


If you are in excel 2007 then on the home tab in should be just to the right of middle

I always find it  abit eratic so have a look online

http://www.dummies.com/how-to/content/applying-conditional-formatting-in-excel-2007.html

Found it! Brilliant! Many, many thanks! I'll have to go away and think about some more stuff I need to do to it now!!

No worries. I spend half my working life using it and you never stop learning and finidng out different ways to do different things.

Found another problem!

When I am comparing with the following:

=IF(ISNA(VLOOKUP(B39,'NMH Data'!B$1:C$147,2,FALSE)),0,VLOOKUP(B39,'NMH Data'!B$1:C$147,2))
 
I sometimes find that parents need to change their standing order. Its common for them to send a second payment. The result is that the name in B39 will appear twice in NMH Data. I may have one payment of £100 and another of £10. How do I adjust the formula to show the total as £110 ?

I don't think you can, best thing to use would be a sumif(....)

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!

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 ?

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

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.

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

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

^^^ this ^^^

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

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!

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)

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

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 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!

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...  

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?

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...

Have just checked a sample over the last 3 months. Transactions are either:
SO (Which don't have a long number but are relatively static - Although changes to standing order payments are frequent, which often results in the payer amending the SO name)
FPI (Faster payment incoming - Which have what appears to be a fairly random alphanumeric pattern. I can't detect anything that appears to be constant)

But in this scenario (either in Excel or Access) I only know that they have paid £212.50 out of a total of £222.50. I wouldn't know which one is £10 short. I'd have to arbitrarily choose which one owed £10.

If the alpha bit stays constant month on month then excel will do it for you. If everything changes then it won't. And I don't know enough about DBs to give an opinion, but I always thought you needed a link between the tables which is exactly what you are trying to do in excel.

It's the 'link' that gives the 'relation' in a relational database which, by simple deduction, implies you are better off using a database than a spreadsheet... However, NMH seems tied to excel so we see what we can do.

The other way to do it is to create a master worksheet for each customer with a unique ID then create a second worksheet/data set which links the SO name back to the master data with the amount paid - you could then sum all payments based on the master name without having to change the SO name to match.

this is what I was angling at on the previous page

I'm back by popular demand !!!

Can I ask for some more help please?

I now have all 'swimmers' aligned to a 'squad' and there are standard fees for each squad. There are 10 squads.

Problem 1:
I have a list of swimmers in column A. When I select the squad in column B, I want to automatically put the squad cost in column C

e.g.
swimmer1 squad1 £10
swimmer2 squad2 £20
swimmer3 squad3 £30 etc etc

What formula should I use?

Problem 2:
We give a discount for some swimmers. For most swimmers there is a 0% discount. For some there is 10% and others 15% (eg. For siblings). So, in column D I want to put a % discount. Ideally, I would like that discount applied to the number in column C

e.g.
swimmer1 squad1 £10 0%
swimmer2 squad1 £9 10%
swimmer3 squad1 £8.50 15%

How do I do that?

Thanks chaps!

Solve both in one go. You need to set up a table with the squad rates and then in the cell where you want the cost to be, use a VLOOKUP to find the rate and then multiply it by the discount factor.

Thanks Samdy. Have set up the table but am struggling with the syntax for the VLOOKUP. Can you help a bit more please?

I've got the following to do 'problem 1':

=IF(ISNA(VLOOKUP(B1,'Squad Fees'!A$2:B$147,2,FALSE)),0, VLOOKUP(B1,'Squad Fees'!A$2:B$147,2))

Does the TEF do your job for you?

No it doesn't....But I'm hoping it will help me in this voluntary work helping a non profit making organisation that gets kids off the street and helps them take part in sport.

Sounds like a waste of time to me.

I you have a sheet with 4 columns (Swimmer, squad, discount & cost) and then set up a lookup table with (Squad 1, squad 2, etc with associate costs in the next column. Then you could use the following calculation. This requires you to manually type in the discount.

=VLOOKUP(B2,Sheet2!A1:B3,2,TRUE)-SUM(VLOOKUP(B2,Sheet2!A1:B3,2,TRUE)*C2)

I can send to you if it helps.

Squad fees would be just as easy to sort out with a nested IF function:

=IF(B2="Squad 1",10,IF(B2="Squad 2",20,IF(B2="Squad 3",30)))

If you have a discount column in column D (with a discount % [format the column as percentages for easier input]) then you could modify the above with:

=IF(B2="Squad 1",10*(1-D2),IF(B2="Squad 2",20*(1-D2),IF(B2="Squad 3",30*(1-D2))))


The only problem is if you get over 7 squads as the nested IF will only allow 7 arguments I think.

Would be more robust if you could only select the squad by a drop down menu. If you don't know how to do this then google it as it's quite simple to achieve.

Further investigation suggests you can have 64 nested IF arguments in a single string in Excel 2007 and later.

Why fuck about with nested IF statements when VLOOKUP would be so much neater and easier.
 
With 10 squads, I wouldn't want to be troubleshooting that nested IF when it stops working!

I assume he understands IF functions and clearly doesn't understand VLOOKUPS.

Really needs to get a book on Excel.

This thread takes me right back to the heady days of the mid 1980s and writing programmes in BASIC for my ZX Spectrum.

Thanks for all of your help thus far.

I considered using 'IF' functions, but thought the string would get very long with potential for 20-25 squads.

I'm not great with VLOOKUP, especially when combined with ISNA and other functions, which I also need.

My state of play at the moment is that I have used:

=IF(ISNA(VLOOKUP(B1,'Squad Fees'!A$1:B$147,2,FALSE)),0, VLOOKUP(B1,'Squad Fees'!A$1:B$147,2))

This checks my look up table and puts a numerical value in column C

I manually enter a percentage in column D and then have the following in column E:
=SUM(C1-(C1*D1))

This takes a percentage value from column D and calculates the discount which it places in column E. 

I cant work out how to combine both the long VLOOKUP and SUM together though - But then I'm no Excel expert!

Do you need the ISNA? Are the Squad fees ever blank? If I'm reading that formula right, you've got an IF statement that's returning the same results for both the 'then' and 'else' parts, just putting a 0 if the cell in Squad fees is blank.

If you want to do the lookup and sum in one go:

=VLOOKUP(B1,'Squad Fees'!A$1:B$147,2,FALSE)*(1-D1)

Once again thanks for this.

I was trying to overcomplicate things.

Another plea for help.

Club has just made wholesale changes to squad structures and fees. When reconciling using the excel skills that you have taught me I find that there are over 100 people that I need to email and tell them something simple like:

'Your new fees are £x but you have only paid £y. Please arrange for the remaining £z to be paid within 7 days.'

I'm really tired after a nightshift and before I waste my energy in researching, can someone tell me if it is possible to get Excel to automatically create 100 (or so emails) with a message along these lines? (I have X, Y & Z in columns in my spreadsheet and can create another reference sheet with everyone's emails. I also already use Outlook).

If it is possible, can someone help steer in in the right direction please?

Thanks

http://office.microsoft.com/en-001/word-help/use-mail-merge-to-send-personalized-e-mail-messages-to-your-e-mail-address-list-HA010109162.aspx

Thanks. So does that mean I need to transfer from Excel to Word first? I was hoping that I could put in a script that read the value of a cell and then automatically created the email.

No, you create the Mail Merge in Outlook and select the Excel spreadsheet as the source file.