School Pupil Council, EXCEL Help Needed

ChristianCounsellor

Evening All,


We are trying to start a school council within out year 7 class. it has 8 boys and 11 girls. We wish to have three of each sex in the council. within excel I am able to work out the combinations of the council but what I would like to see is a list of all possible councils. this way I would be able to go through it ensuring that people who are disruptive together would be kept apart plus would also help to see how many "dream teams" I could have.


How could I get Excel to give me a list of the councils naming the children that make up each combination?


Thank you

Jivesinger

I make it 9,240 combinations - that's a long list to sift through by eye to work out the good and bad ones!

eddddy

If you're saying that you want to work out all possible combinations of 3 boys and 3 girls, out of a group of 8 boys and 11 girls - then I agree with Jivesinger - 9,240 possible combinations.

I calculated it as follows:

8! / (3!*4!) = 56
11! / (3!*8!) = 165
56*165 = 9240

But I suspect you really want something more sophisticated than that. e.g Having identified "Dream Team 1", you want to find "Dream Team 2" out of the remaining 5 boys and 8 girls.

It's very complicated!

ChristianCounsellor

Well it is not that many as it is looking at the sets of three boys and the sets of three girls. I will be able to add filters to block some I already know to keep apart. I am adept at doing the filters on Excel but not in how to make it give me the list I need. My own school report in excel is could do better lol

eddddy

ChristianCounsellor wrote: »

Well it is not that many as it is looking at the sets of three boys and the sets of three girls.
...

You probably need to explain more clearly what you want to do.

To explain the calculation in a bit more detail:

If you consider just the 8 boys - you can get 56 different groups of 3 boys out of that

If you consider just the 11 girls - you can get 165 different groups of 3 girls out of that.

Then matching 56 boy groups with 165 girl groups gives you 9,240 possibilities.

Jivesinger

Assuming I've understood the problem correctly, here are all the possible combinations of girls:
Girl One,Girl Two,Girl Three
Girl One,Girl Two,Girl Four
Girl One,Girl Two,Girl Five
Girl One,Girl Two,Girl Six
Girl One,Girl Two,Girl Seven
Girl One,Girl Two,Girl Eight
Girl One,Girl Two,Girl Nine
Girl One,Girl Two,Girl Ten
Girl One,Girl Two,Girl Eleven
Girl One,Girl Three,Girl Four
Girl One,Girl Three,Girl Five
Girl One,Girl Three,Girl Six
Girl One,Girl Three,Girl Seven
Girl One,Girl Three,Girl Eight
Girl One,Girl Three,Girl Nine
Girl One,Girl Three,Girl Ten
Girl One,Girl Three,Girl Eleven
Girl One,Girl Four,Girl Five
Girl One,Girl Four,Girl Six
Girl One,Girl Four,Girl Seven
Girl One,Girl Four,Girl Eight
Girl One,Girl Four,Girl Nine
Girl One,Girl Four,Girl Ten
Girl One,Girl Four,Girl Eleven
Girl One,Girl Five,Girl Six
Girl One,Girl Five,Girl Seven
Girl One,Girl Five,Girl Eight
Girl One,Girl Five,Girl Nine
Girl One,Girl Five,Girl Ten
Girl One,Girl Five,Girl Eleven
Girl One,Girl Six,Girl Seven
Girl One,Girl Six,Girl Eight
Girl One,Girl Six,Girl Nine
Girl One,Girl Six,Girl Ten
Girl One,Girl Six,Girl Eleven
Girl One,Girl Seven,Girl Eight
Girl One,Girl Seven,Girl Nine
Girl One,Girl Seven,Girl Ten
Girl One,Girl Seven,Girl Eleven
Girl One,Girl Eight,Girl Nine
Girl One,Girl Eight,Girl Ten
Girl One,Girl Eight,Girl Eleven
Girl One,Girl Nine,Girl Ten
Girl One,Girl Nine,Girl Eleven
Girl One,Girl Ten,Girl Eleven
Girl Two,Girl Three,Girl Four
Girl Two,Girl Three,Girl Five
Girl Two,Girl Three,Girl Six
Girl Two,Girl Three,Girl Seven
Girl Two,Girl Three,Girl Eight
Girl Two,Girl Three,Girl Nine
Girl Two,Girl Three,Girl Ten
Girl Two,Girl Three,Girl Eleven
Girl Two,Girl Four,Girl Five
Girl Two,Girl Four,Girl Six
Girl Two,Girl Four,Girl Seven
Girl Two,Girl Four,Girl Eight
Girl Two,Girl Four,Girl Nine
Girl Two,Girl Four,Girl Ten
Girl Two,Girl Four,Girl Eleven
Girl Two,Girl Five,Girl Six
Girl Two,Girl Five,Girl Seven
Girl Two,Girl Five,Girl Eight
Girl Two,Girl Five,Girl Nine
Girl Two,Girl Five,Girl Ten
Girl Two,Girl Five,Girl Eleven
Girl Two,Girl Six,Girl Seven
Girl Two,Girl Six,Girl Eight
Girl Two,Girl Six,Girl Nine
Girl Two,Girl Six,Girl Ten
Girl Two,Girl Six,Girl Eleven
Girl Two,Girl Seven,Girl Eight
Girl Two,Girl Seven,Girl Nine
Girl Two,Girl Seven,Girl Ten
Girl Two,Girl Seven,Girl Eleven
Girl Two,Girl Eight,Girl Nine
Girl Two,Girl Eight,Girl Ten
Girl Two,Girl Eight,Girl Eleven
Girl Two,Girl Nine,Girl Ten
Girl Two,Girl Nine,Girl Eleven
Girl Two,Girl Ten,Girl Eleven
Girl Three,Girl Four,Girl Five
Girl Three,Girl Four,Girl Six
Girl Three,Girl Four,Girl Seven
Girl Three,Girl Four,Girl Eight
Girl Three,Girl Four,Girl Nine
Girl Three,Girl Four,Girl Ten
Girl Three,Girl Four,Girl Eleven
Girl Three,Girl Five,Girl Six
Girl Three,Girl Five,Girl Seven
Girl Three,Girl Five,Girl Eight
Girl Three,Girl Five,Girl Nine
Girl Three,Girl Five,Girl Ten
Girl Three,Girl Five,Girl Eleven
Girl Three,Girl Six,Girl Seven
Girl Three,Girl Six,Girl Eight
Girl Three,Girl Six,Girl Nine
Girl Three,Girl Six,Girl Ten
Girl Three,Girl Six,Girl Eleven
Girl Three,Girl Seven,Girl Eight
Girl Three,Girl Seven,Girl Nine
Girl Three,Girl Seven,Girl Ten
Girl Three,Girl Seven,Girl Eleven
Girl Three,Girl Eight,Girl Nine
Girl Three,Girl Eight,Girl Ten
Girl Three,Girl Eight,Girl Eleven
Girl Three,Girl Nine,Girl Ten
Girl Three,Girl Nine,Girl Eleven
Girl Three,Girl Ten,Girl Eleven
Girl Four,Girl Five,Girl Six
Girl Four,Girl Five,Girl Seven
Girl Four,Girl Five,Girl Eight
Girl Four,Girl Five,Girl Nine
Girl Four,Girl Five,Girl Ten
Girl Four,Girl Five,Girl Eleven
Girl Four,Girl Six,Girl Seven
Girl Four,Girl Six,Girl Eight
Girl Four,Girl Six,Girl Nine
Girl Four,Girl Six,Girl Ten
Girl Four,Girl Six,Girl Eleven
Girl Four,Girl Seven,Girl Eight
Girl Four,Girl Seven,Girl Nine
Girl Four,Girl Seven,Girl Ten
Girl Four,Girl Seven,Girl Eleven
Girl Four,Girl Eight,Girl Nine
Girl Four,Girl Eight,Girl Ten
Girl Four,Girl Eight,Girl Eleven
Girl Four,Girl Nine,Girl Ten
Girl Four,Girl Nine,Girl Eleven
Girl Four,Girl Ten,Girl Eleven
Girl Five,Girl Six,Girl Seven
Girl Five,Girl Six,Girl Eight
Girl Five,Girl Six,Girl Nine
Girl Five,Girl Six,Girl Ten
Girl Five,Girl Six,Girl Eleven
Girl Five,Girl Seven,Girl Eight
Girl Five,Girl Seven,Girl Nine
Girl Five,Girl Seven,Girl Ten
Girl Five,Girl Seven,Girl Eleven
Girl Five,Girl Eight,Girl Nine
Girl Five,Girl Eight,Girl Ten
Girl Five,Girl Eight,Girl Eleven
Girl Five,Girl Nine,Girl Ten
Girl Five,Girl Nine,Girl Eleven
Girl Five,Girl Ten,Girl Eleven
Girl Six,Girl Seven,Girl Eight
Girl Six,Girl Seven,Girl Nine
Girl Six,Girl Seven,Girl Ten
Girl Six,Girl Seven,Girl Eleven
Girl Six,Girl Eight,Girl Nine
Girl Six,Girl Eight,Girl Ten
Girl Six,Girl Eight,Girl Eleven
Girl Six,Girl Nine,Girl Ten
Girl Six,Girl Nine,Girl Eleven
Girl Six,Girl Ten,Girl Eleven
Girl Seven,Girl Eight,Girl Nine
Girl Seven,Girl Eight,Girl Ten
Girl Seven,Girl Eight,Girl Eleven
Girl Seven,Girl Nine,Girl Ten
Girl Seven,Girl Nine,Girl Eleven
Girl Seven,Girl Ten,Girl Eleven
Girl Eight,Girl Nine,Girl Ten
Girl Eight,Girl Nine,Girl Eleven
Girl Eight,Girl Ten,Girl Eleven
Girl Nine,Girl Ten,Girl Eleven

