Forum Discussion

Copper Contributor

May 06, 2024

Solved

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

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...

excel

Formulas and Functions

Martin_Angosto
May 06, 2024
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.

Martin_Angosto

Iron Contributor

May 06, 2024

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.

lvottiernct
Copper Contributor
May 06, 2024
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?"
- Martin_Angosto
  Iron Contributor
  May 06, 2024
  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.
  - lvottiernct
    Copper Contributor
    May 06, 2024
    thank you for your help !