No duplicates in multiples lists

New Contributor

Hi everyone,


I am working on a project at work. We have 6 uniques jobs at work and different people are qualified to do certain tasks and others are not. So far I have created 6 random lists of who should do this job each using a variant of =INDEX($L$36:$L$42,RANDBETWEEN(1,COUNTA($L$36:$L$42)),1), with different cell values. However, this can give duplicates, and I need a certain amount of people to do each of these jobs. 

Also with the other tasks, some people are qualified to do multiple jobs but cannot do it at the same time. I need a way so no name is repeated, but all slots are filled. 

Also, if that is able to be done, as I am working on multiple days throughout the week, I would like it that if they have appeared on 3 of the day lists, to not be featured on anymore.


Thank you very much in advance!



3 Replies



I'm not sure I'll be able to help, although I've done some less ambitious mixing and matching based on random sequences.... but you would be more likely to get directly relevant help if you were to post a copy of the spreadsheet you have developed so far. Otherwise you're asking people to create from scratch a working model (which may or may not meet your needs) or just give abstract guidance.


You should make sure there is no confidential or private info in any working spreadsheet--so replace real names with movie character names, real job/task labels with fictitious ones, unless they're already pretty generic. It's just a lot easier for somebody to help you if we start with some semblance of the real situation.

@mathetes Thank you for your reply,


See the example spreadsheet attached.



Wow....that's beyond me at this point, and I just don't have the time to dig around. My hope is that another with more expertise in Excel will join the thread and give you the help you're looking for. Thanks for creating such a good working example.