Replace names with numbers

if one sheet has both the names and numbers on it you can do a vlookup, xlookup (provided that your excel is new enough) or index match against the sheet and that should simplify things for you. If it doesn't and you can create a list you can then do this easily enough without doing it manually. However without some sample workbooks we will just be guessing.

You wont be able to actually replace the names with numbers, but what you can do is use a helper to ID the number associated with the name using something like vlookup(), then value those formulas (so only numbers are left) and delete the orginal names.

Or you can use Find & Replace for each value.

Seeing a sample workbook would help to consolidate our advice, but I think you will end up going down the VBA route with this one.

you can use Power Query (Get&Transform) but your example should show real structure without your comments in

eg. the result like this

STUDENT IDENTIFICATION NUMBER	Student Name	Test A Score	Test B Score	Test B Score2	Test B Score3
87654321	Mary Smith	23	14	450	1
98765432	John Jones	57	9	345	2

are these the results you are looking for??? check rows 10 and 11 where the names were replaced with the student ID (SID) numbers and the grades were brought over to match the SID. I used index/match formulas in A11 and A12 and C11 through F12.
the formulas were variations of this index/match formula and you can see in them in your uploaded attachment...
=INDEX($A$17:$A$18,MATCH(B7,$B$17:$B$18,0))