Help, regarding random number, with exclusion and without repeat.

[FluffyTee]

Hi guys, im new to excel.

I ve been trying to create a roster maker that randomly picks staff and into stations.
There are few rules that need to be fulfilled...
There are 15 stations, 15 staff. but only minimum 6 prioritized stations need to be filled per day(allowing 7 to be away).
Some station are also restricted to certain staff of certain experience or health limitation.

Ive made use of

what would be the best recommended method to do this?
Ive been using

=IF((S2-T2)>9,(SMALL(IF(V2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(W2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(X2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(Y2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(Z2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(AA2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(I2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(J2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(N2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(F2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(E2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(H2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(K2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(C2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",IF(D2={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},"",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))))))))))))))),RANDBETWEEN(1,(S2-(9+T2))))),0)

It was getting a lil messy. was wondering whats the best way to to this.

Many thanks in advance.

[Kaper]

In my opinion there is no generally easy way to obtain your required result just with formulas
To make it properly you shall think rather about some VBA code.

Particular solution would depend on exact requirements and limitations.

So consider posting an attachment with sample data.




To attach a sample workbook:
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.