5-digit Random Unique Alphanumeric Code

[mcodden]

I'm looking to create 5-digit random but unique alphanumeric codes, in the format of: letter, number, letter, number, letter. Example: d9h5e, p3g5a, c5x2w, y0v5v, s6c3q, i3v6a. I'm trying to avoid RANDBETWEEN because with the volume of codes we need to create (~6,500 to start) I want to ensure that all the codes are being created uniquely, without duplicates. And if we stick to letter, number, letter, number, letter, we will have 1.7 million variations.

Thanks!

[ChemistB]

I don't know that there is a formula solution that guarentees there are no duplicates and using RANDBETWEEN is no more suseptible than other methods.

=CHAR(RANDBETWEEN(97,122)) & RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(97,122)) & RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(97,122))
In the next column you can run a check for any duplicates with something like

=SUMIF($A$1:A1, A1)

[6StringJazzer]

I would agree with ChemistB's solution. Be aware that RANDBETWEEN is volatile so if your sheet recalculates the values are all going to change. If you use this formula to generate a long list of numbers then check for duplicates using the formula provided, you can tweak those by hand to be unique. However, I used those formulas to generate 6500 numbers and in 20 trials did not find a single duplicate.

[mcodden]

Yeah that's why I wanted to stay away from using RANDBETWEEN because we either need to generate a lot of codes the first time and never change the data or find an alternate route. I'll try to post to the macros portion as well.

[GeneralDisarray]

would be pretty simple via a macro:

Basically,
Have the macro author the formula ChemistB put up into two columns - then lock the values.

Then, have the macro 'flag' any cells with a count greater than 1 (may be tricky to make fast, think I could do it though) - Rekey -those cells (add the formula again) and then lock values... check for uniqueness again (if the SUM of the counts columns = the COUNT of records, you are good to go...)

That way it would be automatic and unique :D

[GeneralDisarray]

See attached.

The code is pretty straightforward I would think, you would need to edit it to work on columns other than A and B. Randbetween gets you there after a couple of iterations.



edit: I tried to mark up the parts you would need to edit for another column in red type.

Please Login or Register  to view this content.

[LJMetzger]

Duplicate Post. See http://www.excelforum.com/excel-prog...eric-code.html

[GeneralDisarray]

LOL, this was the first post....

[6StringJazzer]

I'll try to post to the macros portion as well.

Please do not post the same question to multiple subforums. We have a rule against duplicate posts. If you feel that you should post to a different subforum please ask a moderator to move it for you.


This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.