5-digit Random Unique Alphanumeric Code

[mcodden]

I am looking to create a 5-digit random alphanumeric code that is unique because they will be associated as certain discount/promo codes. We need them to follow the format of: letter, number, letter, number, letter. Example: f9d8s, a8b2w, y6x3q, g0o5n,w2p3p, n1g8v.

I know I can create random codes using RANDBETWEEN if needed, but I know this will eventually create duplicate codes and we need to make sure all of the codes are unique. There are 1.7million combinations, so we should be able to use this generator for a long time if I can figure out how the formula/macro.

Thanks!

 

[DRSteele]

I see your problem. Firstly, this will generate the proper codes:

=CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))

[source: http://www.clickconsulting.com/forum/excel-support/random-alphanumeric-values-excel]

Secondly, you can keep a log of the codes you publish and then check to see if a new candidate has been created.

Excel 2012
	A	B
1	K1S1D	FALSE
2
3	Published
4	E5Q9W
5	T7P0M
6	G7S5X
7	D2Z5I
8	X1N5G
9	Z8D3K
10	L0H7P
11	P6H2I
12	K8F1Z
13	G9S8T
Sheet69

Cell Formulas
Range	Formula
A1	=CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))
B1	=IF(SUMPRODUCT(--(A1=A:A))>1,"Duplicate")