Thanks!
That actually did the trick!
Johnny
Den 2006-05-11 10:41:17 skrev Barrie Backhurst
<[EMAIL PROTECTED]>:
On Wed, 2006-05-10 at 20:56 +0200, Johnny Andersson wrote:
Den 2006-05-10 15:44:54 skrev Andy Pepperdine
<[EMAIL PROTECTED]>:
> Commented inserted inline.
>
> On Wednesday 10 May 2006 13:55, Johnny Andersson wrote:
>> Hi!
>>
>> I tried to, just for fun, make a simple spreadsheet to solve a very
>> simple
>> "puzzle", in this case the very famous SEVEN+SEVEN+SIX=TWENTY.
>>
>> I did this the following way:
>>
>> Column B and C, rows 5-13:
>> E 8
>> I 5
>> N 2
>> S 6
>> T 1
>> V 7
>> W 3
>> X 0
>> Y 4
>>
>> Range E5:J8:
>> S E V E N
>> S E V E N
>> + S I X
>> ----------------
>> T W E N T Y
>>
>> Range L5:Q8:
>> Formulas converting letters in E5:J8 to their corresponding numbers
in
>> B5:C13.
>> Example: E9 (corresponding to the "W" in "TWENTY") looks like this:
>>
>> =LETARAD(E9;$B$5:$C$13;2;1)
>>
>> This is Swedish. In English I guess it would look like this:
>> =VLOOKUP(E9;$B$5:$C$13;2;1)
>>
>> S5:S8:
>> Converts the single character numbers of a row to a multi character
>> number.
>> Example: S5 looks like this:
>> =Q5+10*P5+100*O5+1000*N5+10000*M5
>>
>> S9 sums S5:S7.
>>
>> S10:
>> =OM(S8=S9;"Jippie!";"Attans också!")
>>
>> Translated to English:
>> =IF(S8=S9;"Jippie!";"Shit, man!")
>>
>> So if S8=S9, then the problem is solved.
>>
>> Now to the question or problem:
>> It seems like VLOOKUP looks at V and W as the same character! As you
can
>> see, in the table above, V=7, W=3, but E9 still displays 7, not 3 (E9
>> looks for the W in B5:C13 but it displays the result of the V row
>> instead)!
>
> I thought that in Swedish V and W compare equal for the purposes of
> sorting
> names. So it appears the lookup is done using the locale's collating
> rules.
>
So if I switch to another language, this problem does not occur?
>> Is this a bug or is it supposed to work this way?
>> In this case I just replaced the formula with "=C11", but sometimes
it
>> might not be possible to find a workaround to the problem.
>>
>> I realize that in some cases I might want to consider V and W to be
the
>> same character, but sometimes I'd rather not. How about making this
>> possible to set by the user by adding another parameter to the
VLOOKUP
>> function? Or is there another function that would better suit my
needs
>> in
>> this particular case?
>
Hi Johnny
I think you need to change the sorted parameter to false, within the
VLOOKUP function.
Barrie
--
Johnny
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
