column value translation

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

0
Charles (140)
6/15/2006 4:00:01 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
443 Views

Similar Articles

[PageSpeed] 2

Look at VLOOKUP in help, and post back if you get stuck.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"charles" <charles@discussions.microsoft.com> wrote in message
news:30F7254D-F9A4-42A7-A747-8F9CA914CB54@microsoft.com...
> 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
>


0
6/15/2006 4:43:10 PM
Thanks Tim, 
I've got one spread sheet with about 80 locations listed on it. This list is 
in two columns, new ID and old ID. then I have the list of employees on 
another spread sheet and next to them is the old ID. I need to come up with a 
new list of employees that shows them with the correct new ID next to them.

"Tim M" wrote:

> Well it depends more on the number of codes not the number of employees as 
> you would not need to do a find and replace for each employee, just for each 
> branch code. I assume you have alot of different branch codes as well?  (do 
> you have these codes in a common spreadsheet now?  As in a column of the old 
> codes next to the corresponding column of new codes?)
> 
> "charles" wrote:
> 
> > You're right it would depend on that, and we're looking at about 20,000 
> > different employees, so find and replace would take me forever...anything 
> > else?
> > 
> > "Tim M" wrote:
> > 
> > > What method you chose probably depends on just how many of these codes you 
> > > have to do.  you could use find and replace to do this.
> > > 
> > > 'Edit'....'find'...type the code you need to find, then click on the 
> > > 'replace' tab and tell it what to replace it with.
> > > 
> > > "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
> > > > 
0
Charles (140)
6/15/2006 5:22:49 PM
Well it depends more on the number of codes not the number of employees as 
you would not need to do a find and replace for each employee, just for each 
branch code. I assume you have alot of different branch codes as well?  (do 
you have these codes in a common spreadsheet now?  As in a column of the old 
codes next to the corresponding column of new codes?)

"charles" wrote:

> You're right it would depend on that, and we're looking at about 20,000 
> different employees, so find and replace would take me forever...anything 
> else?
> 
> "Tim M" wrote:
> 
> > What method you chose probably depends on just how many of these codes you 
> > have to do.  you could use find and replace to do this.
> > 
> > 'Edit'....'find'...type the code you need to find, then click on the 
> > 'replace' tab and tell it what to replace it with.
> > 
> > "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
> > > 
0
timm (30)
6/15/2006 5:22:52 PM
You're right it would depend on that, and we're looking at about 20,000 
different employees, so find and replace would take me forever...anything 
else?

"Tim M" wrote:

> What method you chose probably depends on just how many of these codes you 
> have to do.  you could use find and replace to do this.
> 
> 'Edit'....'find'...type the code you need to find, then click on the 
> 'replace' tab and tell it what to replace it with.
> 
> "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
> > 
0
Charles (140)
6/15/2006 5:23:15 PM
What method you chose probably depends on just how many of these codes you 
have to do.  you could use find and replace to do this.

'Edit'....'find'...type the code you need to find, then click on the 
'replace' tab and tell it what to replace it with.

"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
> 
0
timm (30)
6/15/2006 5:23:52 PM
I tried that, but it didn't work right. I have the one spread sheet with the 
old and new branch ID's and then I have the spread sheet of employees with 
the old ID's. when I did the VLOOKUP anytime an employee had an old ID that 
had occured on the list "up above" it came back with a value of #NA. I've got 
about 20K people on this list and there will be a lot on there that share the 
same branch code

"Bob Phillips" wrote:

> Look at VLOOKUP in help, and post back if you get stuck.
> 
> --
> 
> HTH
> 
> Bob Phillips
> 
> (replace xxxx in the email address with gmail if mailing direct)
> 
> "charles" <charles@discussions.microsoft.com> wrote in message
> news:30F7254D-F9A4-42A7-A747-8F9CA914CB54@microsoft.com...
> > 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
> >
> 
> 
> 
0
Charles (140)
6/15/2006 5:40:02 PM
Tim,
I used your formula ( =VLOOKUP(C1,$A$1:$B$1300,2,FALSE)  ) 
I found in a differet post about Find and Replace and it worked!!! Thanks 

"Tim M" wrote:

