need to get a random number for each different names on my spreadsheet (same name can appear twice)

[lvottiernct]

Hello,

I am looking for a bit of help here as i am still learning my way into excel.

I have this list of people that i need to transform into random numbers for anonymization reasons.

i thought of using ALEA but i have another problem, the same person can appear more than once on my list. if they appear twice, i still need them to have the same random number. I do not know how to do that, can someone help me please ?

thank you :) 

[#2 · Accepted solution]

@lvottiernct 

Hello! You can use this:

=HSTACK(UNIQUE(B3:B11),INDEX(UNIQUE(RANDARRAY(ROWS(UNIQUE(B3:B11)),1,0,1000000,TRUE)),SEQUENCE(ROWS(UNIQUE(B3:B11)),1)))

Change range B3:B11 with the actual range of your names. It will create an array with all unique names with a random integer number next to each one.

[#3]

Hello thank you for helping me, it would seem excel didnt like it.. my column with names is column C (from C3 to C9852), so it should be something like : =HSTACK(UNIQUE(C2:C9852),RANDARRAY(ROWS(UNIQUE(C2:C9852)),1,0,ROWS(UNIQUE(C2:C9852)),TRUE))
but when i hit enter all i get back is "#NOM?"

[#4]

@lvottiernct 

Hi, I edited my reply, please refer again to it. Additionally, your error seems to be because you may use Excel in another language. Please try to translate all functions to your language and change the comma separator to ";" if needed.

[#5]

thank you for your help !