Think this is what you want
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).
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.
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.
But if one student has 12 subjects and another 13 it wont work.
Nearest I could come up with:
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!
You can do it with an array formula such as
This assumes you've layed your results table out thus:-
Blank Maths English Science ICT History
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
There are currently 1 users browsing this thread. (0 members and 1 guests)