Change multiple names to numbers

[Farro]

I am attempting to change names in multiple cells to their employee numbers.

I need to have a macro or function that can read from a list of names, compare to the list that I have created and attribute the new number in that cell (or a selected cell).

I have provided an example below.

A
1 Ryan Frank
2 Tim Jones
3 Shelly Show
4 Bill Ballroom
5 Diana Happen
6 Todd Torpo

The above would be the standard cell and then I would want to have a list such as

Ryan Frank 123456
Tim Jones 123232
Shelly Show 164675
Bill Ballroom 123489
Diana Happen 246557
Todd Torpo 756865


Then a way to have my report change to

A
1 123456
2 123232
3 164675
4 123489
5 246557
6 756865

Any help would be appreciated and if further clarification is needed please let me know

 

[wideboydixon]

INDEX/MATCH is your friend:

Excel 2016 (Windows) 32 bit
	A	B
1	1	Ryan Frank
2	2	Tim Jones
3	3	Shelly Show
4	4	Bill Ballroom
5	5	Diana Happen
6	6	Todd Torpo
7
8
9	1	123456
10	2	123232
11	3	164675
12	4	123489
13	5	246557
14	6	756865
15
16
17	Diana Happen	246557
18	Tim Jones	123232
19	Todd Torpo	756865
20	Ryan Frank	123456
21	Bill Ballroom	123489
22	Shelly Show	164675
Sheet1

Cell Formulas
Range	Formula
B17	=INDEX($B$9:$B$14,MATCH(INDEX($A$1:$A$6,MATCH($A17,$B$1:$B$6,0)),$A$9:$A$14))

WBD

 

[Farro]

What if it is not as clean as that and the spreadsheet looks similar to what I have attached below. This is an actual example of my spreadsheet i just changed the information around. Essentially, I am trying to substitute the names with a numbered code so I can post their results anonymously for them.

User Full Name	Courses	Credits	Total Current Credits
Ryan Frank	Fake Course Name 1	6	6
Bill Ballroom	Fake Course Name 1	12
	Fake Course Name 2	2
	Fake Course Name 3	6
	Fake Course Name 4	6
	Fake Course Name 5	6
	Fake Course Name 6	2.5
	Course: Version 4	2	36.5
Shelly Show	Fake Course Name 1	6
	Fake Course Name 2	6
	Fake Course Name 3	6
	Fake Course Name 4	6
	Fake Course Name 5	6
	Fake Course Name 6	6
	Course: Version 4	6
	Course: Version 5	6
	Course: Version 45	6
	Course: Version 42	6
	Test Course	6
	Course 67	2.5
	Understanding test course 3	16	84.5
Tim Jones	Fake Course Name 5	6
	Fake Course Name 6	6
	Course: Version 4	6
	Course: Version 5	6	24

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[wideboydixon]

Then just insert a column to the right of the "User Full Name" column and use VLOOKUP to retrieve the employee number from wherever you're storing that. It's hard to help if you don't post proper data tables - see my signature for a solution to that.

WBD

 

[Farro]

I appreciate the help on this solution.

Thank you!