Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comapring fields that have TXT and No's
Please can someone help me here. I have 3 columns. 1st column has Diagnosis Codes = 123.8 etc, but in no particular order. 2nd column has the complete diagnosis codes list, starting at one going on to 12490 fields. 3rd column relates to the text of the diagnosis code ie, if column 2 = 123.9 then column 3 would say Pregnancy Tests. What I need is to compare column 1 with column 2 so I can get the correct text from column 3. This will then tell me what the diagnosis text is for column 1. I know this is long winded but I need to know so I can keep my job. I have tried everything in my small head to get around this. -- Alisdair ------------------------------------------------------------------------ Alisdair's Profile: http://www.excelforum.com/member.php...o&userid=33354 View this thread: http://www.excelforum.com/showthread...hreadid=531863 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comapring fields that have TXT and No's
Hi Alisdair,
Asuming that the first column is A , secound column is B and thrid is C data starting in row 1 you could use Vlookup in column D to retrive the diagnosis text for column A =VLOOKUP(A1,$B$1:$C$12490,2) You can copy this down in column D starting at D1. The column D should match column A Regards, Bondi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comapring fields that have TXT and No's
Hi Alisdair, It sounds as if you need to use VLOOKUP so that when you enter your code it will search for that code and return the text associated with it. Excel help is quite good on this function. I think you need another column for the this i.e one column to enter your code, one to return the text and then two columns for the lookup data. Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=531863 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comapring fields that have TXT and No's
Thank you both, but I am still struggling. I did look up the help, but that has confused me as all i get is N/A in the cells. TRUE --- diag_code ------- diag_ldesc 465.9 1 Cholera due to Vibrio cholerae 625.3 1.1 Cholera due to Vibrio cholerae el tor 625.3 1.9 Unspecified cholera 789.09 2 Typhoid fever 789.09 2.1 Paratyphoid fever A 478.9 2.2 Paratyphoid fever B 478.9 2.3 Paratyphoid fever C 465.9 2.9 Unspecified paratyphoid fever 465.9 3 Salmonella gastroenteritis 847 3.1 Salmonella septicemia 847 3.2 Unspecified localized salmonella infection 847 3.21 Salmonella meningitis 723.1 3.22 Salmonella pneumonia 723.1 3.23 Salmonella arthritis 465.9 3.24 Salmonella osteomyelitis 465.9 3.29 Other localized salmonella infections 721.1 3.8 Other specified salmonella infections 721.1 3.9 Unspecified salmonella infection 721 4 Shigella dysenteriae 721 4.1 Shigella flexneri 721 4.2 Shigella boydii 721 4.3 Shigella sonnei 558.9 4.8 Other specified shigella infections 558.9 4.9 Unspecified shigellosis 558.9 5 Staphylococcal food poisoning 724.8 5.1 Botulism 724.2 5.2 Food poisoning due to Clostridium perfringens (C. welchii) 466 5.3 Food poisoning due to other Clostridia 466 5.4 Food poisoning due to Vibrio parahaemolyticus 463 5.81 Food poisoning due to Vibrio vulnificus 490 5.89 Other bacterial food poisoning 616.1 5.9 Unspecified food poisoning 616.1 6 Acute amebic dysentery without mention of abscess So here it is or part of it. The first column is the column I need to match with the text on the last column. Therefor I need to get the correct match with column 2 i.e 1 looks for the same number in 2 and then copies the text it relates to in column 3 into a new colum that sit along side column 1!!!!!! Jee whizz I am confusing myself. But that is it. Please can you have a look for me and tell me what I need to do. Thanks -- Alisdair ------------------------------------------------------------------------ Alisdair's Profile: http://www.excelforum.com/member.php...o&userid=33354 View this thread: http://www.excelforum.com/showthread...hreadid=531863 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comapring fields that have TXT and No's
OK lets see if you have got this straight The lookup table can be anywhere in your spread sheet. From a user viewpoint I imagine you want to enter a number in a cell and up pops your text description so it may be better to locate your table out of the way. In the lookup formula the first cell reference is the one you are entering the number in, the second is the range of the table i.e. First:Lastvalue. The third is the column number in the table - in this case 2. I would also suggest adding a fourth which is FALSE. All of these are separated by commas. When entering the data that you want to look up the description for, spaces are OK between the digits, BUT what you are entering must exactly match a number in the lookup table - the N/A error normally means it can't find the result. If getting spaces in the wrong place causes problems it might be worth editing the data and the table to get rid of them - of course if there is need to keep them that is another matter. If you continue to have problems, post back with a description of the problem as well as examples of what you have set up - you can always attach a ZIP file of your sheet. Hope this helps Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736 View this thread: http://www.excelforum.com/showthread...hreadid=531863 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|