Excel Picking random numbers with IF statement and creating random arrays

Question

I'm trying to create a list of random numbers that don't duplicate and then I want to take those numbers and generate multiple lists that are all random.

for example
for cells A1-A10 i want random numbers from 1-100
and then for A11-20 I want random numbers from 1-100
and then for A21 - A30 I want random number from 1-100

dragging down and repeating this seems possible with the MOD and OFFSET function which I'm still learning about.

For generating numbers I've tried

=RANDBETWEEN(1,100)

but it produces duplicates.

Ive also tried Putting

Rand()

in G1 and generating random numbers and then using

=RANK.EQ(G1,$G$1:$G$100)

along with:

=INDEX($H$1:$H$100, RANK(G1,$G$1:$G$100), 1)

which is better and doesn't produce duplicates but since i need multiple lists what happens is every list has the exact same data since the code is just replicating the same reference.

e.g : what i get is A1-10

A1 = 65
A2 = 54
A3 = 23
A4 = 31
A10= 23

then i try repeat the code for A11 - A 20 but it produces the same values.

even if i use

=INDEX($H$1:$H$50, RANK.EQ(G2, $G$1:$G$100) + COUNTIF($G$1:G2, G2) - 1, 1)

and then i have

=INDEX($H$1:$H$100, RANK(G1,$G$1:$G$100), 1)

in two differnt rows they produce the exact same random numbers

So i reckoned I need something that randomises every time.

Any help is appreciated

Lots of questions on here about random numbers - have you looked? Also consider looking for dealing with duplicates... — Solar Mike
– Solar Mike, Commented Apr 6, 2019 at 8:01
been reading for a few hours, cant find something that really fits. A lot of people give the rank() option as a solution but it doesnt work for this — Michael Morris
– Michael Morris, Commented Apr 6, 2019 at 16:38

Gary's Student · Accepted Answer · 2019-04-06 16:45:18Z

1

Try the following VBA macro:

Sub vRandom()
    Dim mn As Long, mx As Long, samples As Long
    Dim times As Long, arr1(), t As Long, s As Long
    Dim k As Long

    mn = 1
    mx = 100
    samples = 10
    times = 3
    k = 1

    ReDim arr1(mn To mx)
    For s = mn To mx
            arr1(s) = s
    Next s

    For t = 1 To times

        Call Shuffle(arr1)
        For s = 1 To samples
            Cells(k, 1) = arr1(mn + s - 1)
            k = k + 1
        Next s
    Next t

End Sub
Public Sub Shuffle(InOut() As Variant)
    Dim i As Long, J As Long
    Dim tempF As Double, Temp As Variant

    Hi = UBound(InOut)
    Low = LBound(InOut)
    ReDim Helper(Low To Hi) As Double
    Randomize

    For i = Low To Hi
        Helper(i) = Rnd
    Next i


    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

The code produces 3 blocks of items (10 items in each block).

Within a block there are no duplicates, but there may be duplicates between blocks.

answered Apr 6, 2019 at 16:45

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Morris Over a year ago

Gary's it worked incredibly! thanks for the time and effort, I truly appreciate it. Ive been sitting around all day trying all kinds of formulas and everything just fell apart. Thanks

Collectives™ on Stack Overflow

Excel Picking random numbers with IF statement and creating random arrays

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related