Data Sorting Conundrum

Hello SchoolData and Welcome to Excel Forum.
I don't know what "...each pupil gets their best 6 options (not necessarily their top 6 choices)" means.
The screen shot shows 12 column headers which I assume to be the modules. Do the numbers represent the students? If so I don't understand why the column with the header British contains duplicates of the numbers 9, 3 and 8.
I suggest explaining in more detail and also providing us with an actual Excel worksheet as it is not possible to test formulas/code on a picture.
Information about attaching an Excel workbook to your next post is given in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page.
Let us know if you have any questions.

SchoolData,

Think the attached will do what you want.

Cols A - N have "dummy" scores against the thirteen Modules for two hundred and forty Students, assuming each row shows the Student "ranks" of choices without any "ties".
Conditional Formatting "fills" the choices between 1 - 6 in green.
Results are shown in the Table in Cols P - AC, showing how many Students opted for what.
Assuming you are limiting the class to twenty one Students, row 10 shows you will need six classes for each Module, except for Modules 7 and 8, which need only five, and Module 5, which needs seven Classes.
I haven't done anything to "Stream" the different Classes, if you want to seperate your 1s and 2s from those making the Module their Third or Fourth choice, but if you want to do that, just change the CF so the Middle Choices (3 and 4) and Lower Choices (5 and 6) fill in different Colours.
You can then "filter" each Column by colour if you want to extract the actual Names.
Just overwrite the dummy names and values with your actuals, and see if it works for you?

Look forward to the feedback

Ochimus

This is a brute force proposal which hopefully will help until someone proposes a more elegant one.
1. The original table is copied six times, once for each half-term and is populated with formulas like:

Formula:

Please Login or Register  to view this content.

2. Each table has an assignment range for that half-term to its right. The first column of each of those is populated using formulas like:

Formula:

Please Login or Register  to view this content.

The remaining columns are populated using formulas like:

Formula:

Please Login or Register  to view this content.

3. To the right of each assignment range is the display of students in each module, which are populated using formulas like:

Formula:

Please Login or Register  to view this content.

Note that the display for Autumn 1 is in columns Z:AK and for Summer 2 is in columns HT:IE.
Let us know if you have any questions.

SchoolData,

I put together the attached CLASSES worksheet Table which "assigns" the Students to a class in each of the six Terms for the subjects they scored as 1 - 6.

The basis of the totals is that I sorted everyone's 1 Score and set that as Autumn 1, then the 2s as Autumn 2, and so on, ending the Summer 2 Term with everyone's 6s.

Fortunately, as Cols A - E are a Table, you can filter any or all the Columns by Name, Subject or Term, and change the Terms as needed.

The Pivot Table in Cols I - J gives you the Overview, summarising how many Students are currently assigned to each Term in each Subject.

If you change any Terms in Cols E, click on any cell in the Pivot, click 'Refresh', and you will see the revised Summaries.

Hope it helps, as you can simply "overwrite" the dummy names with your real ones.

Ochimus

Looking at the file attached to post #5, none of the half terms have more than 25 students in a module.