Help with assigning random numbers please :)

[KUstudent]

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!

 

[Norie]

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]

or you can try =BASE(RAND()*10^10,10,15) for distinct users

 

[Toadstool]

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
	B	C	D	E	F	G	H
1	Username	Rand1	Rand1 Pasted as Values	Identify Duplicates	Fix Duplicates	Row=	#N/A
2	NWWAYGNN	N64738	E77239		E77239	Count=	0
3	XWYXGPHP	M39744	C29218		C29218
4	NYUHGGWH	X22095	X46130		X46130
5	JYPGKQFK	G63773	W06302		W06302
6	JYPGKQFK	E24688	X02068		W06302
7	RGXAGPXT	K94024	F98565		F98565
8	TQEPWRFM	D06356	M35380		M35380
9	XWYXGPHP	P81094	A84080		C29218
10	FCGHGTJK	V85425	P20022		P20022
11	WWJPXXHT	F11979	X65200		X65200
12	RHEPFWMW	X74310	M97858		M97858
13	JYPGKQFK	A43043	G07982		W06302
14	FEEHREJQ	F08126	V45991		V45991
15	JYPGKQFK	C75606	W40708		W06302
16	YFYGTFQP	H66228	K28395		K28395
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16	F2	=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1	H1	=MATCH("X",$E$2:$E$3001,0)
H2	H2	=COUNTIF($E$2:$E$3001,"X")
C2:C16	C2	=MID("ACDEFGHKMNPQRTVWX",RANDBETWEEN(1,17),1)&TEXT(RANDBETWEEN(1,99999),"00000")

 

[KUstudent]

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
	B	C	D	E	F	G	H
1	Username	Rand1	Rand1 Pasted as Values	Identify Duplicates	Fix Duplicates	Row=	#N/A
2	NWWAYGNN	N64738	E77239		E77239	Count=	0
3	XWYXGPHP	M39744	C29218		C29218
4	NYUHGGWH	X22095	X46130		X46130
5	JYPGKQFK	G63773	W06302		W06302
6	JYPGKQFK	E24688	X02068		W06302
7	RGXAGPXT	K94024	F98565		F98565
8	TQEPWRFM	D06356	M35380		M35380
9	XWYXGPHP	P81094	A84080		C29218
10	FCGHGTJK	V85425	P20022		P20022
11	WWJPXXHT	F11979	X65200		X65200
12	RHEPFWMW	X74310	M97858		M97858
13	JYPGKQFK	A43043	G07982		W06302
14	FEEHREJQ	F08126	V45991		V45991
15	JYPGKQFK	C75606	W40708		W06302
16	YFYGTFQP	H66228	K28395		K28395
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16	F2	=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1	H1	=MATCH("X",$E$2:$E$3001,0)
H2	H2	=COUNTIF($E$2:$E$3001,"X")
C2:C16	C2	=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]

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
	B	C	D	E	F	G	H
1	Username	Rand1	Rand1 Pasted as Values	Identify Duplicates	Fix Duplicates	Row=	#N/A
2	NWWAYGNN	N64738	E77239		E77239	Count=	0
3	XWYXGPHP	M39744	C29218		C29218
4	NYUHGGWH	X22095	X46130		X46130
5	JYPGKQFK	G63773	W06302		W06302
6	JYPGKQFK	E24688	X02068		W06302
7	RGXAGPXT	K94024	F98565		F98565
8	TQEPWRFM	D06356	M35380		M35380
9	XWYXGPHP	P81094	A84080		C29218
10	FCGHGTJK	V85425	P20022		P20022
11	WWJPXXHT	F11979	X65200		X65200
12	RHEPFWMW	X74310	M97858		M97858
13	JYPGKQFK	A43043	G07982		W06302
14	FEEHREJQ	F08126	V45991		V45991
15	JYPGKQFK	C75606	W40708		W06302
16	YFYGTFQP	H66228	K28395		K28395
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IF(COUNTIF($D$2:$D$3000,D2)>1,"X","")
F2:F16	F2	=IF(ISNA(MATCH(B2,$B$1:$B3,0)),D2,INDEX($D$1:$D3,MATCH(B2,$B$1:$B3,0)))
H1	H1	=MATCH("X",$E$2:$E$3001,0)
H2	H2	=COUNTIF($E$2:$E$3001,"X")
C2:C16	C2	=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!

 

[Toadstool]

You're welcome! Glad I could help.