VLOOKUP WITH ALPHA NUMERIC

[JACOB]

i HAVE ALOOK UP RANGE THAT HAS IN THE 1ST COLUMN NUMBERS & OR NUMBERS &
LETTERS THE vLOOK UP ONLY GIVES ME THE NUMBERS IT GIVE n?A ON THE ALPHA OR
ALPHA NUMERIC
I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS

 

[arno]

Hi Jacob,

stop SHOUTING at people, be friendy.

I HAVE THEM FORMATTED AS GENERAL IN BOTH LOCATIONS

well, you must have the same format on both sides. you can check that
with simple formulas. eg. if a1 and b1 should be the same, then write
into c1 the formula

=a1=b1

if the result is "FALSE" then you can edit the formula in c1, select
c1, press F2, higlight "a1" with the mouse, press F9 which will show
you the value it uses, highlight "b1" with the mouse and press F9, then
you shoud see a difference. Eg.

="22"=22 ==> text 22 and number 22 do not match
="33"="33 " ==> some databaseprogramm fill fields with blanks,
use trim-functions
etc. etc.

try this an post your results (contents of formulas showing "false")
then we can fix your problem.

arno

 

[KL]

Hi Jacob,

Try doing something like this:

=VLOOKUP("*"&A1&"*",TEXT(B1:G100,"0"),2,0)

This is an ARRAY formula (Ctrl+Shift+Enter)

Regards,
KL

 

[JACOB]

Sorry I do most of my tables in caps

I tried and it came back as "FALSE"
I formatted the cell as TEXT on Both (I used only one cell to test)
and it still came back "FALSE"
Jacob

 

[arno]

Sorry I do most of my tables in caps
no worries, but this increases your chances of getting an answer cause
caps is considered as unfriendly, netiquette bla bla.

I tried and it came back as "FALSE"
I formatted the cell as TEXT on Both (I used only one cell to test)
and it still came back "FALSE"

this is why you should _post_ this

="22"=22

stuff!

arno

 

[KL]

....or instead of converting all your data you can try and use my solution
posted below )

Regards,
KL

 

[arno]

Hi KL,

...or instead of converting all your data you can try and use my
solution posted below )

I do not agree. I always set the datatypes right once, then I never
have to think about it again. Pls. note that the data can be a
datasource for other databases etc. etc. Then you always have to manage
the problem within the formulas. The best is always to set everything
as early as possible - eg. in the sql-statement you use to query the
data which solves the problem even before the data arrives in excel.
This problem arises very very often so I solve it where it starts.

BTW your formula is _wrong_ because if you search for 1a you will find
it in
1a
xxxx1a
1axxxx
etc
etc.

arno

 

[KL]

Hi arno,

"arno" wrote in message:

I do not agree. I always set the datatypes right once, then I never
have to think about it again. Pls. note that the data can be a
datasource for other databases etc. etc.

Just like the world is not "black and white" there could be different
situatiations - so the best solution will depend on that. Sometimes it makes
sense fixing the data especially when it is going to be used the way you
described, however sometimes it is easier and more practical to do it via
formula e.g. in a situation where you have no control over the data format,
or it is a one-off exercise, or the data may need to be used in exactly the
format they have been entered: the numbers as numbres and the text as text,
etc. What makes you think that we are currently dealing with a dataset for
further use with sql statement? I just offered a quick alternative solution.

BTW your formula is _wrong_ because if you search for 1a you will find...

Yep, thank for this I just copied it from another solution I did and forgot
to remove the asteriscs. So the final formula could be:

=VLOOKUP(""&A1&"",TEXT(B1:G100,"0"),2,0)

Regards,
KL

 

[arno]

Hi KL,

Just like the world is not ...

the problem behind is that too many people are importing/exporting and
transferring data nowadays because it is so "easy" with office
applications. They do not care about anything (you know, the boring
stuff like datatypes and field descriptions) but this is neccessary to
avoid errors - and these errors start eg. in excel when the vlookup
does not work, then complicated formulas are used to fix the problem -
problems that would never come up if you deal with the boring stuff
first.

Your solution is working, for you. Do you think someone who cannot deal
with the boring stuff eg. will be able to find lost brackets {} of your
matrix formula (when you edit the formula an leave it without
ctrl+shift+enter)? Do you think someone else has a chance to really
understand what you did - why did you use ""& in the first part of the
formula, why text() in the second part?

