Random numbers, Canadian Zip Code style

S

Shocked

In a previous job, we used Lotus Notes. To generate unique identifier
for individual forms and other documents (complaints, datasheets, etc.
a number/letter combination was generated based on the time and in th
style of Canadian Zip Codes.

Does anyone have any idea how this could be done in Excel
 
M

Myrna Larson

Certainly you can do it, but you need to decide on the algorithm to be used:
what sort of arithmetic do you want to do on the time to translate it?
 
J

jay somerset

In a previous job, we used Lotus Notes. To generate unique identifiers
for individual forms and other documents (complaints, datasheets, etc.)
a number/letter combination was generated based on the time and in the
style of Canadian Zip Codes.

Does anyone have any idea how this could be done in Excel?
Click to expand...

Yes. here is one way to do it.

First, create a column containing all the letters in the alphabet,
A-Z. Name the first cell in this column A.

Then, create another column containing the numbers 0-9.
Name the first cell N.

Thwe following formula will generate a Canadian style Postal Code each
time the worksheet recalculates (use F9 to force recalc).

=OFFSET(A,RANDBETWEEN(1,26)-1,0,1,1)&OFFSET(N,RANDBETWEEN(0,9),0,1,1)&OFFSET(A,RANDBETWEEN(1,26)-1,0,1,1)&"
"&OFFSET(N,RANDBETWEEN(0,9),0,1,1)&OFFSET(A,RANDBETWEEN(1,26)-1,0,1,1)&OFFSET(N,RANDBETWEEN(0,9),0,1,1)

You could put the letters and numbers in two rows, but then you would
have to change the row/column parameters in the OFFSET calls.

In theory, the codes generated this way are not unique, but the chance
of duplicating one is of the order of one in 1000*26^3 (approx. 17.5
million).
 
Top