Random Selection Macro by Location and Ranking (High/Med/Low)

Hi all,

I am looking to create a macro to provide a random testing list of people based on their location and ranking. For example, wanting a list of a sample list of 3 individuals located in Sydney with a ranking from High, Med to Low.

I have attached a sample of the data set as a guide.

Any advise would be appreciated?


Regards

J

We would need the workbook password.

Sorry,

Password: Bud2014

This is similar to what I've done in other thread, but not really sure how you want the result.

Try and if this is not how you wanted, Need to see your expected result in your workbook.

Sub test()
    Dim a, i As Long, e, s, n As Long
    Const myCity As String = "Sydney"
    a = Cells(1).CurrentRegion.Value
    Randomize
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(a, 1)
            If Not .exists(a(i, 4)) Then
                Set .Item(a(i, 4)) = _
                CreateObject("Scripting.Dictionary")
                .Item(a(i, 4)).CompareMode = 1
            End If
            If Not .Item(a(i, 4)).exists(a(i, 8)) Then
                Set .Item(a(i, 4))(a(i, 8)) = _
                CreateObject("System.Collections.SortedList")
            End If
            .Item(a(i, 4))(a(i, 8))(Rnd) = a(i, 2)
        Next
        For Each e In .keys
            For Each s In .Item(e).keys
                n = n + 1
                a(n, 1) = e
                a(n, 2) = s
                a(n, 3) = .Item(e)(s).GetByIndex(0)
            Next
        Next
        Cells(2, "k").Resize(n, 3).Value = a
    End With
End Sub