ok, it's working, but it is complicated, hard to understand, not very
common and therefore a good base for any kind of errors. My message is
to solve the problem where it starts.

use with sql statement?

I meant the datatable that is used for the vlookup may be created with
some sql-query.

arno

 

[VG]

Arno, HL or anyone else who can help - I don't quite understand your
solutions, maybe you can help me by explaining a little more - I have this
problem too and it is driving me crazy. I have an extract from our
accounting system of several thousand lines of accounting codes - but some
are pure numbers and others are alpha-numeric, for example 6103, 6103A,
6103B, etc. I have no control over what format these come into Excel as.
All of the numerics sort separately from the alpha-numerics, so my vlookup
formula won't work. The microsoft web site says that to sort mixed data it
needs to all be formatted as text using the Format-Cells menu, the Numbers
tab, click Text, THEN RETYPE THE VALUE IN THE CELL (YES, I KNOW, I'M
SHOUTING!!). They must be kidding! I have thousands of lines and can't
RETYPE all of those cell values. Is there any way to easily get the entire
column of numbers into text format after the fact AND have the vlookup
formulas work without retyping? Microsoft website says format as text first
when typing in new data, but that doesn't help when I have an existing
extract. Help???? Thanks!

 

[arno]

Hi VG,

pls. explain how you get your data into excel, what is the database,
what is the file you get, how do you import into excel. the best is to
solve the problem already here at this stage.

arno

 

[arno]

Hi again VG,

some are pure numbers and others are alpha-numeric, for

no, they are all alpha-numeric, just excel treats them as it likes.

I have no control over what format
these come into Excel as.

maybe you have - in the exportfunction of your database or in the
import to excel.

All of the numerics sort separately from
the alpha-numerics, so my vlookup formula won't work.

this is the problem. there are workarounds for this - in the vlookup
formula like KL suggested or you correct the data eg. with a formula.
Eg. if you have your alphanumeric data in column A you could use this
formula in column B (and copy down to the end): ="'"&A1 this will make
'123 (which is a text, the '-character is invisible but defines the
content as text) out of 123 (which is a number). Then you could copy
column B and PasteSpecial/VALUES to column A - this will overwrite your
mixed numbers/text with only texts from col B. You could have macros
doing this for you.

THEN
RETYPE THE VALUE IN THE CELL

no way

arno

 

[VG]

Arno - it turns out one of your first suggestions saved me - I did the =a1=b1
check (such a simple thing, but I never knew of it before!!), and found out
that my two sets of alpha-numeric data were actually different, so the
vlookups couldn't match. In one set, which comes from an extract to a .csv
file that is comma delimited (written by our programmers), the code was
actually "6103D". In my vlookup table (which came from another extract of
codes from our "vanilla" accounting system, i.e. we didn't program it), the
code was "6103D " - with a space at the end. This is because the length of
the field is 6 characters, and this code is only 5 characters - well it turns
out all of the less than 6 digit alpha-numeric codes had spaces - so I had to
manually go into my vlookup table and delete all the spaces (yuck) but at
least it worked and only took a few minutes. Once this was fixed it turns
out the alpha-numeric and numeric codes work just fine in the vlookup - the
numerics sort first and then the alpha-numerics, and the forumula has no
trouble finding either and bringing back the right data. So now I'm not sure
what the big deal is about mixed data -- but anyway, thanks for your help!!
(You're right though, I think I do have control over the .csv comma delimited
extracts in how the data comes in - I just never remember till later when I'm
having the problem! Not sure about the vanilla extracts - I'll have to
notice next time.) VG

 

[arno]

Hi,

now that you know that your vanilla fills up fields with blanks and
that the size is 6 characters you know what to do in your vlookup: fill
up your match kriteria (in a1) with blanks like:

=vlookup(left(a1&" ", 6), table, column, false)

(there's a repeat function that could repeat a " " 6 times, I do not
recall the name in english right now...)

you do not need to remove the blanks from your csv-file, so don't do
it, leave your data what it is.

arno

ps. knowing the field descriptions of tables make the difference

 

[VG]

Thanks! I'll try it next time. VG