# Help with assigning random numbers please :)

#### KUstudent

##### New Member
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!

### 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
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
or you can try =BASE(RAND()*10^10,10,15) for distinct users

##### Well-known Member
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")

#### KUstudent

##### New Member

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

##### Well-known Member
You're welcome! Glad I could help.

Replies
8
Views
79
Replies
2
Views
71
Replies
8
Views
192
Replies
6
Views
261
Replies
0
Views
206

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?

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