MS Excel Randomly pick name with freedom to choose how many to select

Question

Everyone,

I'm trying to automated my excel file to choose random data to check for audit. I want to make randomizer that I can input how many data to select. is that possible in excel? I put some screenshot below for better explanation. I hope you can help me.

Answer 1

Using the usual excel functions this is indeed impossible... However, excel (and the other Microsoft office applications) run an underlying programming language: visual basic. That's the way to go :)

Here's a makro, that selects a random field matching the search in the whole column.

Sub SelectRandomSearch()
    'Declaring Variables
    Dim y As Integer
    Dim x As Integer
    Dim startY As Integer
    Dim lastY As Integer
    Dim search As String
    Dim hits As Integer
    Dim random As Integer
    Dim hitsArr() As Integer
    Dim controlPart As Double
    Dim controlsNum As Integer
    Dim controlArr() As Integer

    'Declaring Values
    startY = 1        'lowest Y-Coordianate of the input column
    x = 1             'X-Coordiante of the input column
    controlPart = 0.1 'Fraction of the hits, that need to be controled

    'Get search value
    search = InputBox("Enter a search value", "Searching", "")

    'Getting Column Lenght and reset coloring
    y = startY
    Do Until IsEmpty(Cells(y, x).Value)
        Cells(y, x).Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        y = y + 1
    Loop

    'Getting number of search hits
    lastY = y - 1
    hits = WorksheetFunction.CountIf(Range(Cells(startY, x), Cells(lastY, x)), search)

    'Fill hitsArr with row-numbers of hits
    ReDim hitsArr(hits + 1)
    hits = 1
    For y = startY To lastY
        If Cells(y, x) = search Then
            hitsArr(hits) = y
            hits = hits + 1
        End If
    Next y
    hits = hits - 1

    'Getting number of controlled Entries
    controlsNum = WorksheetFunction.RoundUp(hits * controlPart, 0)

    'Shuffle a part of hitsArr
    ReDim controlArr(controlsNum + 1)
    For y = 1 To controlsNum
        random = ((hits - y + 1) * Rnd + y)
        hitsArr(0) = hitsArr(y)
        hitsArr(y) = hitsArr(random)
        hitsArr(random) = hitsArr(0)
    Next y

    'Mark every hit that needs to be controlled
    For y = 1 To controlsNum
        Cells(hitsArr(y), x).Select
        With Selection.Interior
            .Color = 49407
        End With
    Next y
End Sub

You probably need to change the makro slightly, but this basicly does all I can think of you could need :)

I hope this helps!

Now the makro marks the fields that need to be checkt like this: