Subject choice workbook help

I need to put together a subject choice collation workbork, but am getting very mixed up with it.

I have 150 students, who each need to pick three subjects. Each student also has a first and second choice for each of their three subjects.
i.e subject one - first choice maths second choice biology, subject two - first choice chemistry second choice physics, subject three - first choice history second choice geography.

I would like to be able to input into a sheet their choices they have then made, and then be able to have a list of the students who have picked each subject, possibly via a look up but mine doesn't seem to work.

I see it like this, i think.

Student sub 1.1 sub 1.2 sub 2.1 sub 2.2 sub 3.1 sub 3.3
Joe Smith maths biology chemistry physics history geography

*new sheet perhaps*
Maths
First choice Second choice
Joe Smith Jack Black

Any ideas?


Thanks
Bry.

Can you upload a sanitized workbook with examples of what you're attempting to accomplish?

I think something like this, but that is only because I can't think of another way.
choices sample.xlsx

The way the data is organized makes it very difficult to work with. You receive the data as it's organized in the "Group List" worksheet? Also, where is the data distinguishing Subject 1 from Subject 2 from Subject 3 etc.?

rotation choices.docx
i get the choices in this format.
each of the 150 students completes this form.
they have three rotations to pick a subject for and they are allowed two choices per subject,
we then need to collate this information so the students can be entered into a subject and also for which of the rotations.
So for example the photography rotation is run in sept, jan and april.
Sorry it is quite complex hence the issue

I'd organize the data as attached. Then you can filter by subject choice for each rotation and get a list of the relevant students. Please respond if this is inadequate.

Thanks, I'll have a look and let you know