If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
The colums changed from alpha to numeric how do you make it alpha | worldmade | General Discussion | 2 | May 26th, 2005 03:44 PM |
How do I change Outlook date format from numeric to alpha? | Charles E. Greene II | General Discussion | 1 | May 22nd, 2005 10:37 PM |
Alpha & Numeric Counts in Excel | Programmer wanna be | General Discussion | 3 | April 5th, 2005 11:12 AM |
If Statement based on Alpha or Numeric | Ryan | General Discussion | 9 | September 23rd, 2004 01:55 PM |