> Well it depends more on the number of codes not the number of employees as 
> you would not need to do a find and replace for each employee, just for each 
> branch code. I assume you have alot of different branch codes as well?  (do 
> you have these codes in a common spreadsheet now?  As in a column of the old 
> codes next to the corresponding column of new codes?)
> 
> "charles" wrote:
> 
> > You're right it would depend on that, and we're looking at about 20,000 
> > different employees, so find and replace would take me forever...anything 
> > else?
> > 
> > "Tim M" wrote:
> > 
> > > What method you chose probably depends on just how many of these codes you 
> > > have to do.  you could use find and replace to do this.
> > > 
> > > 'Edit'....'find'...type the code you need to find, then click on the 
> > > 'replace' tab and tell it what to replace it with.
> > > 
> > > "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
> > > > 
0
Charles (140)
6/15/2006 8:12:02 PM
Reply:

Similar Artilces:

columns in RMSSO
Hi There, Is it possible to change the columns on the forms ?? For example: Receiving po contains: Type, Item Lookup Code, Reorder Number, Description, OtyOrd, QtyRTD, QtyRcv, Cost, Price, NewPrice, Extended We need: Item Lookup Code, Description, MPQ, Price, BinLocation We need to modify few other forms as well. I think I've seen that in one of the add-ons while ago, but I cant remember which one was it and I'm not 100% sure if I really did. Thanks, Arthur ...

SUM of values within date range which equals another field
Hi, Can someone please help me out.. I have gone thru this forum and hav found a kinda right solution but mine is abit different. Basically its like for hospital data: I have say a date range say between 01/01/2002(start_date) an 01/04/2004 (end_date) Need to know how many patients (patient_ID) are admitted into parituclar department (heart_dept) within the above date range. If someone can help me would be really great : -- Message posted from http://www.ExcelForum.com Hi if column A stores the date and column B the department use =SUMPRODUCT(--(A1:A1000>=DATE(2002,1,1)),--(A1:A100...

