Need to generate random numbers with no duplicates

Hi everyone. I am working on a spreadsheet that will assign various tasks to individuals without assigning any task twice. Another stipulation is that certain individuals should not be assigned certain tasks deemed outside of their "area". Here is what I have done, thus far:

I assigned a number (1-198) to each task and grouped the tasks into specific areas. Then I used the following to generate random numbers between 144-159 across cells D46:G57.

Sub EXAMPLE()
Dim FillRange As Range, c As Range
Set FillRange = Range("D46:G57")
For Each c In FillRange
Do
c.Value = Int((159 - 144 + 1) * Rnd + 144)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

However, as you can see - the range of cells is greater than the number of integers between 144-159. Is there a way for me to specify that, once this has run, fill in the remaining cells with numbers that correspond to a second category of tasks?

Additionally, I have created several macros corresponding to different number ranges and cells. However, some of the categories overlap, and I am getting duplicate numbers.

Does anyone have a better solution for me? I am very much a beginner to all of this and appreciate any help you can offer.

Thanks!

You need some tricks to write the algorithm for drawing randomly without repeating. The following is my codes to write random draw no. 1 to 10 into cells(1, 1) to cells(1, 10) sequentially. You can replace the total no. to any number you want.

Sub Draw_Randomly()
Dim Total As Integer, Occupy() As Boolean, Position() As Integer
Dim Remain As Integer

Total = 10
ReDim Occupy(1 To Total)
ReDim Position(1 To Total)
Remain = Total

For i = 1 To Total
    Position(i) = i
    Occupy(i) = False
Next i

Randomize
For i = 1 To Total
    n = Int(Rnd * Remain) + 1
    Cells(i, 1) = Position(n)
    Remain = Remain - 1
    For j = n To Remain
        Position(j) = Position(j + 1)
    Next j
Next i

End Sub

This is how I would have gone about solving your problem.

Say you have three different areas:

Task_Area_1
Task_Area_2
Task_Area_3

Each task area has task numbers associated with it, some more than others. After dimensioning all of the above as tables, you will have something like this, for example:

Task_Area_1(20) ' 20 elements in this table
Task_Area_2(10) ' 10 elements in this table
Task_Area_3(5) ' 5 elements in this table

Task_Area_1 has twenty different tasks numbers. Task_Area_2 has ten different task numbers, etc.

Start off by assigning numbers to each:

for x = 144 to 163 ' 20 elements in the range 144 to 163
Task_Area_1(x) = x
next x

for x = 13 to 22 ' 10 elements in the range 13 to 22
Task_Area_2(x) = x
next x

etc.

Now you randomly shuffle each Task_Area. One way to shuffle is make just one pass through the array, swapping that element with a random array element.

Pseudocode:

for x = 1 to 20
swap Task_Area(x) with Task_Area(random_number from 1 to 20)
next x

That's it. You're done.

Your Task Area_1 values now might look something like this:

Task_Area_1(1) = 160
Task_Area_1(2) = 163
Task_Area_1(3) = 147
Task_Area_1(4) = 157

etc.

So if you need 15 tasks from this Task_Area_1, you just pick the first 15 elements of this array. They already contain the specific range you want, and they are already rancomized.

I've done similar types of things over the years involving games that use playing cards for example.

I hope this is clear.