Random number generator with x number of duplicates

Hi all,

I was wondering how to generate random numbers with limited duplicates.

I have a code to select these specific numbers only in VBA,

Please Login or Register  to view this content.

The thing is I want to limit my duplication as I want to run this on multiple rows.

An example of this scenario would be:

I have 100 rows, within the hundred rows there will be only 1 two, 3 fours, 4 fives, ..... , 99 one hundreds.

Obviously there wouldn't be 191 numbers within 100 rows, just saying if a 4 is in the row then the program will now have two 4s left to insert or whether it will be used or not based on random choice.

Hope this makes sense as I have tried searching but I have no luck in finding a solution.

Please let me know how I can go about it.

Cheers.

Wow this is interesting.

I am sure that I can do this.

But I would need to use a user defined function.

You haven't said if you wanted you output as a column, a row or a cell

I will look at this and get back to you.

I think I have it.

I have built in two lots of error handling for you, to use while we test it.

The first one: calculates your RunSum from you Mn and Mx number, it then checks to make sure that you have enough available rows, RunReq.

The second will break your loop at 2000 loops. you can increase this as you get to trust the macro.

The routine works by filling an array with random numbers between your specified minimum and maximum numbers.

It then counts between your min and max numbers and counts how many times that number exists in the array.
If it matches or exceeds your limit then the number is replaced by another valid random number, obviously this could bust the limit of that number,
so we raise a flag and loop back after our loop is complete. And we do it all over again.

The Closer your RunSum is to your RunReq then the more loops you will need.

I set the limit of loop backs to 2000, but this could be infinite if my first check proves to be effective.
Even with 2000 loops my macro sometimes breaks out of the loop before finding a valid solution.

But hey you wanted random numbers right?

Lets see how you get on.

Please Login or Register  to view this content.

Thanks for posing such an interesting problem.

I spent some time testing this. It is a bit confusing but I have made some progress.

The latest version allows you to either enter a range, or a start address and the numbers of rows to insert

I have created a sheet specific macro that looks for you entering a FactRand formula and then copies the formula down to the required number of rows.

Th sheet specific macro copies and pastes values over the formulas that it adds to save some confusion.

Please Login or Register  to view this content.

Hi atomichybrid (and fellow Aussie ),

Here's my attempt:

Please Login or Register  to view this content.

Regards,

Robert

Hi atomichybrid,

Let us know how this goes:

Please Login or Register  to view this content.

Regards,

Robert