convert column values

[charles]

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

 

[Harlan Grove]

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.

 

[Toppers]

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,false)),"Invalid
branch",vlookup(branchcode,Sheet2!A2:B500,2,false))

HTH

 

[charles]

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

 

[HB Designs]

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]

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.