View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernd Bernd is offline
external usenet poster
 
Posts: 92
Default Generate alphanumeric unique 4 digit values from 12 digit values

12 digits are 10^12 = 1,000,000,000,000 possibilities.

4 alphadigits are 36^4 = 1,679,616 = a different alternatives.
1,679,627 = b is the next higher prime number. If you have less than a
articles you can define a table with b elements and convert your 12
digit strings as follows:
c = 12_digit_string mod b
if c not taken then c is new product code: table[c] = 12_digit_string
if c already taken then increase c until table[] is empty: set table[c
+i] = 12_digit_string
(if c+i = b then start over with 1...)
finally convert the index c or c+i to 4 digit string (base 36)

If 12_digit_string mod b will cluster your idents to much then take
another function.

Regards,
Bernd