+ Post New Thread
Results 1 to 9 of 9
Office Software Thread, Excel - Pivot Tables in Technical; I have spreadsheet which looks a bit like this: Name | Subject | Grade | Fred | Maths | A ...
  1. #1

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    29

    Excel - Pivot Tables

    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).

  2. #2

    teejay's Avatar
    Join Date
    Apr 2008
    Posts
    3,051
    Thank Post
    275
    Thanked 722 Times in 550 Posts
    Rep Power
    326
    Think this is what you want

  3. #3

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    29
    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.

  4. #4

    teejay's Avatar
    Join Date
    Apr 2008
    Posts
    3,051
    Thank Post
    275
    Thanked 722 Times in 550 Posts
    Rep Power
    326
    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.

  5. #5

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    29
    But if one student has 12 subjects and another 13 it wont work.

  6. #6
    steve's Avatar
    Join Date
    Oct 2005
    Location
    West Yorkshire
    Posts
    1,040
    Thank Post
    22
    Thanked 175 Times in 121 Posts
    Rep Power
    51
    Nearest I could come up with:
    Attached Images Attached Images

  7. #7

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,228
    Thank Post
    2,773
    Thanked 936 Times in 876 Posts
    Rep Power
    343
    Quote Originally Posted by danIT View Post
    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.

  8. #8

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    234
    Thank Post
    17
    Thanked 32 Times in 24 Posts
    Rep Power
    19
    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!

  9. #9
    The_Original_Invisible's Avatar
    Join Date
    May 2009
    Location
    Swindon
    Posts
    6
    Thank Post
    0
    Thanked 3 Times in 3 Posts
    Rep Power
    0
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Interactive Tables
    By loopylala in forum Hardware
    Replies: 2
    Last Post: 8th October 2009, 05:18 PM
  2. Exam tables... Furniture
    By timbo343 in forum General Chat
    Replies: 1
    Last Post: 9th October 2008, 12:00 AM
  3. Connecting to SIMS tables from elsewhere
    By dhassen in forum MIS Systems
    Replies: 15
    Last Post: 19th September 2008, 11:16 AM
  4. Performance Tables
    By PhilNeal in forum MIS Systems
    Replies: 1
    Last Post: 8th September 2008, 10:02 PM
  5. Word 2003 Tables
    By Mintsoft in forum Windows
    Replies: 2
    Last Post: 26th May 2006, 11:24 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •