Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP WITH ALPHA NUMERIC
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote: 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
....or instead of converting all your data you can try and use my solution
posted below :-)) Regards, KL "arno" wrote in message ... 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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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! |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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" wrote: 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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
Thanks! I'll try it next time. VG
"arno" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) | |||
Convert Alpha to Numeric | Excel Discussion (Misc queries) | |||
Summing part of an Alpha Numeric String | Excel Worksheet Functions | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |