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

  • New [INDENT]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.[/INDENT]

  • 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.




    I have attached a demo worksheet for you.


    Regards,


    Tom Rowe