Excel Geeks

[Bewster]

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.

[Nomoreheroes]

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?

[4D]

CONCATENATE is your freind

[Bewster]

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

[Nomoreheroes]

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.

[Samdy Gray]

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" ?

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

[Nomoreheroes]

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

[Nomoreheroes]

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)

[Bewster]

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.

[Nomoreheroes]

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?

[Bewster]

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

[Nomoreheroes]

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

[Bewster]

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

[Nomoreheroes]

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 ?

[STFC_Manc]

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