Forum Discussion
Solved
How to have a list of Random Names
Hello, I need some help on using the function Random (not really sure if it the correct one). I have a list of process names to be reviewed by different set of personnels. Example: In column A, I have a list of 26 processes, these processes will be check by different set of personnel in a month (or 4 weeks) depending on the frequency. We only have 43 personnel that will do the checking so some names will be repeated, and some names will have only one assignment. Is there way in excel to automatically fill names every week (in random).
So due to the enormous number of possible requirements and specifics, the RAND functions can't anticipate every situation. There is a great quote something like, "Random numbers are far too imporatant to be left to chance." We often SAY we want it random but really have a lot of constraints to prevent things that would naturally occur if things were truely left to chance. For example in your case you have 26 processes and 43 personnel and some names will be repeated. So based on your grid it looks like you want a name in each week of the month for 26 processes so 104 spots for 43 names which is just over 2x. So what restraints are required? Does every name have to be used 2x and a few 3x but never more than 3x? Left to chance you will have some used only 1x or not at all and others could be used 4 or more times. So there are many ways to 'randomly select' but may not meet your needs:
a) random pick for each slot. simply =RANDARRAY(26,4,1,43,1) will give you a grid of integers for your personnel #1-43 but has no guarantee on frequencies. For example:
in that example I don't see any #2 or 4
Furthermore, you will see things like #41 is 2x next to each other in that 1st column, is that acceptable? Any given column is only 26x so is ANY repeat in a single column ok?
b) another approach might be to use an array of RAND numbers to SORT the list of personnel. So something like: =SORTBY( [list of personnel or numbers], RANDARRAY( 43,1) ) but that is only 1x of your list. Then you could loop through that list but that is no longer random. If you simple 'stack' the list 3x and do the same trick you aren't guaranteed to get every item at least 2x but won't get any more than 3x and you may still get the same item in the same column or even next to itself. You could do that rand-sort for each column to guarantee you don't get 2x in same column but over the 4 columns you can get up to 4x and other may get pick none.
So there are lots of 'tricks' you can do but you need to be clear about any requirements/restrictions.
Next you need to realize that RAND functions are 'volatile' and will recalculate any and EVERY time a change is made on the worksheet. Therefore to keep your values you need to copy and paste-values to freeze or lock those values. Alternatively I created a pseudo-random function with a seed input (technically all random functions are pseudo-random) so you can pick a seed based on the month value (e.g. use the first of the month 2025-01-01) and the 'random' number it output will be the same everytime for that seed. I know that doesn't sound random but let's say you use the date*100+ID# and then for each ID# for that date will give you a different random number and that list of random numbers for all the IDs on that day could be used to create the sort list for that day. Change the day and the list is a new random sequence. Good news is that given a particular seed it won't change until you change that seed (i.e. update the month). This may be overkill for you however.
So due to the enormous number of possible requirements and specifics, the RAND functions can't anticipate every situation. There is a great quote something like, "Random numbers are far too imporatant to be left to chance." We often SAY we want it random but really have a lot of constraints to prevent things that would naturally occur if things were truely left to chance. For example in your case you have 26 processes and 43 personnel and some names will be repeated. So based on your grid it looks like you want a name in each week of the month for 26 processes so 104 spots for 43 names which is just over 2x. So what restraints are required? Does every name have to be used 2x and a few 3x but never more than 3x? Left to chance you will have some used only 1x or not at all and others could be used 4 or more times. So there are many ways to 'randomly select' but may not meet your needs:
a) random pick for each slot. simply =RANDARRAY(26,4,1,43,1) will give you a grid of integers for your personnel #1-43 but has no guarantee on frequencies. For example:
in that example I don't see any #2 or 4
Furthermore, you will see things like #41 is 2x next to each other in that 1st column, is that acceptable? Any given column is only 26x so is ANY repeat in a single column ok?
b) another approach might be to use an array of RAND numbers to SORT the list of personnel. So something like: =SORTBY( [list of personnel or numbers], RANDARRAY( 43,1) ) but that is only 1x of your list. Then you could loop through that list but that is no longer random. If you simple 'stack' the list 3x and do the same trick you aren't guaranteed to get every item at least 2x but won't get any more than 3x and you may still get the same item in the same column or even next to itself. You could do that rand-sort for each column to guarantee you don't get 2x in same column but over the 4 columns you can get up to 4x and other may get pick none.
So there are lots of 'tricks' you can do but you need to be clear about any requirements/restrictions.
Next you need to realize that RAND functions are 'volatile' and will recalculate any and EVERY time a change is made on the worksheet. Therefore to keep your values you need to copy and paste-values to freeze or lock those values. Alternatively I created a pseudo-random function with a seed input (technically all random functions are pseudo-random) so you can pick a seed based on the month value (e.g. use the first of the month 2025-01-01) and the 'random' number it output will be the same everytime for that seed. I know that doesn't sound random but let's say you use the date*100+ID# and then for each ID# for that date will give you a different random number and that list of random numbers for all the IDs on that day could be used to create the sort list for that day. Change the day and the list is a new random sequence. Good news is that given a particular seed it won't change until you change that seed (i.e. update the month). This may be overkill for you however.
Sorry, I’m not familiar with pseudo-random functions, but your suggestion seems like a good starting point. I haven’t tried it yet. Thank you very much for your response.
