+ Post New Thread
Results 1 to 6 of 6
Office Software Thread, Converting an ordered list to broadsheet format in Technical; 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 ...
  1. #1
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    4,999
    Thank Post
    120
    Thanked 280 Times in 258 Posts
    Rep Power
    106

    Converting an ordered list to broadsheet format

    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.

  2. #2
    JPS
    JPS is offline
    JPS's Avatar
    Join Date
    May 2010
    Location
    Chelmsford
    Posts
    293
    Thank Post
    91
    Thanked 44 Times in 40 Posts
    Rep Power
    12
    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)

  3. #3
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    4,999
    Thank Post
    120
    Thanked 280 Times in 258 Posts
    Rep Power
    106
    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.
    Last edited by ChrisH; 11th February 2011 at 09:28 AM.

  4. #4
    JPS
    JPS is offline
    JPS's Avatar
    Join Date
    May 2010
    Location
    Chelmsford
    Posts
    293
    Thank Post
    91
    Thanked 44 Times in 40 Posts
    Rep Power
    12
    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.

  5. #5

    Join Date
    Jan 2013
    Location
    Switzerland
    Posts
    168
    Thank Post
    13
    Thanked 23 Times in 22 Posts
    Rep Power
    7
    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.

  6. #6

    Join Date
    May 2013
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. Converting VHS videos to either DVD or file format
    By ctbjs in forum How do you do....it?
    Replies: 8
    Last Post: 17th May 2013, 10:38 PM
  2. Just Ordered Sky HD
    By Hightower in forum General Chat
    Replies: 22
    Last Post: 1st October 2010, 12:31 PM
  3. Exchange 2007 - Mailing list Digest Format
    By garethedmondson in forum How do you do....it?
    Replies: 1
    Last Post: 19th June 2009, 01:57 PM
  4. Mac Pro Just ordered
    By mac_shinobi in forum Mac
    Replies: 46
    Last Post: 17th August 2008, 09:01 AM
  5. Thinkgeek anyone ordered from them?
    By Pyroman in forum General Chat
    Replies: 20
    Last Post: 24th May 2008, 07:00 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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