Random number generating

If i use "=RANDBETWEEN" in an equation, how can i insure that the same number never gets used twice?

Attached is a spreadsheet sheet which I hope will explain what I am trying to accomplish.

Disregard Column A.

Column B has a drop down list showing Groups 1 to 23. When a group is selected Column D populates with the Group code and the next available number (based on the formula you provided earlier.)

I wanted to the number to be a random 5 digit number which can only be used once.

Since that is not possible I then assigned a range of numbers to each Group consisting of 500 numbers.

Can an equation be written that says.

If Group 1 is selected then the first Group code is ALMC-00001
If Group 2 is selected then the first Group code is ALPT-00500
If Group 1 is then selected again the Group code is ALMC-00002

so on and so forth....

I put some notes on the attachment.

Thanks!

I'm not sure how to code it, but as for your original question I think you might be able to do it by putting in some extra, clunky code that essentially just checks your new random number (after it has been created) against all your other previously created numbers, and deletes and tries again if there's a match. With some more time I might be able to write it, but I'm pretty slow at vba coding, so don't rely on me. If you can code yourself you might try that approach, else I'd wait for someone else to help out. In the meantime, I can try to code it a little bit today in case no else responds.

Jbm444,

I guess you and I are the same..I can "code" once I know the basic of the code I'm trying to use...if that makes any sense... with that said i haven't even dared start with any sort of looping or self check codes.

Hopefully one day soon....

Here is a workbook I had made for random numbers, maybe it will help you, maybe not.


Mark

Mark,

Great spreadsheet that I am sure I will find use for. The only problem I see with what you provided is each time you press "Create", it over writes the last issued numbers. In essence I could adjust the parameters, copy and paste to the other sheet, but I am looking for a cleaner, self populating approach.

I am going to tinker with it to see if I can get it to add more numbers without deleting the first group it generates.

Thanks for you valuable input.

Okay, obviously this doesn't get the checking job done, but here's the first step...

Please Login or Register  to view this content.

dagindi,

I figured it wouldn't be exactly what you wanted. It seems that whenever I am looking for code I always have to tweak it to fit my needs. I am glad it at leasts gives you something to work with.

Mark