Unique Random Categorisation with non-duplicates

[efsk]

Dear all,

I have a list of 28 unique IDs. e.g. 4101, 4102, 4103, etc.

These 28 IDs will be paired to take an examination with 4 different instructors at the same time.

How do I randomly pair them for each examiner?
The conditions for the pairing is that at any one time there would be 4 pairs with 4 examiners respectively whose IDs will not overlap, and all 28 IDs will have to meet each instructor for the exam.

I tried using the RAND() function, and RANDBETWEEN() function, but I had problems with duplicates both across 4 examiners and within each examiner.

At the same time, I would like to be able to randomly generate them with a click of a button, if that is possible.

I have attached my worksheet for reference.

I am still a beginner when it comes to Excel.

Your assistance will be much appreciated.

Thank you.

[JeteMc]

Hello efsk and Welcome to Excel Forum.
In the attached proposal, the randomization is in which ID is assigned which rank number in column C.
Column B is populated using: =RAND()
Column C ranks the random numbers in column B using: =RANK.EQ(B4,B$4:B$31)
The 'tables' at the top of the sheet show which rank number will be assigned to which examination at which time.
The 'tables' at the bottom of the sheet are populated using: =INDEX($A$4:$A$31,MATCH(K3,$C$4:$C$31,0))
Note that each of the examination 'tables' has conditional formatting applied to highlight duplicates. (should not activate, but just in case)
To activate the randomization press the F9 key.
If you want to stop the randomization process then select cells C4:C31 > copy > (while the range C4:C31 is still selected) paste values
Let us know if you have any questions.

[efsk]

Dear JeteMC,

Awesome!

I really appreciate your help. I will be looking into it to better understand the formulas and how they are connected so that I could use it for a different set when the number of individuals are different.

Thank you for your kind assistance.

Regards.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. A good way to see what a formula is doing is to select a cell containing the formula then run the Evaluate Formula feature. I hope that you have a blessed day.