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.