[ms office - 2007] Converting an ordered list to broadsheet format

[ChrisH]

I have an ordered list which is name, subject, etc.

Each name has multiple row entries so

 

joe,bloggs,subject1,A

joe,bloggs,subject12,B

 

and I wan't this in a broadsheet format like:

 

FName,SName,Subject1,Subject2

joe,bloggs,A,B

 

Is there an easy way to do this in excel? I kind of got the right format using a pivot table but I couldn't get the original grade values to show, only the totals because of the pivot table doing it's normal calculations. Any ideas? I am sure this should be straight forward, but I am just having one of those days.

[JPS]

I appreciate it doesnt answer the question, but, are you getting this data into Excel from your MIS? If so there is probably a way to get it out in the format desired. I certainly do this a lot in my MIS (Serco CMIS)

[ChrisH]

It's FFT live data but with multiple subjects per student. You can do the individual subjects but that takes ages, would be nice to do them in one hit. Edited February 11, 2011 by ChrisH

[JPS]

Ahh FFT Live. Hmm not a big fan, in many ways the old database version was better. I am fortunate that we don't use FFT data here so not a problem I've had to tackle. I'll finish here and wait for other edugeekers to make a more helpful contribution. Good luck.

[jallsop]

Have you found a solution to this, as i have something very similar, which i need to get into a linear format for a mail merge.

[hgant]

A clunky way to do it is to convert the grades to points (e.g. put the points score on another sheet, use vlookup to look up the points). Add Pupil details to the rows of your pivot table, Subject to the columns, then your grade points in the Values section. You can then select Maximum or Average on the value field settings and as long as each pupil only has one value per grade per subject, it will select the only value available. Get rid of any subtotals you don't want.

 

You can then create a new sheet, pick the names and subjects up directly from the cells in the pivot table, and use a lookup to convert the points values that the pivot table has now put in the right positions back into grades. There is probably a more elegant solution.