Re: [libreoffice-users] vlookup case
Wow! Thanks i'll look into that and maybe try to adapt it. The strings are of variable length from 1 to 3 characters. What i came up with (bypassing VLOOKUP altogether) was =CONCATENATE (MID ($J$2, FIND(LEFT (A2,1), $J $3),1),IFERROR(MID($J$2, FIND (MID (A2,2,1), $J $3),1," "),IFERROR (MID ($J $2,FIND (MID (A2,3,1), $J $3,1),1," ")) (I think); which takes each character from the string in A2, gets the position of that character in the string held in J2, then extracts the corresponding character from the string held in J3 and concatenates it to the result. In the case of the string being less than 3 characters, MID () will return an error which will result in " " concatenated instead. Null string would have been better but in my case space works just as well. Its very cumbersome (and more so because in my actual file the strings J2 and J3 are in fact located on a different sheet); but it does what i need and thats all i demand of it! Best Gary On Fri, 10/3/17, Michael D. Setzer II <mi...@kuentos.guam.net> wrote: Subject: Re: [libreoffice-users] vlookup case To: "Gary Collins" <gcatl...@yahoo.co.uk>, users@global.libreoffice.org, "Brian Barker" <b.m.bar...@btinternet.com> Date: Friday, 10 March, 2017, 11:25 I was doing some testing with single characters, but then saw it was three characters. Originally, just used the code of the character, changed code to ascii, but with 3 multiple each value to get a number that matched. In both put the row number in next column. Put the match field in C15 both the formulas work, both formulas give the row. abc 1 97098099 1 aBc 2 97066099 2 aBC 3 97066067 3 Abc 4 65098099 4 AbC 5 65098067 5 Abc 6 65098099 6 Ab 7 65098000 7 aCb 8 97067098 8 AcB 9 65099066 9 aBC 10 97066067 10 AAC 11 65065067 11 ss 12 115115000 12 aBC 97066067 3 “=VLOOKUP($C$15,C1:D12,2,0) 3 “{=INDEX(B1:B12,MATCH(1,EXACT($C$15,A1:A12),0))} Formula in C “=CODE(MID(A15,1,1))*100+CODE(MID(A15,2,1))*1000+CODE(MID(A15,3,1)) On 10 Mar 2017 at 9:46, Gary Collins wrote: Date sent: Fri, 10 Mar
Re: [libreoffice-users] vlookup case
I was doing some testing with single characters, but then saw it was three characters. Originally, just used the code of the character, changed code to ascii, but with 3 multiple each value to get a number that matched. In both put the row number in next column. Put the match field in C15 both the formulas work, both formulas give the row. abc 1 97098099 1 aBc 2 97066099 2 aBC 3 97066067 3 Abc 4 65098099 4 AbC 5 65098067 5 Abc 6 65098099 6 Ab 7 65098000 7 aCb 8 97067098 8 AcB 9 65099066 9 aBC 10 97066067 10 AAC 11 65065067 11 ss 12 115115000 12 aBC 97066067 3 “=VLOOKUP($C$15,C1:D12,2,0) 3 “{=INDEX(B1:B12,MATCH(1,EXACT($C$15,A1:A12),0))} Formula in C “=CODE(MID(A15,1,1))*100+CODE(MID(A15,2,1))*1000+CODE(MID(A15,3,1)) On 10 Mar 2017 at 9:46, Gary Collins wrote: Date sent: Fri, 10 Mar 2017 09:46:22 + (UTC) From: Gary Collins <gcatl...@yahoo.co.uk> Send reply to: Gary Collins <gcatl...@yahoo.co.uk> To: <users@global.libreoffice.org>, Brian Barker <b.m.bar...@btinternet.com> Copies to: Gary Collins <gcatl...@yahoo.co.uk> Subject: Re: [libreoffice-users] vlookup case > Thanks for very helpful reply. It does seem to be a big drawback with these > functions. I'll certainly try it but ive found a workaround that im using at > the mo: ive defined two strings in adjacent cells and using string functions > to find the position of a character in one string then extract the > corresponding character from the other. Much less elegant and it was quite > tiresome to implement but ive at least got it to work, in the limited cases > that im using it so far. If i extend it to transliterate strings of arbitrary > length (for now the max length ive needed is 3 chars) then i guess i'll > probably have to get into macros. > Thanks > G. > > On Thu, 9/3/17, Brian Barker <b.m.bar...@btinternet.com> wrote: > > Subject: Re: [libreoffice-users] vlookup case > To: users@global.libreoffice.org > Cc: "Gary Collins" <gcatl...@yahoo.co.uk> > Date: Thursday, 9 March, 2017, 23:17 > > At 11:50 09/03/2017 +, Gary > Collins wrote: > >Is it possible to make the search performed by vlookup > to be case sensit
Re: [libreoffice-users] vlookup case
Thanks for very helpful reply. It does seem to be a big drawback with these functions. I'll certainly try it but ive found a workaround that im using at the mo: ive defined two strings in adjacent cells and using string functions to find the position of a character in one string then extract the corresponding character from the other. Much less elegant and it was quite tiresome to implement but ive at least got it to work, in the limited cases that im using it so far. If i extend it to transliterate strings of arbitrary length (for now the max length ive needed is 3 chars) then i guess i'll probably have to get into macros. Thanks G. On Thu, 9/3/17, Brian Barker <b.m.bar...@btinternet.com> wrote: Subject: Re: [libreoffice-users] vlookup case To: users@global.libreoffice.org Cc: "Gary Collins" <gcatl...@yahoo.co.uk> Date: Thursday, 9 March, 2017, 23:17 At 11:50 09/03/2017 +, Gary Collins wrote: >Is it possible to make the search performed by vlookup to be case sensitive? Apparently not! >I need to be able to distinguish between eg 'd' and 'D' but at the >moment i cant work out how to do it (if it's possible) Suppose your array has the values to be searched in column A and the values to be returned in column B. Then try: =INDEX(B1:Bn;MATCH(1;EXACT("text";A1:An);0)) Note that this is an array formula, so when you have entered it you must complete the process by pressing Ctrl+Shift+Enter. If you do this successfully, the entire formula will appear in the Input line surrounded by braces, but you cannot simply type these braces yourself. This relies on EXACT() being the one function that *is* case-sensitive. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] vlookup case
At 11:50 09/03/2017 +, Gary Collins wrote: Is it possible to make the search performed by vlookup to be case sensitive? Apparently not! I need to be able to distinguish between eg 'd' and 'D' but at the moment i cant work out how to do it (if it's possible) Suppose your array has the values to be searched in column A and the values to be returned in column B. Then try: =INDEX(B1:Bn;MATCH(1;EXACT("text";A1:An);0)) Note that this is an array formula, so when you have entered it you must complete the process by pressing Ctrl+Shift+Enter. If you do this successfully, the entire formula will appear in the Input line surrounded by braces, but you cannot simply type these braces yourself. This relies on EXACT() being the one function that *is* case-sensitive. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] vlookup case
Hello, Is it possible to make the search performed by vlookup to be case sensitive? I need to be able to distinguish between eg 'd' and 'D' but at the moment i cant work out how to do it (if its possible) Thanks Gary -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted