Generate alphanumeric unique 4 digit values from 12 digit values
 

I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?

Generate alphanumeric unique 4 digit values from 12 digit values
 

Can you give an example of what you're trying to do?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mikep" wrote in message ...
| I'm trying to convert 12 digit strings to individually unique 4 digit strings
| for product coding. Any ideas?

Generate alphanumeric unique 4 digit values from 12 digit values
 

On Feb 9, 2:03 pm, mikep wrote:
I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?

Examples required.

1) Insert a column
2) In the first row put '0000
3) In the second row put '0001
4) Fill down to your hearts content
5) You now have 10000 unique 4 digit ids

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

Generate alphanumeric unique 4 digit values from 12 digit values
 

Mike,

If your 12 digit strings are in column A, starting in Row 2, then in cell B2 use the array formula
(entered with Ctrl-Shift-Enter)

=IF(COUNTIF($A$1:A2,A2)1,VLOOKUP(A2,$A$1:$B2,2,FA LSE),TEXT(MAX(VALUE($B$1:B1))+1,"0000"))

and copy down to match your list in column A. It will work, possibly slowly depending on how big
your list is...

HTH,
Bernie
MS Excel MVP


"mikep" wrote in message
...
I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?

Generate alphanumeric unique 4 digit values from 12 digit values
 

DataText to ColumnsFixed WidthNext. Click after every 4th number to draw a
break line.

The NextColumn Data FormatText for each column(in case a 4-digit number has a
0 as first digit.

Finish.


Gord Dibben MS Excel MVP


On Fri, 9 Feb 2007 11:03:01 -0800, mikep
wrote:

I'm trying to convert 12 digit strings to individually unique 4 digit strings
for product coding. Any ideas?