Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Formula based Cell Content Return Unique Consecutive Duplicate Values | Excel Worksheet Functions | |||
To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions | |||
How to Sort by Count the Max nos of Unique text values in Pivot Ta | Excel Discussion (Misc queries) | |||
Summing unique values | Excel Worksheet Functions | |||
list unique values in a column | Excel Worksheet Functions |