Randomly Assign to Groups of Equal Size, but groups already have members

**RosaL123** · 04-26-2023, 06:33 AM

Hi Excel Forum,

I would like to randomly assign participants to groups and I am doing this by:

1) Using Rand() to give each participant a random number
2) Rank the random numbers
3) Divide the rank by the group size
4) Use the ceiling function for the (Rank\Group Size) value

The problem however is that the groups already have participants, so there isn't the same amount of spaces available in each. E.g;

Group 1 has 14 participants already and 36 spaces available
Group 2 has 2 participants already and 48 spaces available
Group 3 has 23 participants already and 27 spaces available
Group 4 has 7 participants already and 43 spaces available

Is there a way we can randomly allocate to the available spaces in each group, so that each group only has 50 members?

Thank you

Rosa

**AliGW** · 04-26-2023, 06:34 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.