Excel - Pivot Tables

[danIT]

I have spreadsheet which looks a bit like this:

 

Name | Subject | Grade |

Fred | Maths | A |

Fred | English | A |

Fred | Science | A |

Fred | ICT | A |

Fred | History | A |

Sally | Maths | A |

Sally | English | A |

Sally | Science | A |

Sally | ICT | A |

Sally | History | A |

 

I want to convert that to:

 

Name | Maths | English | Science | ICT |

Fred | A | A | A | A |

Sally | A | A | A | A |

 

Any other ideas than using a Pivot Table (Which i hate as it always returns '1' instead of the Grade as if its counting the records).

[teejay]

Think this is what you want

[danIT]

Doesnt quite do the job, it doesn't get rid of the duplicates. For instance in my example the Subject English appears twice, once for each pupil, it need only appear once as once column titled English.

 

I suspect i'll need some kind of script to dynamically read the records.

[teejay]

Yes it does, you just change the cell reference at the start of the formula to $C$1.

The only time this won't work is you don't have the same subjects listed for each pupil in the subject column.

[danIT]

But if one student has 12 subjects and another 13 it wont work.

[steve]

Nearest I could come up with:

[mac_shinobi]

I have spreadsheet which looks a bit like this:

 

Name | Subject | Grade |

Fred | Maths | A |

Fred | English | A |

Fred | Science | A |

Fred | ICT | A |

Fred | History | A |

Sally | Maths | A |

Sally | English | A |

Sally | Science | A |

Sally | ICT | A |

Sally | History | A |

 

I want to convert that to:

 

Name | Maths | English | Science | ICT |

Fred | A | A | A | A |

Sally | A | A | A | A |

 

Any other ideas than using a Pivot Table (Which i hate as it always returns '1' instead of the Grade as if its counting the records).

 

create a new worksheet and just use relative paths ie =$A$1 etc

 

and just make each cell on the next sheet equal the correct value on the one you already have and that will make them appear in the correct place on the new worksheet.

[MattMitchell]

If you do a pivot table, instead of having Count() of for the value field, use Min() instead (or max, depending on whether you want to show "A" or "C" if both values are present) and it'll do what you want!

[The_Original_Invisible]

You can do it with an array formula such as

 

{=INDEX($C$2:$C$11,MATCH($E3,IF($B$2:$B$11=F$2,$A$2:$A$11),0))}

 

This assumes you've layed your results table out thus:-

 

Blank Maths English Science ICT History

Fred

Sally

 

This assumes that $E3 = Name of student and F$2 = Subject

 

Remember to Ctrl Shift Enter it to get the curly bracket, don't try and type them

 

Working example available but you'll need to pm me. I'm in a band and off gigging tonight but can look tomorrow for you

 

Regards

 

Lee