+ Reply to Thread
Results 1 to 20 of 20

Random Sequence?

  1. #1
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Random Sequence?

    I have a group of 10 rooms that I am trying to make paths through. Each person has to go to 4 rooms, but it doesn't matter which rooms or which sequence they go in. But at any given time there shouldn't be more than 10 people in a room. Is there a way in Excel to create a random set of rows and columns to do this? I was thinking each person is on a row, then a column for each time block with the room numbers (or letters, doesn't matter) in the column assigned randomly. But they can't repeat, and they have to be evenly distributed. I'm trying to avoid doing this by hand if possible as there are 120 people.

    Thank you!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Random Sequence?

    Welcome to the forum!

    Perhaps you could attach a workbook? Manually mock-up the sort of thing that you would like Excel to generate automatically showing where your manual inputs would be and where the automatic bits would be.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Sure. In the attached workbook I've split it into 12 possible rooms, then just given the people a random sequence. The difference is that in the example I used 12 people, but in reality I'd have 120 people, so there are parameters for how many of each letter can be in a room at once.
    Attached Files Attached Files

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Random Sequence?

    I have a group of 10 rooms that I am trying to make paths through. Each person has to go to 4 rooms, but it doesn't matter which rooms or which sequence they go in. But at any given time there shouldn't be more than 10 people in a room.
    if there are 120 people and 10 rooms, then there are 12 in a room, not 10

  5. #5
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Yes, I know, I haven't actually decided if I'm going to do 12 or 10 rooms. I'd like 12, but it might have to be 10.

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Random Sequence?

    this is main formula
    =AGGREGATE(15,6,COLUMN($A$1:INDEX($1:$1,$A$1))/ISNA(MATCH(COLUMN($A$1:INDEX($1:$1,$A$1)),$P3:P3,)),RANDBETWEEN(1,$A$1-COUNT($P3:P3)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Thank you, but there must be an error somewhere in that sheet since every cell shows up as the "NAME" error. I think it's safe to say we can use 12 rooms for this purpose. Thank you so much!

  8. #8
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Random Sequence?

    no error
    just mso2007
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Thank you! The only problem I'm seeing is that there are more people in some of the rooms at any time than there should be. Each person only has to go to 4 rooms, they will not go to all of the rooms. It can be any 4 of the 12 rooms in any order, which should mean at any given time there are not more than 10 people per room.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Random Sequence?

    Latin square?

    B
    C
    D
    E
    F
    1
    Room Round1 Round2 Round3 Round4
    2
    1 39 24 60 12
    3
    1 26 22 63 119
    4
    1 55 105 42 76
    5
    1 113 50 48 83
    6
    1 5 40 36 25
    7
    1 46 93 68 35
    8
    1 78 107 27 38
    9
    1 89 30 59 44
    10
    1 29 20 37 7
    11
    1 85 65 120 84
    12
    2 51 31 92 41
    13
    2 30 7 57 108
    14
    2 8 25 69 62
    15
    2 7 28 26 106
    16
    2 95 29 19 30
    17
    2 105 70 96 18
    18
    2 66 84 29 36
    19
    2 22 4 78 42
    20
    2 36 33 7 15
    21
    2 56 115 76 48
    22
    3 119 42 101 118
    23
    3 25 88 15 120
    24
    3 48 17 10 4
    25
    3 102 94 25 85
    26
    3 109 76 118 99
    27
    3 52 81 2 77
    28
    3 83 34 4 105
    29
    3 110 6 73 37
    30
    3 115 111 18 17
    31
    3 88 58 72 13
    32
    4 44 108 24 49
    33
    4 112 36 30 69
    34
    4 64 118 74 97
    35
    4 11 16 111 87
    36
    4 59 57 23 24
    37
    4 18 95 116 89
    38
    4 15 72 106 6
    39
    4 1 43 105 115
    40
    4 9 41 43 11
    41
    4 93 23 77 60
    42
    5 82 85 62 66
    43
    5 69 15 108 110
    44
    5 72 1 55 56
    45
    5 68 77 113 90
    46
    5 4 32 107 96
    47
    5 16 67 66 5
    48
    5 71 59 93 39
    49
    5 90 61 83 1
    50
    5 107 44 39 86
    51
    5 33 90 28 72
    52
    6 47 64 41 103
    53
    6 32 69 44 45
    54
    6 50 87 17 34
    55
    6 45 110 49 19
    56
    6 42 96 38 21
    57
    6 62 120 110 29
    58
    6 2 11 115 50
    59
    6 106 55 119 109
    60
    6 101 38 94 54
    61
    6 81 103 11 14
    62
    7 3 63 114 31
    63
    7 70 62 45 95
    64
    7 65 52 13 68
    65
    7 94 79 88 65
    66
    7 43 82 70 111
    67
    7 91 100 81 23
    68
    7 99 10 80 78
    69
    7 117 91 52 93
    70
    7 35 60 90 58
    71
    7 20 113 75 28
    72
    8 100 104 103 3
    73
    8 114 98 5 102
    74
    8 103 97 16 114
    75
    8 87 5 112 8
    76
    8 57 26 3 53
    77
    8 13 2 56 113
    78
    8 73 109 64 104
    79
    8 34 8 32 70
    80
    8 92 102 8 82
    81
    8 61 92 34 43
    82
    9 86 49 12 91
    83
    9 27 39 35 79
    84
    9 54 117 65 46
    85
    9 111 66 95 112
    86
    9 58 9 1 2
    87
    9 120 13 6 20
    88
    9 14 114 87 92
    89
    9 63 78 98 101
    90
    9 98 27 40 94
    91
    9 31 101 102 74
    92
    10 75 48 99 22
    93
    10 60 51 61 9
    94
    10 97 80 67 98
    95
    10 38 86 79 117
    96
    10 80 71 46 27
    97
    10 77 14 50 61
    98
    10 10 89 71 107
    99
    10 24 53 51 47
    100
    10 28 83 22 55
    101
    10 53 119 31 64
    102
    11 21 116 117 71
    103
    11 79 12 58 52
    104
    11 104 99 97 63
    105
    11 23 3 14 51
    106
    11 118 21 54 80
    107
    11 19 37 100 57
    108
    11 12 47 9 81
    109
    11 40 35 33 88
    110
    11 96 45 86 116
    111
    11 67 46 84 40
    112
    12 17 112 89 32
    113
    12 76 18 21 10
    114
    12 49 73 47 100
    115
    12 6 56 109 75
    116
    12 108 106 53 73
    117
    12 41 74 82 16
    118
    12 84 68 20 33
    119
    12 74 54 85 67
    120
    12 116 19 91 59
    121
    12 37 75 104 26
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    I must be missing something, I'm not sure how that solves the problem? The first column has the correct number of people in rooms, but the columns titled "Round 1 - 4" have numbers way too high.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Random Sequence?

    They are people numbers, 1 to 120, in each of four rounds. Room numbers are in the first column.

  13. #13
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Oh! Now I see. The problem I would have would be that I need to be able to know what path each person takes so I can give them the clues to get to each place. I'm not immediately seeing how I would do that in this format. This would be perfect if I just needed a roster of each room for each round, but I need to individualize the clues by person. Thank you!

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Random Sequence?

    Simple INDEX/MATCH from the previous table:

    H
    I
    J
    K
    L
    1
    Name Round1 Round2 Round3 Round4
    2
    1 4 5 9 5
    3
    2 6 8 3 9
    4
    3 7 11 8 8
    5
    4 5 2 3 3
    6
    5 1 8 8 5
    7
    6 12 3 9 4
    8
    7 2 2 2 1
    9
    8 2 8 8 8
    10
    9 4 9 11 10
    11
    10 10 7 3 12
    12
    11 4 6 6 4
    13
    12 11 11 9 1
    14
    13 8 9 7 3
    15
    14 9 10 11 6
    16
    15 4 5 3 2
    17
    16 5 4 8 12
    18
    17 12 3 6 3
    19
    18 4 12 3 2
    20
    19 11 12 2 6
    21
    20 7 1 12 9
    22
    21 11 11 12 6
    23
    22 2 1 10 10
    24
    23 11 4 4 7
    25
    24 10 1 4 4
    26
    25 3 2 3 1
    27
    26 1 8 2 12
    28
    27 9 9 1 10
    29
    28 10 2 5 7
    30
    29 1 2 2 6
    31
    30 2 1 4 2
    32
    31 9 2 10 7
    33
    32 6 5 8 12
    34
    33 5 2 11 12
    35
    34 8 3 8 6
    36
    35 7 11 9 1
    37
    36 2 4 1 2
    38
    37 12 11 1 3
    39
    38 10 6 6 1
    40
    39 1 9 5 5
    41
    40 11 1 9 11
    42
    41 12 4 6 2
    43
    42 6 3 1 2
    44
    43 7 4 4 8
    45
    44 4 5 6 1
    46
    45 6 11 7 6
    47
    46 1 11 10 9
    48
    47 6 11 12 10
    49
    48 3 10 1 2
    50
    49 12 9 6 4
    51
    50 6 1 10 6
    52
    51 2 10 10 11
    53
    52 3 7 7 11
    54
    53 10 10 12 8
    55
    54 9 12 11 6
    56
    55 1 6 5 10
    57
    56 2 12 8 5
    58
    57 8 4 2 11
    59
    58 9 3 11 7
    60
    59 4 5 1 12
    61
    60 10 7 1 4
    62
    61 8 5 10 10
    63
    62 6 7 5 2
    64
    63 9 7 1 11
    65
    64 4 6 8 10
    66
    65 7 1 9 7
    67
    66 2 9 5 5
    68
    67 11 5 10 12
    69
    68 5 12 1 7
    70
    69 5 6 2 4
    71
    70 7 2 7 8
    72
    71 5 10 10 11
    73
    72 5 4 3 5
    74
    73 8 12 3 12
    75
    74 12 12 4 9
    76
    75 10 12 7 12
    77
    76 12 3 2 1
    78
    77 10 5 4 3
    79
    78 1 9 2 7
    80
    79 11 7 10 9
    81
    80 10 10 7 11
    82
    81 6 3 7 11
    83
    82 5 7 12 8
    84
    83 3 10 5 1
    85
    84 12 2 11 1
    86
    85 1 5 12 3
    87
    86 9 10 11 5
    88
    87 8 6 9 4
    89
    88 3 3 7 11
    90
    89 1 10 12 4
    91
    90 5 5 7 5
    92
    91 7 7 12 9
    93
    92 8 8 2 9
    94
    93 4 1 5 7
    95
    94 7 3 6 9
    96
    95 2 4 9 7
    97
    96 11 6 2 5
    98
    97 10 8 11 4
    99
    98 9 8 9 10
    100
    99 7 11 10 3
    101
    100 8 7 11 12
    102
    101 6 9 3 9
    103
    102 3 8 9 8
    104
    103 8 6 8 6
    105
    104 11 8 12 8
    106
    105 2 1 4 3
    107
    106 6 12 4 2
    108
    107 5 1 5 10
    109
    108 12 4 5 2
    110
    109 3 8 12 6
    111
    110 3 6 6 5
    112
    111 9 3 4 7
    113
    112 4 12 8 9
    114
    113 1 7 5 8
    115
    114 8 9 7 8
    116
    115 3 2 6 4
    117
    116 12 11 4 11
    118
    117 7 9 11 10
    119
    118 11 4 3 3
    120
    119 3 10 6 1
    121
    120 9 6 1 3

  15. #15
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    But in your example, person one goes to room 5 in both round 2 and round 4. They can't repeat rooms.

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Random Sequence?

    Don't understand why, but OK.

  17. #17
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    It's an event I am planning, a scavenger hunt of sorts. So each room has a task, and each person has to complete the task to move to the next room. So they can't do the same task twice.

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Random Sequence?

    What about putting the rooms in some type pattern but randomizing the names?
    In the attached copy of the file the 12 rooms are listed in row 1. The numbers in the range B2:M13 indicate the sequence in which a person will enter a room, for example the person whose name occupies A2 will first go to room1, then room4 then room7 and finally room10. Note that columns B:M may be hidden for aesthetic purposes.
    The range O2:R13 displays the order using: =INDEX($B$1:$M$1,MATCH(O$1,$B2:$M2,0))
    The names are placed in A15:A26, and B15:B26 are populated using: =RAND()
    A2:A13 are then populated using: =INDEX(A$15:A$26,MATCH(SMALL(B$15:B$26,ROW(1:1)),B$15:B$26,0))
    Test by pressing the F9 key.
    After you have randomized the names select A2:A13 and copy (Ctrl + c) then, while A2:A13 are still selected, paste special values (Ctrl + Alt + v to bring up paste special, choose values and select OK).
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  19. #19
    Registered User
    Join Date
    10-20-2018
    Location
    Boston, MA
    MS-Off Ver
    2007
    Posts
    10

    Re: Random Sequence?

    Interesting way of doing that. Because my event is in a few days I ended up somewhat doing it by hand - I made a list that had 10 of each letter in a column, then used =RAND() in the next column to randomly sort the first column, then just did that 4 more times. I then used a COUNTIF to check for any duplicate rooms. With only 120 names it wasn't terribly time consuming. Not very elegant, but it got the job done! Thank you all for your help, I truly appreciate it!

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Random Sequence?

    Glad that you got the issue resolved. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. printing random pages with page numbers still in sequence
    By steve111 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2014, 02:10 PM
  2. Make a countdown sequence with random intervals
    By Orthobill13 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-12-2012, 09:38 AM
  3. Generate random sequence of numbers in excel
    By excelvb in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-06-2011, 06:41 AM
  4. Random number sequence
    By tghcogo in forum Excel General
    Replies: 3
    Last Post: 04-10-2007, 04:25 PM
  5. I WANT TO ADD A TWO RANDOM CHARACTER SEQUENCE TO A SET OF ROWS
    By XLSUSER in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2005, 06:10 AM
  6. Random Sequence with no Duplication
    By nebb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2005, 04:05 PM
  7. Replies: 6
    Last Post: 08-04-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1