And here are the boys:
Boy One,Boy Two,Boy Three
Boy One,Boy Two,Boy Four
Boy One,Boy Two,Boy Five
Boy One,Boy Two,Boy Six
Boy One,Boy Two,Boy Seven
Boy One,Boy Two,Boy Eight
Boy One,Boy Three,Boy Four
Boy One,Boy Three,Boy Five
Boy One,Boy Three,Boy Six
Boy One,Boy Three,Boy Seven
Boy One,Boy Three,Boy Eight
Boy One,Boy Four,Boy Five
Boy One,Boy Four,Boy Six
Boy One,Boy Four,Boy Seven
Boy One,Boy Four,Boy Eight
Boy One,Boy Five,Boy Six
Boy One,Boy Five,Boy Seven
Boy One,Boy Five,Boy Eight
Boy One,Boy Six,Boy Seven
Boy One,Boy Six,Boy Eight
Boy One,Boy Seven,Boy Eight
Boy Two,Boy Three,Boy Four
Boy Two,Boy Three,Boy Five
Boy Two,Boy Three,Boy Six
Boy Two,Boy Three,Boy Seven
Boy Two,Boy Three,Boy Eight
Boy Two,Boy Four,Boy Five
Boy Two,Boy Four,Boy Six
Boy Two,Boy Four,Boy Seven
Boy Two,Boy Four,Boy Eight
Boy Two,Boy Five,Boy Six
Boy Two,Boy Five,Boy Seven
Boy Two,Boy Five,Boy Eight
Boy Two,Boy Six,Boy Seven
Boy Two,Boy Six,Boy Eight
Boy Two,Boy Seven,Boy Eight
Boy Three,Boy Four,Boy Five
Boy Three,Boy Four,Boy Six
Boy Three,Boy Four,Boy Seven
Boy Three,Boy Four,Boy Eight
Boy Three,Boy Five,Boy Six
Boy Three,Boy Five,Boy Seven
Boy Three,Boy Five,Boy Eight
Boy Three,Boy Six,Boy Seven
Boy Three,Boy Six,Boy Eight
Boy Three,Boy Seven,Boy Eight
Boy Four,Boy Five,Boy Six
Boy Four,Boy Five,Boy Seven
Boy Four,Boy Five,Boy Eight
Boy Four,Boy Six,Boy Seven
Boy Four,Boy Six,Boy Eight
Boy Four,Boy Seven,Boy Eight
Boy Five,Boy Six,Boy Seven
Boy Five,Boy Six,Boy Eight
Boy Five,Boy Seven,Boy Eight
Boy Six,Boy Seven,Boy Eight

You can copy the girls list into a single Excel column, then use the "Text to Columns" feature to make it into three columns of girls names. Use the Delimited option with comma as the delimiter.

Then do the same with the boys, but once you've got their list into three columns, copy these and use Paste Special | Transpose to make three rows.

Then you can make a grid of combinations with the boys' names along the top, and the girls' names down the side.

Go to the first intersecting cell (in cell D4 or thereabouts) and use Windows | Freeze Panes to keep both the boys' and girls' names visible as you move to the far extremes of the grid.

Then use Search and Replace to change "Boy One" into your first boy's name, etc.

ChristianCounsellor

Thanks but how did you get it to give you them two lists? I know it worked out perfect as I will be able to cut the groups of three from each list where boys are not wanted together and same for girls but then I will want list of one pick the remaining in group boys and one from group girls to give me the remaining possible and I can use search to find where say brother and sister are for the twins in the class as they distract one another. I can name seven pairs of boy/girl that would not work together so after I filter all them too I will have a much shorter list.

pandora205

What's the rational for having three children of each gender? Ratios aside (as there are more girls than boys), is there an assumption that this is the only way to have a balanced representation of the rest of the class?

Why not ask the children to prepare 'manifestos' and have the others vote?

Jivesinger

ChristianCounsellor wrote: »

Thanks but how did you get it to give you them two lists?

It was a bodge - I'm not proud of it!

I strongly suspect there's a much more elegant and/or simple way to achieve this.

But here's what I did...

My trick is to use binary representation of numbers. So I start with a list of numbers 0,1,2,3,4,5 etc. going down the first column of my sheet. For the boys, this needs to go from 0 up to 255

Start your numbers in row 4, so A4 is the first number; it also makes things easier later if you start with zero, not one. I used row 3 as my title row with column labels.

Then use the DEC2BIN function (you might need to install the Analysis Toolpak to make that work) to make the binary representation
0,1,10,11,100,101 etc.

At the same time I want to make it a string with leading zeros. So if my initial list of numbers starts from cell A4, the function for the boys is:
=RIGHT("00000000"&DEC2BIN(A4),8)
This gives me an 8 digit binary number, so every one of 8 digits is 1 or 0 (with each digit corresponding to a boy). In my sheet this formula is in column B

I then added 8 columns, one for each boy, with a formula like this. First I put a sequence of 1,2,3,..8 in the first row above my main table (Cells C1 to J1). Then I put the Boys names in cells C3 to J3 as titles.
Then in columns C to J, I put a formula like this, and copy it down (starting at row 4 in my case)
=0+MID($B4,C$1,1)

So I've now got every possible combination of boys, not just sets of 3. So we need to narrow it down to the binary numbers with three "1"s.
So my Column K has a formula like this
=SUM(C4:J4)
And Column L has:
=IF(K4=3,"Y","N")

Then I can filter or sort for the "Y"s, which are the combinations of 3.

Then my columns M to T look like this:
=IF(C4=1,C$3&",","")
This converts the 1s and 0s to showing the relevant boy's name if there's a 1. To make this work, my row 3 has the boy's names; "Boy One" in C3, "Boy Two" in D3, etc. and I've copied the same titles into M3 to T3.

So this gives "Boy One," if there's a 1 in the relevant column, or a blank string otherwise.

Then my Column U has:
=IF(L4="Y",M4&N4&O4&P4&Q4&R4&S4&T4," ")
This joins the selected names together

And Column Y has this to take off the last comma
=LEFT(U4,LEN(U4)-1)

Column Y was what I pasted above. (I sorted it into order first).

Then do the same with the girls...but with 11 columns rather than 8 (left as an exercise to the reader ). Annoyingly the DEC2BIN doesn't cope with 11 digit binary numbers so I had to cheat in my Column B by manually copying a block of 512 numbers and sticking "01","10","11" on the next 3 blocks.

ChristianCounsellor

The plan is to have a council and a sub council. the council will be in place for the year and sub council will change each week or so to allow each child to have some inclusion.


we did the vote last year and with more boys they voted for things more suited to boys (football) and we feel if balanced the choices they make will be better balanced rather than gender specific.


The last hour of each week they will be able to vote on what activity to do as a class together. also each day they will be able to see what lessons are that day and choose the order of them along with what they do during break time and lunch with a choice being group activity of varied types or play on own with friends. By allowing them the feel of authority and responsibility we hope it will help them focus more and work more as a group than boys v girls.

ChristianCounsellor

I was thinking something like Blake=1, John=2 Jack =3 etc and getting it to pick the numbers like a,b,c if a =1, b=1+1 c=b+1 if you follow my thought