Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
I'm sorry if this is already here somewhere, but I could't find any references.
I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? I'm sorry if this is a basic question. I only starte using Excel a little while ago and am learning as I'm going. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
charles wrote...
.... . . . For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? . . . .... Use VLOOKUP. You'd need to create a cross-reference table of branch codes consisting of two columns, the first containing the branch codes used in your spreadsheet and the second containing the corresponding branch codes used in the target system. I'll assume that table is in X1:Y200. Easier if you insert a column in your list between other fields and the branch code field. Then for each branch code in your list (I'll assume the topmost one is in cell A5), use a formula like the following in the inserted column. =VLOOKUP(A5,$X$1:$Y$200,2,0) Fill this formula so that there's one such formula for each record in your list, then export the range containing the other fields and this column of formulas to the target system. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
Charles,
VLOOKUP function is one way, with table of branch codes and system codes. In a spare column put: =vlookup(branchcode,Sheet2!A2:B500,2,false) and copy down. When succesful, you can replace the branch codes with system codes as required. where branchcode = cell containing Branch code A2:B500 is a table of branch codes(A) and system codes(B) on (for example) Sheet2 The following produces an error if there is no branch match: =If(iserror(vlookup(branchcode,Sheet2!A2:B500,2,fa lse)),"Invalid branch",vlookup(branchcode,Sheet2!A2:B500,2,false) ) HTH "charles" wrote: I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? I'm sorry if this is a basic question. I only starte using Excel a little while ago and am learning as I'm going. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
Thanks Toppers
I tried that, but the problem I have is that the ID that need to be replaced will occure more than once. I've got an employee list of about 20000 folks so I could have 2000 or so working at one location and I need to make sure that when I do this converstion that all 2000 get their ID number translated. I tried what you said below and anytime there was a duplicate occurance of a location code, the translation came back with #NA??? Thanks again, Charles "Toppers" wrote: Charles, VLOOKUP function is one way, with table of branch codes and system codes. In a spare column put: =vlookup(branchcode,Sheet2!A2:B500,2,false) and copy down. When succesful, you can replace the branch codes with system codes as required. where branchcode = cell containing Branch code A2:B500 is a table of branch codes(A) and system codes(B) on (for example) Sheet2 The following produces an error if there is no branch match: =If(iserror(vlookup(branchcode,Sheet2!A2:B500,2,fa lse)),"Invalid branch",vlookup(branchcode,Sheet2!A2:B500,2,false) ) HTH "charles" wrote: I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? I'm sorry if this is a basic question. I only starte using Excel a little while ago and am learning as I'm going. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
Charles, don't forget to 'paste values' once you've done the VLOOKUP function
before you delete your orignal data. If you delete the column that the system doesn't recognize before you've pasted values, all of your VLOOKUP results will turn to errors. "Toppers" wrote: Charles, VLOOKUP function is one way, with table of branch codes and system codes. In a spare column put: =vlookup(branchcode,Sheet2!A2:B500,2,false) and copy down. When succesful, you can replace the branch codes with system codes as required. where branchcode = cell containing Branch code A2:B500 is a table of branch codes(A) and system codes(B) on (for example) Sheet2 The following produces an error if there is no branch match: =If(iserror(vlookup(branchcode,Sheet2!A2:B500,2,fa lse)),"Invalid branch",vlookup(branchcode,Sheet2!A2:B500,2,false) ) HTH "charles" wrote: I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? I'm sorry if this is a basic question. I only starte using Excel a little while ago and am learning as I'm going. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert column values
My error: VLOOKUP table should be absolute addresses e.g $A$2:$B$500.
This is why got your (my!) error condition. Also see Harlan's note. "charles" wrote: Thanks Toppers I tried that, but the problem I have is that the ID that need to be replaced will occure more than once. I've got an employee list of about 20000 folks so I could have 2000 or so working at one location and I need to make sure that when I do this converstion that all 2000 get their ID number translated. I tried what you said below and anytime there was a duplicate occurance of a location code, the translation came back with #NA??? Thanks again, Charles "Toppers" wrote: Charles, VLOOKUP function is one way, with table of branch codes and system codes. In a spare column put: =vlookup(branchcode,Sheet2!A2:B500,2,false) and copy down. When succesful, you can replace the branch codes with system codes as required. where branchcode = cell containing Branch code A2:B500 is a table of branch codes(A) and system codes(B) on (for example) Sheet2 The following produces an error if there is no branch match: =If(iserror(vlookup(branchcode,Sheet2!A2:B500,2,fa lse)),"Invalid branch",vlookup(branchcode,Sheet2!A2:B500,2,false) ) HTH "charles" wrote: I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next to the employees to the code I have currently in our system. Is there a way in Excel to "translate" the existing (non-system recognized) codes into the ones that they euate to in the system? I'm sorry if this is a basic question. I only starte using Excel a little while ago and am learning as I'm going. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
To copy values in a column relevant to text in an adjacent column? | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
How do you convert numbers as "text" to values for a long column . | Excel Discussion (Misc queries) |