Create a Unique Code Generator with Excel

[LukeA79]

Hi folks!

SO, I am stuck trying to create a simple Excel worksheet (using Excel 2019) to allow me to create a unique identifier every time a button is pressed.

These are my requirements:

1. The Identifier must be 2 letters (always the same "XX") and 5 numbers, from 30001 to 79999
2. The Identifier must ALWAYS be a new one

The user interface should be extremely easy, that means just one white blank visible sheet with 1 button in the middle reading "Generate ID".

I have done some legwork and created the list of identifiers and also randomized them, but I do not know how to "store" in Excel the ID's that have already been generated.

Any ideas?

Thanks!

[#2 · Accepted solution]

@LukeA79 Hi, Why does the identifier need to be random?

If it doesnt, just extract the numbers, find the max and add 1, and error out when you hit the max. Since you know the format allowed, theres no need to have a list of available numbers.
Alternatively,

Create vba code to pull all used names, extract the numbers into an array, sort ascending, loop 49998 times and quit when you find an unused number.

Let me know if you need any more help.

[#3]

@alandorss 

Thank you for proposing a solution, I realized it is way beyond my skills and I would need too much time to learn this from scratch (it is for my job, but I will ask the IT department to sort this out).

I will try with an Access Web APp published to SharePoint