Distributing records to groups so that the groups are as similar as possible

I've run into a bit of a problem that I'll try to explain with a hypothetical scenario.

I have 100 records that I would like to place into 20 groups of 5 based on 3 factors. The factors are gender (M/F), program (5 programs) and activity level (3 levels). I’d like each of the 20 groups to be as similar as possible across those factors. I guess I’m not really grouping the factors but distributing them as evenly as possible across the different groups.

If all of those factors had equal numbers (50 males/50 females, 20 from each program and ~33 at each activity level) each group would have one person from each of the 5 programs, 2 or 3 males and 1 or 2 from each activity level. In reality the data won’t be evenly distributed though.

Does anyone have any suggestions as to what tools I could use to assign people to groups to get as even a spread as possible across the three factors?

Thanks in advance.

best,

DHB.

Best Practices

Help

Data Investigation

Accepted answers

DavidP

Ok, here are my thoughts so far.

Sort the data by Gender Code, Program Code and Activity Level. This means that sequential students will be the most similar based on these criteria.

Then create 6 tiles each with 60 records. Each record in a tile has a sequence number, so there will be 6 number 1's, 6 number 2's, 6 number 3's, etc up to 6 number 60's.

Group all the number 1's together in a group, number 2's in a group, etc.

This should give you 60 groups as similar as the sorting allows.

You could go a little more sophisticated if you want give certain criteria more weighting so that you end up with a ranking score for each student, then sort by ranking and do the same as above.

Let me know if this works for you.

equal records grouping.png

equal groups.yxmd

All comments

DavidP

I think the Tile tool might be useful here, but you’ll have to play around with it. I’ll have go when I get to my pc and let you know.

DHB

Thanks for the tip David I'll do some tests with that.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703