Hello, how can i create a unique 3 character alpha numberic column in excel 2010? I only have 3 characters to work with and I need to assign a unique code to over 5,000 inventory items. I have the items in Excel. Please help.

The letters A-Z 3 times will give you 26^3 = 17576 unique codes.

In cell A1 =ROW()-1 and fill down to A17576
In cell B1 =MOD(A1,26)+65 and fill down to B17576
In cell C1 =INT(MOD(A1/26,26))+65 and fill down to C17576
In cell D1 =INT(MOD(A1/26/26,26))+65 and fill down to D17576
In cell E1 =CHAR(B1)&CHAR(C1)&CHAR(D1) and fill down to E17576

Column E gives you the unique alpha codes. Copy them and paste special to turn into constants. Then you can use them.

__________________
Colin

RAD Excel Blog

Thank you that worked for all Alpha, do you know of a formula for Alpha Numeric with the same 3 character contraint?

Yes, 0 to 9 gives 10 extra characters so 36^3 = 46656

A1 =ROW()-1 and fill down to A46656
B1 =MOD(A1,36)+IF(MOD(A1,36)<26,65,22)
C1 =INT(MOD(A1/36,36))+IF(INT(MOD(A1/36,36))<26,65,22)
D1 =INT(MOD(A1/36/36,36))+IF(INT(MOD(A1/36/36,36))<26,65,22)
E1 =CHAR(B1)&CHAR(C1)&CHAR(D1)


If case sensitive then you could also include lower case alpha which would then give you 238328 unique codes. I'll let you have a try at that one.

__________________
Colin

RAD Excel Blog

Thank you.

Can you tell me how I can sort the 3 character alpha numeric value, so they are in some logical order.

What order do you want them in?

__________________
Colin

RAD Excel Blog

Actually, they're already in a logical order, but perhaps it wasn't apparent. The order was the reverse of what might be expected. A more conventional order in column E would be gained by:
=CHAR(D1)&CHAR(C1)&CHAR(B1)

Alternatively, you could put the whole catastrophe into a single formula:
=CHAR(MOD((ROW()-1)/1296,36)+22+(MOD((ROW()-1)/1296,36)<26)*43)&CHAR(MOD((ROW()-1)/36,36)+22+(MOD((ROW()-1)/36,36)<26)*43)&CHAR(MOD(ROW()-1,36)+(MOD(ROW()-1,36)<26)*43+22)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]