How to group my student data using Excel

[dstdst123]

Hi

I have 16 female, 4 male students.
All these 20 students comes from different majors.
12 Physic, 4 Chemistry and 4 Biology.


I want to group them into 4 teams (each team 5 students).

Priority #1:
To ensure at least 1 male student assigned to each team
Priority #2:
Distribute them equally between Physic, chem, and biology
(i.e. each team should have a mix of majors)

Questions:
1) Do I need to write programmes in Excel 2003?

2) Can pivot table help? How to do it in my case?

Thanks a lot.

dstdst123

 

[Don Guillett Excel MVP]

Hi

I have 16 female, 4 male students.
All these 20 students comes from different majors.
12 Physic, 4 Chemistry and 4 Biology.

I want to group them into 4 teams (each team 5 students).

Priority #1:
To ensure at least 1 male student assigned to each team
Priority #2:
Distribute them equally between Physic, chem, and biology
(i.e. each team should have a mix of majors)

Questions:
1) Do I need to write programmes in Excel 2003?

2) Can pivot table help? How to do it in my case?

Thanks a lot.

dstdst123

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Reeza]

Hi

I have 16 female, 4 male students.
All these 20 students comes from different majors.
12 Physic, 4 Chemistry and 4 Biology.

I want to group them into 4 teams (each team 5 students).

Priority #1:
To ensure at least 1 male student assigned to each team
Priority #2:
Distribute them equally between Physic, chem, and biology
(i.e. each team should have a mix of majors)

Questions:
1) Do I need to write programmes in Excel 2003?

2) Can pivot table help? How to do it in my case?

Thanks a lot.

dstdst123

If you need to repeat or automate this procedure then you'll need to
program

otherwise,

Assign them each a random number, using rand() function.
Copy and paste values to fix them.

Sort the list of students by gender, subject, random variable.

Assign one male to each group, using the random number order then
assign the rest so that there is 3 phys and 1 bio, 1 chem to each
group, in the order of the list.