Unique random numbers daily calculation macro

**pco101** · 03-12-2011, 10:03 AM

Hi there

I would be extremely grateful if you could help.

I work in a prison.

There are 68 cells [1 to 68] in a unit. We have to search 3 random cells a day [this is a minimum requirement].

The main problem is that I want to generate a macro that selects 3 random cells on a daily basis until all 68 cells are randomly searched at least once a month.

e.g. On day 1, three of the 68 cells are generated randomly. The three cells to be searched are recorded in day 1.
On day 2, To keep everyone guessing, an officer will initiate a macro so that another three different cells are generated randomly and recorded in day 2.

And so on...

I can readily generate 68 random numbers in one calculation. But, I am utterly stumped on this problem and any help would be appreciated.

**shg** · 03-12-2011, 03:05 PM

Public Function aiRandLong(iMin As Long, _
                           iMax As Long, _
                           n As Long, _
                           Optional bVolatile As Boolean = False) As Long()
    ' UDF or VBA

    ' Adapted from Chip Pearson at http://www.cpearson.com/excel/RandomNumbers.aspx
    ' Returns a 1-based array of n unique Longs between iMin and iMax inclusive
    Dim aiSrc()     As Long
    Dim aiOut()     As Long
    Dim iSrc        As Long
    Dim iOut        As Long
    Dim iTop        As Long

    If bVolatile Then Application.Volatile

    If iMin > iMax Or n > (iMax - iMin + 1) Or n < 1 Then Exit Function

    ReDim aiSrc(iMin To iMax)
    ReDim aiOut(1 To n)

    ' init iSrc
    For iSrc = iMin To iMax
        aiSrc(iSrc) = iSrc
    Next iSrc

    iTop = iMax
    For iOut = 1 To n
        ' pick a number between 1 and iTop, swap with iTop, decrement iTop
        iSrc = Int((iTop - iMin + 1) * Rnd) + iMin
        aiOut(iOut) = aiSrc(iSrc)
        aiSrc(iSrc) = aiSrc(iTop)
        iTop = iTop - 1
    Next iOut

    aiRandLong = aiOut
End Function

Select three cells, e.g., A1:A3, and paste this into the formula bar:

=TRANSPOSE(aiRandLong(1,68,3))

Don't press Enter; instead press and hold the Ctrl and Shift keys, then press Enter.

If you enter the cell in a horizontal range, e.g., A1:C1, then skip the Transpose:

=aiRandLong(1,68,3)

To make the formula update, do Ctrl+Alt+F9.

**pco101** · 03-13-2011, 06:37 AM

Hi there

Thanks for the post. I've run the macro but duplicates are appearing. I don't think the UDF is disregarding the random cells that have already been listed. Moreover, I think I might need to use VBA code rather than a UDF because of two reasons:

1. Officers need a simple process [ideally just clicking a command button to activate a macro]

2. To list the random cells in a table

e.g.

A1=Day 1, B1=Cell 1, C1=Cell 2, D1=Cell 3
A2=Day 2, B1=Cell 4, C1=Cell 5, D1=Cell 6

Another problem will be on day 23. Because there are 68 unique numbers the macro will need to allow an extra number.

If you can lend any advice it would again be greatly appreciated. I have attached the excel table format just for clarification on what I am trying to achieve.

**pco101** · 03-13-2011, 02:31 PM

Thanks shg for your perseverance. I understand that I will need to post this in the appropriate forum now that I have shifted the goalposts.

**shg** · 03-13-2011, 03:50 PM

The UDF works fine, and it does not generate any duplicates for any given formula. It MUST be array entered as I explained.

If over several days you don't want any duplicates, thats a different problem, and the results each day are not random: Prisoner #1, having his cell searched on day one knows that his cell will not be searched again for 68/3 = 23 days.

**pco101** · 03-14-2011, 04:43 AM

Thanks shg
You are correct. I have run the macro and there are no duplicates so long as the array is entered correctly. Unfortunately, a lot of our staff who generate the random cell searches are only proficient in basic excel tasks and the UDF procedure will merely confuse them. I will adapt the formula to VBA code and link it to a command button.

p.s. As well as random cell searches we also conduct reasonable grounds cell searches, search cells that are vacated and periodically conduct search operations of groups of cells. The random three-cell a day searches are minimum compliance requirements to detect contraband and to check cell standards. Security checks are conducted on each cell every day.