Randomly assign people to groups, over three rotations, without repeating groups

Hi everyone,

I have 360 participants that need to be randomly assigned to three sessions. They all need to attend each Topic once. There are seven sessions available within each Topic. I've attached a table with all the information. I was able to create an array formula to randomly assign each participant to a session for the first round but I am not sure how I can make sure they don't attend the same topic twice.

The intended results would be similar to the attached Round 1 - Group Generator sheet except it would be for all three rounds with each person having attended all three topics

Any help would be really appreciated.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Not fully tested, but insert two more columns to the right of column B, and then try these array formulae:

=IFERROR(INDEX($E$2:$E$22,LARGE((COUNTIF($C$1:C1,Teams)<>Members)*IF($E$2:$E$22<>B2,MATCH(ROW(Teams),ROW(Teams))),RANDBETWEEN(1,SUM(--(COUNTIF($C$1:C1,Teams)<>Members))))),"")

=IFERROR(INDEX($E$2:$E$22,LARGE((COUNTIF($D$1:D1,Teams)<>Members)*IF($E$2:$E$22<>B2,IF($E$2:$E$22<>C2,MATCH(ROW(Teams),ROW(Teams)))),RANDBETWEEN(1,SUM(--(COUNTIF($D$1:D1,Teams)<>Members))))),"")

Where E refers to what is currently in C, etc.

Originally Posted by Gioletsgo

Thank you! I have made some changes and attached excel sheets to make it easier. Please let me know if this is alright?

Thank you

I answered before you posted - please let me know if what I suggested is OK or not.

Yes, and it was a guess, I have to confess! I am not sure quite how to refine it to work properly - someone else may know.

Maybe you can adapt it.