Help with assigning random numbers please :)

KUstudent

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
Hey, guys! Happy New Years! I was hoping I could get some help. I have roughly 3,000 usernames in column B, and I need to assign each one a random string of letters and/or numbers.
I know about =RANDBETWEEEN(, but some of the usernames repeat, and I need repeat usernames to be assigned the same random string of letters. (Essentially, to protect the participants anonymity, I'm creating ID#s for them.)
Is there a function or macro that could do this for me so that I don't have to do all three thousand by hand? Thank you in advance!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
The first thing you should do is get a unique list of usernames.

That can easily be done by copying the usernames to another sheet and using Remove Duplicates under Data Tools.

You could then try using RANDBETWEEN to create a random 'ID' for each user but be warned you might end up with repeating values - I tried with =RANDBETWEEN(1000000, 9999999) and still got repeats.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
or you can try =BASE(RAND()*10^10,10,15) for distinct users
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,397
Office Version
  1. 2016
Platform
  1. Windows
Hi KUStudent,

Please have your Unique Id start with an alpha as it avoids issues with leading zeros and maths rounding.

Here I generated 3,000 random usernames of 8 characters in column B. I had no duplicates so I forced some to test (i.e. JYPGKQFK, XWYXGPHP).

Column C generates a random 6 character key but of course recalculates every time Excel recalculates so the first thing is to copy the column and paste as Values into column D.

Now we remove all duplicates with the formula in column E to help. The values in H1 and H2 show which row has a duplicate and how many others remain. You'll need to go to each row with an X in column E and change the last digit in column D. You'll know it worked because the X will disappear (I had to change 4 in my 3,000 sample).

Now we want to add back duplicates where the same Username appears so that's done with the column F formula.

Now you can take column F and paste as Values next to your usernames to get the final list.

KUStudent.xlsx
BCDEFGH
1UsernameRand1Rand1 Pasted as ValuesIdentify DuplicatesFix DuplicatesRow=#N/A
2NWWAYGNNN64738E77239 E77239Count=0
3XWYXGPHPM39744C29218 C29218
4NYUHGGWHX22095X46130 X46130
5JYPGKQFKG63773W06302 W06302
6JYPGKQFKE24688X02068 W06302
7RGXAGPXTK94024F98565 F98565
8TQEPWRFMD06356M35380 M35380
9XWYXGPHPP81094A84080 C29218
10FCGHGTJKV85425P20022 P20022
11WWJPXXHTF11979X65200 X65200
12RHEPFWMWX74310M97858 M97858
13JYPGKQFKA43043G07982 W06302
14FEEHREJQF08126V45991 V45991
15JYPGKQFKC75606W40708 W06302
16YFYGTFQPH66228K28395 K28395
Sheet1
Cell Formulas
RangeFormula
E2:E16E2=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16F2=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1H1=MATCH("X",$E$2:$E$3001,0)
H2H2=COUNTIF($E$2:$E$3001,"X")
C2:C16C2=MID("ACDEFGHKMNPQRTVWX",RANDBETWEEN(1,17),1)&TEXT(RANDBETWEEN(1,99999),"00000")
 
Solution

KUstudent

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

Hi KUStudent,

Please have your Unique Id start with an alpha as it avoids issues with leading zeros and maths rounding.

Here I generated 3,000 random usernames of 8 characters in column B. I had no duplicates so I forced some to test (i.e. JYPGKQFK, XWYXGPHP).

Column C generates a random 6 character key but of course recalculates every time Excel recalculates so the first thing is to copy the column and paste as Values into column D.

Now we remove all duplicates with the formula in column E to help. The values in H1 and H2 show which row has a duplicate and how many others remain. You'll need to go to each row with an X in column E and change the last digit in column D. You'll know it worked because the X will disappear (I had to change 4 in my 3,000 sample).

Now we want to add back duplicates where the same Username appears so that's done with the column F formula.

Now you can take column F and paste as Values next to your usernames to get the final list.

KUStudent.xlsx
BCDEFGH
1UsernameRand1Rand1 Pasted as ValuesIdentify DuplicatesFix DuplicatesRow=#N/A
2NWWAYGNNN64738E77239 E77239Count=0
3XWYXGPHPM39744C29218 C29218
4NYUHGGWHX22095X46130 X46130
5JYPGKQFKG63773W06302 W06302
6JYPGKQFKE24688X02068 W06302
7RGXAGPXTK94024F98565 F98565
8TQEPWRFMD06356M35380 M35380
9XWYXGPHPP81094A84080 C29218
10FCGHGTJKV85425P20022 P20022
11WWJPXXHTF11979X65200 X65200
12RHEPFWMWX74310M97858 M97858
13JYPGKQFKA43043G07982 W06302
14FEEHREJQF08126V45991 V45991
15JYPGKQFKC75606W40708 W06302
16YFYGTFQPH66228K28395 K28395
Sheet1
Cell Formulas
RangeFormula
E2:E16E2=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16F2=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1H1=MATCH("X",$E$2:$E$3001,0)
H2H2=COUNTIF($E$2:$E$3001,"X")
C2:C16C2=MID("ACDEFGHKMNPQRTVWX",RANDBETWEEN(1,17),1)&TEXT(RANDBETWEEN(1,99999),"00000")
Wow, I really, really appreciate you taking the time to do this! I am glad I sought help, because I did not realize it would be that confounding, haha.

Thank you very much for your time and energy :)
 

KUstudent

New Member
Joined
Jan 18, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
Hi KUStudent,

Please have your Unique Id start with an alpha as it avoids issues with leading zeros and maths rounding.

Here I generated 3,000 random usernames of 8 characters in column B. I had no duplicates so I forced some to test (i.e. JYPGKQFK, XWYXGPHP).

Column C generates a random 6 character key but of course recalculates every time Excel recalculates so the first thing is to copy the column and paste as Values into column D.

Now we remove all duplicates with the formula in column E to help. The values in H1 and H2 show which row has a duplicate and how many others remain. You'll need to go to each row with an X in column E and change the last digit in column D. You'll know it worked because the X will disappear (I had to change 4 in my 3,000 sample).

Now we want to add back duplicates where the same Username appears so that's done with the column F formula.

Now you can take column F and paste as Values next to your usernames to get the final list.

KUStudent.xlsx
BCDEFGH
1UsernameRand1Rand1 Pasted as ValuesIdentify DuplicatesFix DuplicatesRow=#N/A
2NWWAYGNNN64738E77239 E77239Count=0
3XWYXGPHPM39744C29218 C29218
4NYUHGGWHX22095X46130 X46130
5JYPGKQFKG63773W06302 W06302
6JYPGKQFKE24688X02068 W06302
7RGXAGPXTK94024F98565 F98565
8TQEPWRFMD06356M35380 M35380
9XWYXGPHPP81094A84080 C29218
10FCGHGTJKV85425P20022 P20022
11WWJPXXHTF11979X65200 X65200
12RHEPFWMWX74310M97858 M97858
13JYPGKQFKA43043G07982 W06302
14FEEHREJQF08126V45991 V45991
15JYPGKQFKC75606W40708 W06302
16YFYGTFQPH66228K28395 K28395
Sheet1
Cell Formulas
RangeFormula
E2:E16E2=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16F2=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1H1=MATCH("X",$E$2:$E$3001,0)
H2H2=COUNTIF($E$2:$E$3001,"X")
C2:C16C2=MID("ACDEFGHKMNPQRTVWX",RANDBETWEEN(1,17),1)&TEXT(RANDBETWEEN(1,99999),"00000")
Just wanted to come back and say I was successful at duplicating that. :) You're a life and time saver, and I really appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,999
Messages
5,628,052
Members
416,289
Latest member
Jbelisari

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top