Announcement

Collapse
No announcement yet.

Picking multiple values randomly without repetition, into a single cell.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Picking multiple values randomly without repetition, into a single cell.



    New
    I have three ranges (B1:D6) filled with numbers. I would like to pick at least 3 numbers randomly without repetition from the given ranges into the cells F3:F5. This has to be based on the category of the ranges given in cells (E3:E5) viz., Hard, Easy, Moderate. The cells F3, F4 & F5 should be populated with minimum of 3 values from the desired range respectively.

    A B C D E F
    1 Hard Easy Moderate
    2 1 2 11
    3 3 4 13 Hard 1, 8, 3
    4 6 5 14 Easy 2, 10, 5
    5 8 7 19 Moderate 13, 19, 11
    6 9 10 21

    How can I achieve this? Pl help.

  • #2


    Hi,

    There's a really nice function called ShuffleArrayInPlace over at Chip Pearson's site.

    http://www.cpearson.com/excel/ShuffleArray.aspx

    That you can use to achieve this. It basically randomly shuffles an array's contents, then you can select what random values you need from the array. I have used it for developing a Video Poker game which will feature on my website excelcoding.com when I eventually get it up and running. You load the card deck into a 52 place array shuffle that using the function then deal out what cards you need from the top of the array. It has other uses such as selecting unique Lottery numbers, But I digress not to mention in the following code which should hopefully meet your needs.

    Code:
    Option Explicit
    Option Base 1
    
    ' Shuffle Arrays.
    Public vntEasy(5) As Variant
    Public vntHard(5) As Variant
    Public vntModerate(5) As Variant
    
    Private Sub LoadShuffleArrays()
    
    ' Easy.
    vntEasy(1) = 2
    vntEasy(2) = 4
    vntEasy(3) = 5
    vntEasy(4) = 7
    vntEasy(5) = 10
    
    ' Hard.
    vntHard(1) = 1
    vntHard(2) = 3
    vntHard(3) = 6
    vntHard(4) = 8
    vntHard(5) = 9
    
    ' Moderate.
    vntModerate(1) = 11
    vntModerate(2) = 13
    vntModerate(3) = 14
    vntModerate(4) = 19
    vntModerate(5) = 21
    
    End Sub
    
    Public Sub PickRandomValues()
    
    LoadShuffleArrays
    
    ' Shuffle the arrays in place.
    ShuffleArrayInPlace vntEasy
    ShuffleArrayInPlace vntHard
    ShuffleArrayInPlace vntModerate
    
    ' Populate the random values in the worksheet.
    wsPickRandomValues.Cells(3, 6) = vntHard(1) & "," & vntHard(2) & "," & vntHard(3)
    wsPickRandomValues.Cells(4, 6) = vntEasy(1) & "," & vntEasy(2) & "," & vntEasy(3)
    wsPickRandomValues.Cells(5, 6) = vntModerate(1) & "," & vntModerate(2) & "," & vntModerate(3)
    
    End Sub
    
    Sub ShuffleArrayInPlace(InArray() As Variant)
    
    ' +------------------------------------------------------------------+
    ' | ShuffleArrayInPlace                                              |
    ' |                                                                  |
    ' |  http://www.cpearson.com/excel/ShuffleArray.aspx                 |
    ' |  This shuffles InArray into a random order, randomized in place. |
    ' +------------------------------------------------------------------+
    
    Dim lngInArrayIndex As Long
    Dim vntTemporary As Variant
    Dim lngShuffledArrayIndex As Long
      
    Randomize
    
    For lngInArrayIndex = LBound(InArray) To UBound(InArray)
        lngShuffledArrayIndex = CLng(((UBound(InArray) - lngInArrayIndex) * Rnd) + lngInArrayIndex)
        If lngInArrayIndex <> lngShuffledArrayIndex Then
           vntTemporary = InArray(lngInArrayIndex)
           InArray(lngInArrayIndex) = InArray(lngShuffledArrayIndex)
           InArray(lngShuffledArrayIndex) = vntTemporary
          
           ' Debug zero lenth
           If vntTemporary = "" Then Err.Raise 9999
          
        End If
    Next
    
    End Sub

    I have attached a demo worksheet for you.

    Regards,

    Tom Rowe
    Attached Files

    Comment

    Working...
    X