[Example Code] Generate Unique Random Integers in a range of values

[bobsan42]

it started with a question: http://www.mrexcel.com/forum/showthread.php?t=488078
in short the question was: numbers 1 to 90 ->random list with 30 of them.
then i was really tempted to make a generic solution and i think i did.
Below is a UDF which will generate random integer numbers within chosen limits. If you also decide to fill the Range parameter of the function it will try to generate numbers which are not present in this range. if range is omitted it becomes something like RANDBETWEEN.
When further unique numbers cannot be found (all have been used already) it will return #NOMORE.
so when you use a range like A$1:A2 then filling down will expand the range and you will get a list of unique random numbers.
i did choose not to make it Volatile, but anyone who needs this will know why and how to add it.

Public Function RandUI42(LowerLimit As Long, UpperLimit As Long, Optional CompareRange As Range) As Variant
    Dim ll As Long, ul As Long
    ll = Application.WorksheetFunction.Min(LowerLimit, UpperLimit)
    ul = Application.WorksheetFunction.Max(LowerLimit, UpperLimit)
    If CompareRange Is Nothing Then
        RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
        Exit Function
    End If
    For i = ll To ul Step 1
        If Application.WorksheetFunction.CountIf(CompareRange, i) = 0 Then
            RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
            Do While Application.WorksheetFunction.CountIf(CompareRange, RandUI42) > 0
                RandUI42 = Round(ll + (ul - ll) * Rnd(), 0)
            Loop
            Exit Function
        End If
    Next i
    RandUI42 = "#NOMORE"
End Function

any comments, suggestions, etc. are appreciated.
all questions will be answered.
hope someone will find it useful

chrees to all

 

[tusharm]

I am sure you mean well in sharing this code. However, it exhibits several problems that make its use...shall we say...impractical.

In the link I posted in the other discussion, you will find a *single pass* VBA generator.

There are at least 4 problems with your code. The first is a logic error in that it doesn't generate numbers with equal probability. Don't use Round() with Rnd().

Second is the UI. Take a list (array) of values and return an array, not one element at a time. One element at a time is not only extremely inefficient but also a rather non-intuitive UI.

Third, is efficiency and this comes in three parts. The first is the above. You are scanning the CompareRange for 1 number and then 'forgetting' the result when it comes to the next number.

Also, your code takes longer and longer to find an available number as you go through the list. This time will be significant when generating a long list of numbers from a list that is only slightly larger (or even the same). For example, generate a list of 1,000,000 names in random order given a list of 1,000,000 names.

Finally, you will also find that using CountIf is not a very efficient approach. Bouncing back and forth between VBA and Excel functions and VBA code and Excel ranges has an overhead that over time can noticably affect performance.

 

[bobsan42]

thanks for the remarks. some i agree with, some i don't actually accept.
well, i did actully read the link before i started this thread:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/
but there is a difference between the ideas and i thinki probably didn't succeed to explain mine very well.
the site behind the link explains how to create a list of random numbers.
my code is aimed at providing a single number.
i am sure i still have a lot to learn and a lot of the people here know much more than I do. But the other side of the coin has nothing to do with this. nost of the people try to find a simple and working solution to their questions (eventually effective). So a huge part of them will hardly try to understand the long explanations provided sometimes.

The main idea of my code is to provide a single random integer which is not already in the specified range. If the user needs a list of a certain length he/she just needs to fill a desired number of cells with the appropriate formula.

The first is a logic error in that it doesn't generate numbers with equal probability. Don't use Round() with Rnd().

this point is a bit debatable, but i am ready to accept that a combination between RND and RANK of some sort is probably a better solution from this point of view.

Second is the UI. Take a list (array) of values and return an array, not one element at a time. One element at a time is not only extremely inefficient but also a rather non-intuitive UI.

Third, is efficiency and this comes in three parts. The first is the above. You are scanning the CompareRange for 1 number and then 'forgetting' the result when it comes to the next number.

Also, your code takes longer and longer to find an available number as you go through the list. This time will be significant when generating a long list of numbers from a list that is only slightly larger (or even the same). For example, generate a list of 1,000,000 names in random order given a list of 1,000,000 names.

i actually agree about the efficiency but since the idea is to generate a single random number i cannot think of a better approach. yes it takes longer and longer to generate every next number but for reasonable legth of lists it OK. lets not talk about genrating 900 000 randoms from a list of 1 000 000. I such cases such solutions are quite inappropriate. but a lot of formulas (enen quite simple) created with built-in functions may fail then.

Finally, you will also find that using CountIf is not a very efficient approach. Bouncing back and forth between VBA and Excel functions and VBA code and Excel ranges has an overhead that over time can noticably affect performance.

yes - this is also quite true. probably i could use a find method or even put the range in an array but for a 15-minute-midnight-solution i think it is quite fine. the reason behind this is that the way i chose does not care about the shape, size and contents of the selected range and it was the fastest to write.

I am not an expert or MVP but i am an engineer and i prefer to make things that work in a set of circumstances (it's quite a hard task to make it universal). There will always be a WHAT IF.
Thanx again for the remarks - as soon as i find time to think over it i will probably find a better approach.
Conclusion: Don't try to generate really long lists with this UDF. Don't use it for scientiffic work. Feel free to improve it.

 

[bunnymort]

Digging out an old topic.

Reading it was interesting.
It gave me an idea that I am going to apply to another purpose.

To get the same result with "more efficiency", what would you think about the following?
1-Fill an array with all the unique numbers in your range
-Provide random keys to randomize their order
3-Dump it to the desired cell range.
Just check that the key is not in use and determine the cell range size before dumping.

I am also keen to go for quicker and working solutions rather than aiming at logical efficiency.