Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #15 on: Friday, June 14, 2013, 15:33:59 » |
|
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.
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #16 on: Friday, June 14, 2013, 15:47:07 » |
|
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?
|
|
« Last Edit: Friday, June 14, 2013, 15:51:08 by Nomoreheroes »
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
4D
Or not 4D that is the question
Offline
Posts: 22082
I can't bear it 🙄
|
|
« Reply #17 on: Friday, June 14, 2013, 15:52:32 » |
|
CONCATENATE is your freind
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #18 on: Friday, June 14, 2013, 16:06:05 » |
|
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
|
|
« Last Edit: Friday, June 14, 2013, 16:09:13 by Bewster »
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #19 on: Friday, June 14, 2013, 17:18:12 » |
|
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.
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Samdy Gray
Dirty sneaky traitor weasel
Offline
Posts: 27137
|
|
« Reply #20 on: Friday, June 14, 2013, 17:36:02 » |
|
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))
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #21 on: Friday, June 14, 2013, 17:59:28 » |
|
=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.
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #22 on: Friday, June 14, 2013, 18:15:34 » |
|
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)
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #23 on: Friday, June 14, 2013, 18:20:06 » |
|
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.
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #24 on: Friday, June 14, 2013, 18:53:21 » |
|
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?
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #26 on: Friday, June 14, 2013, 19:01:16 » |
|
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!!
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #27 on: Friday, June 14, 2013, 19:09:44 » |
|
No worries. I spend half my working life using it and you never stop learning and finidng out different ways to do different things.
|
|
|
Logged
|
|
|
|
Nomoreheroes
The Moral Majority
Offline
Posts: 14694
|
|
« Reply #28 on: Friday, June 14, 2013, 19:13:02 » |
|
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 ?
|
|
|
Logged
|
You're my incurable malady. I miss the pleasure of your company.
|
|
|
STFC_Manc
Offline
Posts: 1543
|
|
« Reply #29 on: Friday, June 14, 2013, 19:55:07 » |
|
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(....)
|
|
|
Logged
|
|
|
|
|