Need Help in Grouping

[harhar0506]

I would like to put 15 students, 7 boys, 8 girls RANDOMLY into 5 groups with at least one boy or one girl in each group. Please see the attachment.

Could anyone give me any great idea?

Thanks! ^_^

[NickyC]

it's a bit clunky but this macro should do it on your worksheet example

Please Login or Register  to view this content.

[NickyC]

another way to do it without vba is simply to repeatedly randomly sort the whole group into sets until you get the desired outcome. The attached shows one way to do it - simply recalc the sheet repeatedly until the value in cell L1 is "TRUE"

[harhar0506]

Dear NickyC,

Thanks very much! It works perfectly!

Cheers,
Harvey

[NickyC]

Hi Harvey

I have quite enjoyed your puzzle!

I think a better method than the previous two I posted is this, as it does not use VBA or rely on iterating until the right answer is found, so it could be used for much bigger groups and less evenly matched numbers of females and males, without getting slow or clunky.

Basically, the method is this:

1. create two separate lists, one of females and one of males, in columns A and E
2. in the adjacent columns (B and F), insert the random number formula to generate a random number against each name that will change every time you recalculate the sheet
3. adjacent to these (columns C and G), use the =Rank formula to rank your random numbers. This should generate a unique set of random numbers between one and the number of people in your list.
4. To select one random female and one random male for each group, use offset and match to select the males and females with 1,2,3,4, and 5 in their “rank” column to groups 1 to 5. This will allocate one random female and one random male to each group, with no duplicates.
5. To randomly allocate the remaining people, create another table with all the students’ names (male and female). Adjacent to that, create another random number column that assigns a value of 0 to every name ranked 1 to 5 your first two tables, and a random number to any other person. This should put a random number great than zero next to any person who has not already been allocated to a group.
6. Use a similar offset/match formula to allocate the remaining students to a group.

This probably sounds horribly complicated, but should be clearer with the attached example worked out in the “calc” sheet. Every time you recalculate this sheet it should generate a new allocation of students into groups that meets your criteria in the green table in cells J2:M7

[onrainbow]

Please refer

Press Ctrl, Shift, Enter Key

c2 in Sampling

=INDEX($E$3:$E$7,MATCH(RAND()*(SUMIF($F$2:$G$2,B2,$F$8:$G$8)-COUNTIF(B$1:B1,B2)),MMULT(1*(ROW($E$3:$E$7)>TRANSPOSE(ROW($E$3:$E$7))),OFFSET($E$3:$E$7,0,MATCH(B2,$F$2:$G$2,0))-COUNTIFS(C$1:C1,$E$3:$E$7,B$1:B1,B2)),1))