Using INDEX/RAND to fill cells with no duplicate values

[Tony Vargo]

Hi all.

I’m hoping someone can help me with a problem I’m having.

I have a sheet with a list of staff, and I want to randomly assign them duties. Using the following formula it’s easy enough to select staff for a certain task:

Please Login or Register  to view this content.

However, I want to make sure the person isn’t chosen more than once. So for example, if someone is picked for a certain duty, I don’t want the formula picking that person again and assigning them a second or third duty. I’ve attached an example sheet to explain it a bit better!

Thanks in advance guys,

Tony.

[:) Sixthsense :)]

Both Rand() or RandBetween() approach will produce duplicate

[Tony Vargo]

Thanks for the reply. Is there a way around it with VBA perhaps?

[Max, Singapore]

One formulas play to deliver it ....
a. Set up a formulated area to "clear" duplicates (if any) from the names listed under Task 2 and 3
depending on the possible names assigned in D5 and E5

For Task 2
In M6: =IF(K6=D$5,"",ROWS($1:1))
In N6: =IF(ROWS($1:1)>COUNT($M$6:$M$9),"",INDEX($K$6:$K$9,SMALL($M$6:$M$9,ROWS($1:1))))
Copy M6:N6 down to N9

For Task 3
In O6: =IF(OR(L6=D$5,L6=E$5),"",ROWS($1:1))
In P6: =IF(ROWS($1:1)>COUNT($O$6:$O$10),"",INDEX($L$6:$L$10,SMALL($O$6:$O$10,ROWS($1:1))))
Copy O6:P6 down to P10

b. Now you can use these to randomize it for the expected results
In D5: =INDEX($J$6:$J$12,RANDBETWEEN(1,COUNTA($J$6:$J$12)))
In E5: =INDEX($N$6:$N$9,RANDBETWEEN(1,COUNT($M$6:$M$9)))
In F5: =INDEX($P$6:$P$10,RANDBETWEEN(1,COUNT($O$6:$O$10)))
----------------------------------
Success? Wave it, click on the little star at the bottom left of my responses

[Tony Vargo]

Max that's brilliant. Works perfectly, thanks a lot.

[Max, Singapore]

welcome, glad to hear