Max Limit for Values in List Boxes and Combo Boxes in Outlook forms
Folks, a user is setting up a form. She wants to have several combo boxes and list boxes with between 75 and 300 (yes, 300 -- that's not a typo!) possible values in the combo box or list box. Before she starts work on this, she would like to know if there is a limit on the number of possible values for a combo box or a multi-select list box. Is there a limit? Is it defined by the number of characters in the values box, or the number of actual values? What is the limit? Any help here would be appreciated. Thanks. David consulttech2004@hotmail.com ...

CRM 1.2: Custom Field with hyphen in name averts default value settings
unfortunately we have customized a lookup-field in crm whose name includes a hyphen months ago. Now this seems to be the reason why it is not possible to change the default value of this lookup. Any experiences with special characters in field names? What can I do now? Is there another reason for my problem with the default values? Any hotfixes? It's a pitty that CRM didn't prohibit the usage of the hyphen on creation of the field, but now months later this error is hard to correct! Had a similar problem with Crystal Reports as well, where a special character in the fieldname averts...

Check Box
I have a table linked from an SQL Server database that I would like to update through an Access form. I need to use a series of check boxes to adjust over 100 different fields, but I need the check boxes to update the fields in the linked table with 1 for yes instead of -1. The reason for this is that the other software that loads information from the database uses 1 as the value for yes and cannot be configured otherwise. I cannot use custom formatting on the table fields since they are linked. I am assuming that a VB script is the way to go, but am having trouble getting...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

Use cell value as cell address
Hello everyone. I have a worksheet "Main" of 39,000 rows in which column B contains a number between 1 and 7,500. Column C is an empty column I have added. The second sheet, "Names" in the book contains a single column - A - of 7,500 names. I want to get the value from the second sheet that matches the number column of the first sheet. In other words, if "Main" cell B3 contains 3780, I want to put the value from "Names" cell A3780 into "Main" C3. How do I do this please? Richard --- Message posted from http://www.ExcelForum.com/ Hi tr...

Select null values in Crystal Report
I want to select those records in a Crystal Report where the country is not filled. I can select those where it is filled, however I get zero record back when I want to select where it is not filled. I use the following selection formula in the record selection part: Length ({account.address1_country}) < 1 Any idea is appreciated. Thanks, Miklos ...

How to compare "varchar" values?
I need to compare a student's score (that s/he got it after a test) with the score requirement. The problem is the scores are either a "character" only, i.e., "2" OR combined a "character" AND the '+'/ '-' character, i.e., 2+. For example, if the require score is 2+ and the student's score is 2 then that student is not qualified. The datatype of scores is varchar. Can you please help in programming how to compare these values? Thanks a lot in advance. I appreciate it. I see two approaches. The first one is a trick, ...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

All Columns are not to move
How can I stop the columns from moving to the left of the Table? -- Roger On Sun, 3 Feb 2008 17:28:39 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I stop the columns from moving to the left of the Table? You'll have to explain what on Earth you're talking about, Roger. What columns are "moving"? How? John W. Vinson [MVP] Good evening John, I found my own answer:by bringing the cursor to the first cell, the first column will not scroll to the left anymore; however, when I bring the cursor to any other cell and scroll to the rig...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

how to program recurring value
I wish every entry of a colums to be a the same function of the entry above. How do I do that? Leo Hi Leo! Assuming that you have your first formula entered Select the range covered by the common entry formula F2 Ctrl + Enter -- -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Leo Kerner" <l.kerner@sympatico.ca> wrote in message news:404294EF....

Hide Picklist based on value of other picklist.
On the accounts form i have made a new picklist named Partner. In the picklist Customertype i have made a new entry called partner. When someone opens the form i want the picklist named partner to be hidden. When someone selects Partner in the Customertype picklist i want the picklist partner to be visible. If an other value is selected the fields needs to stay hidden. I know i have to make some code on the onchange of the field customertype and i need to make code on the onload of the page. I have tried multiple codes but i cant get it to work. Can someone please help me out please. Hi,...

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...

Sort column with first and last name by last name
Hello, I have a mailing list with 10,000 names. The first cell has first an last name in the same cell. First name is listed first. Is there formula to sort and/or separate text in a cell. I would like to sort o separate the first name from the last in the cell to allow for a mai merge by name. Using Excel 2002. Thank you -- Message posted from http://www.ExcelForum.com If you have just first and last names separated by a space, Data>Text to Columns would be the easiest method to split into two columns. If more than that, like names with van or von or de etc. you may need a different m...

Can get UserProperty Names but not Values?
>From C++ using UserProp->Name I can get the name of a UserProperty, but UserProp->Value.bstrVal always returns nothing, anyone have any ideas what I could be doing wrong and has anyone ever accessed UserProperties from C++? UserPropertiesPtr UserProps = Con->UserProperties; if (UserProps->Count > 0) { tot = UserProps->Count; UserPropertyPtr UserProp; for (i=1;i < tot + 1;i++) { UserProp = UserProps->Item(i); MessageBox(NULL,UserProp->Name,"Test - Name",MB_OK); char* sText; sText = (char*)UserProp->Val...

How to only "paste values" of cells that are not "hidden"?
Hello, In my document, many columns are hidden. Say column B is hidden, and I need to copy columns A and C and paste values into another Excel document. How can I do that? When I select columns A and C, copy and paste values, the other document contains columns A,B and C, instead of just columns A and C. Thank you! Don't drag-select, control-click A and then C, for scattered-selection. Danny On Sun, 17 Jul 2005 16:33:01 -0700, Sam <Sam@discussions.microsoft.com> wrote: > Hello, > > In my document, many columns are hidden. Say column B is hidden, &g...

Bug? Multiple values in merged cells
I found that merged cells can contain multiple values. Steps to reproduce: Type 1,2,3,4 in a1:d1 type sum(a1:d1) in e1 Select a1:b1 and merge Warning : MultipleData, overwrite? Say yes to merge Select the merged a1:b1 cells Copy Select c1 PasteSpecial Formats No warning.. no overwrite. c1:d1 are now merged BUT d1 still contains a value... and the SUM of a1:d1 = 8 !! Also happens with FormatPainter etc Behaviour observed in xl97,xlXP and xl2003 Error checking will find no fault in the sheet... and you can spend ages to find out WHY your cross sums dont match! (although now that i fou...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

What does the program return value mean, for example 0(0x0) or 2 (0x2), when debug a program?
What does the program return value mean, for example 0(0x0) or 2 (0x2), when debug a program? -- ___________________ Xiongjun Xia It's what WinMain (or main) returns. -- Regards, Nish [VC++ MVP] "Xia Xiongjun" <xj-14@126.com> wrote in message news:%23wHd$78tFHA.2392@tk2msftngp13.phx.gbl... > What does the program return value mean, for example 0(0x0) or 2 (0x2), > when debug a program? > > -- > ___________________ > Xiongjun Xia > Think of these as random numbers. That way you won't be misled into thinking they have any significanc...

How to assign value to controls on a Report
I am trying to assign value on a txtbox control from a form to a txtbox control on a report. Form txtbox control:txtBeginDateRange Report txtbox control:txtStartDate reportName = "Report by DR and Vendor" I am have a problem with this line of code Reports![reportName]!txtStartDate = Me.txtBeginDateRange DoCmd.OpenReport reportName, acViewPreview Any help will be appreciated. Thank you. Ayo. Answered in another post where you asked the same question. ...