Splitting data

[Tigerexcel]

I have a spreadsheet with student results that contains data such as Student A (in the first column), then the individual results Chemistry 60, Biology 55, Maths 70, English 80 all in one cell (in the next column), the next row down will contain Student B, Maths 60, Languages 50, Physics 90, next one down might contain Biology 75, English 45, Applied Maths 55, General Science 80 etc. The results for each student are in one cell only making it difficult to do any sort of analysis.
I would like to arrange the data so that instead of meaningless column headings up the top like Column A we have along the top Chemistry Maths English etc, then the scores in the relevant columns for each student.
I can use text to columns to split the data across the columns but the names of the subjects won't be indicated on the spreadsheet. There would be approx. 30 or so units that students could take. Typically no one does more than 4 units at a time. There would be approx 1000 students. There may well be a way to do this without VBA and am happy to do it in anyway that gets the job done.

 

[Peter_SSs]

Not easy to explain, but I'll try.

Put all the column B strings into an array

Take the first element of the array "Chemistry 60, Biology 55, Maths 70, English 80"
and split it where there is ", ". This makes a new array {"Chemistry 60"; "Biology 55"; "Maths 70"; "English 80"}
For each of those 4 elements find where the last space is (pos)
Subj (Subject name) is what is to the left of pos ("Chemistry" is the first one)
If Chemistry is not in the dictionary add it to the dictionary together with "1" being the first entry in the dictionary and indicating that Chemistry will be column 1 of the results.
Into the results array (b) put the score (what was right of pos) into the correct column. The correct column (1) is retrieved from the dictionary.

Next is Biology and it will be 2nd into dictionary so it gets a 2 and the score is put into column 2 of results array.
Maths is 3rd in so gets a 3 in the dictionary and score goes into col 3 of results
English gets a 4 and score goes into col 4 of results.

Now move to the next row (2) "Maths 60, Languages 50, Physics 90"
and split again {"Maths 60"; "Languages 50"; "Physics 90"}
For each of those ..
Maths is already in the dictionary with a 3 so put this score in col 3, row 2 of results.
Languages is not in dictionary so put it in with next available number (5) and put that score in col 5, row 2
Physics is not in yet so gets a 6

Now move to the next row (3) "Biology 75, English 45, Applied Maths 55, General Science 80"
and split again {"Biology 75"; "English 45"; ......}
For each of those ..
Biology is already in the dictionary with a 2 so put this score in col 2, row 3 of results.
etc, etc

Hope that made some sense.

 

[Tigerexcel]

Thanks Peter appreciate the explanation and it does help. It's such a clever piece of coding and does open up other possibilities. Got a couple of books on VBA but they don't go into much detail on VBA with arrays which as I'm discovering is quite powerful.