• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find members common in groups & rank group combinations according to least members in common

Angelique_C

Angelique_C

New Member
Hi Everyone,

Trying to work out an exam timetable and need to find combinations of subjects that allow exams to happen at the same time which involve the least amount of students that have those subjects in common. I have uploaded a very truncated example worksheet, my real sheet involves hundreds of students with lots of subjects...

I thought maybe this could be done via a "choose" formula or would it be better coding in VBA?

Not really sure how to even go about this, can anyone point me in the right direction? :)
 

Attachments

Hi ,

I am not sure what the right direction is , but what I have done is put your data in matrix form. The student names are the row headers , while the subject names form the column headers.

Within the matrix , TRUE means the student is taking that subject , while FALSE means the student is not appearing for that subject exam.

Can we now see which subject has the maximum FALSE values , which in turn will mean that that subject has the fewest number of students ?

Narayan
 

Attachments

NARAYANK991 said:
Hi ,

I am not sure what the right direction is , but what I have done is put your data in matrix form. The student names are the row headers , while the subject names form the column headers.

Within the matrix , TRUE means the student is taking that subject , while FALSE means the student is not appearing for that subject exam.

Can we now see which subject has the maximum FALSE values , which in turn will mean that that subject has the fewest number of students ?

Narayan
Click to expand...

Thanks Narayan,

I think that's nearly it, I just need to find the next step & please forgive my inept description of this, but what I'm really looking to ask is; which subjects would be best scheduled together, or what subject should I schedule with Biology? I'm looking for a formula or some vba code that would then compare all the true & false returns against each of the other subjects and give me an answer like "Biology can be scheduled with Chemistry" (on reflection, my example sheet wasn't set up that well & didn't include any subjects that had no students in common - sorry!).

Thanks again for your help,

Angelique
 
Hi Angelique ,

I think if you have more students , more subjects , and dates , it would be easier to think of the right approach ; can you upload a new file with all of this ?

Can you say what are the numbers , such as :

1. Number of students

2. Number of subjects

3. Number of dates

Will there be more than one exam on a day ?

Narayan
 
Thanks Narayan,

A new file is uploaded; there are 26 students, 25 Subjects and dates range from 25 March to 1 April (not inc 28 & 29 March). There can be more than one exam on one day, but more importantly, we want to hold as many at the same time as possible, hence the need to discover which subjects do not have students in common, indicating which exams can be held at the same time.

Angelique
 

Attachments

Hi Narayan, please add this to my last reply; in reality there are about 150 students studying 54 subjects - I have uploaded a bigger sample file with examples from the real data file, but with 26 students studying 25 subjects.

Angelique
 
Hi Angelique ,

I think this is a very difficult problem to solve ; how have you been doing it till now ?

I have used 1s and 0s instead of TRUE and FALSE , so that it is easier to make the zeros invisible , either by using one of the Excel options , or by a custom cell format. I have used the latter.

If you take a look at the matrix , you can recognize certain patterns , more easily than code can.

For example , English Advanced and English Standard are complementary , and these two subjects can be scheduled on the same date and at the same time.

The same is true for Mathematics and Mathematics General 2.

If you take a look at Sheet3 , you can see how these patterns can be more easily found ; what the figures represent is how many students are there in common between any two subjects. Where you see a zero , it means you can schedule those two subjects at the same time.

Is it possible you can use this method in your working file , and see whether it helps ?

Doing this using VBA involves iterating through all the possibilities , and given that you have 54 subjects , it seems a difficult task.

When you do it manually , what I suggest is that as you schedule two subjects at the same time , you can hide the relevant columns , so that the job of matching the next two subjects becomes a little easier.

After you have scheduled all those subjects which have 0 in the matrix , you can go to those which have 1 , then 2 and so on.

Narayan
 

Attachments

Hi Narayan,

I've been a while to get back to you because it's taken me some time to get my head around this - but have got it all working with real data and it works brilliantly! Thanks so much, you're a true ninja!

BTW, re your question about how I've been doing it manually.... it's too long and boring to even try to explain, but this workbook will change my life :)
 
You must log in or register to reply here.
Back
Top