Help! Replacing rep-codes with rep names...


Posted by Scott on July 05, 2001 9:27 AM

I have a huge spreadsheet of sales data. Unfortunately, this spreadsheet only includes my sales representatives 4-digit rep-code. I want to replace the rep-codes with their names.

There are so many entries and reps, that going through and replacing each 4-digit code with their names manually would be foolish.

I know I can create a seperate, two-column table, and use a 'lookup' (I don't know if that is the term) function to find and replace the rep-codes with the rep names. I have done this, but I don't know how to tell Excel to lookup the repcodes in this table, and replace it with the rep name... The table with the rep-code and rep-name is two columns and looks like this:

4414 (column) John Smith

CAN YOU HELP?!?!

Posted by Scott(2) on July 05, 2001 9:36 AM

Would it be easier to use the EDIT-REPLACE function and in "Find What" type in the Rep Code, and in "Replace with" type in the Rep's Name. Then select "Replace All". you would have to do this once for each rep. You would probably want to highlight the column first to avoid replacing any other data by mistake. If this doesn't work for you, you could use a VLOOKUP formula, but you would have to create a table with the Rep name and number first.

Posted by Russell on July 05, 2001 9:39 AM

Use VLOOKUP. In the sheet that you want the numbers replaced with names, insert a column after the rep numbers.

Ok, say we are starting in row 2, and that your rep numbers are in column A, with the blank column that you just inserted being column B. In column B, row 2, type this formula (you will have to modify for your rep-code rep-name table):

=VLOOKUP(A2,sheet2!$A$1:$B$10, 2, FALSE)

Ok, this assumes that your table with rep-code and rep-name is in sheet2, and it is 10 rows long with rep-code in col A and rep-name in col B.

After you do this, you can select column B (which should now have the rep names), copy, and then click in cell B1 and paste special - values. You can then delete column A (but make sure that all the names are in first!!!!).

Hope this helps,

Russell

Posted by Scott on July 05, 2001 9:42 AM

------

Thanks.

I would prefer to use the VLOOKUP formula... I already have the lookup table made, but I don't know what command to use so that Excel replaces the rep-codes in the main spreadsheet with the rep-names in the lookup-table...

It doesn't really even have to *replace* the rep-codes, but perhaps insert the rep-name on the same row (if that makes it easier)...



Posted by Scott on July 05, 2001 9:46 AM

PERFECT! Thanks, I will try this right away...