Randomly assigning a list of participants to equal-sized groups

[ijkiv]

Hi all, 

I have  a list of participants, which I would like to assign to equal-sized groups (3-4). Assignment should be random. The only requirement is that participants from Asian countries should not be assigned to the same group as participants from the Americas (due to the substantial time zone differences). 

I am attaching a data set. I am curious how you would approach it.

I have created a solution in which I have opted for 'Tile' tool. I will share it a later point. 

[danilang · Accepted solution]

Hi @ijkiv 

Here's one way you can go about it.

Start off by calculating nGroups based on a max group size of 4.  Then split the data into 3 streams, one for each of Europe, Asia and the Americas.  In each of these, randomize the data, and assign an OrderID.  For Europe and Asia calculate the group for each record using mod([OrderID],[Ngroups]).  Since the records are in random order they get assigned to random groups.  The Americas are assigned in a similar way except the maximum group from Asia is used as an offset to keep the 2 groups separate.  Union all the records together and clean up.

Note: that this method only works because I took advantage of a characteristic of your input data, where N(Asia)+N(Americas) < N(Groups).  If the sum had been greater, the workflow would have needed some modifications to calculate the maximum group number to assign the Asia records so that the Americas records could be assigned to the remaining groups without causing the mod function to wrap back around to 0

Dan