+ Post New Thread
Results 1 to 6 of 6
MIS Systems Thread, Data manipulation in Technical; Hi Looking for a bit of guidance on an issue I have. As part of my role I have to ...
  1. #1

    Join Date
    Jul 2007
    Location
    Durham
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Data manipulation

    Hi

    Looking for a bit of guidance on an issue I have.

    As part of my role I have to take export a large marksheet into excel and manipulate the data. This involves breaking down the spreadsheet by gender etc and adding a no of quite exact formulas to a large no of columns.

    My issue is this I have to do this half termly and it takes a great deal of time, plus there are a number of opportunities for errors to occur. For example if after manipulating the data I discover an error on the orig marksheet I have to start all over again! ( sometime the errors are not of my making ) So, to speed up/ Automate the operation, bearing in mind the number of students can alter between each data capture period, is there a way I can set up some sort of template in excel ( and I have to do it in Excel as SIMS itself cannot produce the degree of data manipulation required by the SLT), where I could copy varying number of students in but the formulas would remain and the various sorts and gender breakdowns would be done automatically? Am I asking to much of Excel? Would it invole some form of coding? Hve I posted this in the wrong Forum?

    Regards

    Bhsman

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    That's all doable yes (obviously depends on exact details).

    If you're only after things like you mentioned, it shouldn't be difficult. In terms of counting, you can set formulas to count "occurances" of things. aka M/F's

    Sorts are easy enough too, I'd personally do it as a excel workbook with VBA enabled, so yes some sort of coding as you word it :P

    Obviously you'd need to set some limit, but say you could count occurences within 1000 rows or w/e, to give you room for expanding in future

    (And yes it's an excel/scripting question, rather than SIMS )

    Steve

  3. #3


    Join Date
    May 2009
    Posts
    2,878
    Thank Post
    258
    Thanked 766 Times in 581 Posts
    Rep Power
    269
    It sounds as if it should be possible to set up the worksheet so that you import (paste) the data into one tab (worksheet) and have other worksheets reference that tab for their data. If the source data changes or you spot an error and so need to correct it in SIMS and then re-export, you then only need to past the new data into the raw data worksheet and the rest of the workbook should update from that.

    If you have a variable amount of rows in the source data and are then somewhere up the line using sums/averages, you will probably run into errors (where intermediate cells are referencing blank rows on the source data sheet). You can use ISERROR() function on the intermediate cells to make sure that if there is no source data, the cells are blank rather than showing an error. Blank cells then won't much up sum, average, counts etc so your forumulas will be able to deal with a variable amount of rows/columns in the source data.

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,843
    Thank Post
    671
    Thanked 1,381 Times in 1,143 Posts
    Rep Power
    349
    Vba is a good option but more complex. I went the second option and just paste data in and it sorts it out.
    Also in the background we have a data assistant putting formulas into SIMS so that the bits it can manage are done internally. It's painful but only needs doing once - until management change the goalposts that is!

  5. #5

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,613
    Thank Post
    1,229
    Thanked 772 Times in 670 Posts
    Rep Power
    234
    Quote Originally Posted by bhsman View Post
    Am I asking to much of Excel?
    In my experience, it might not be Excel so much as other users - Excel does, after all, contain facilities to do an awful lot of interesting things, but you do need to know how to use it properly. You also have to know your statistics properly. If you have other people trying to use the spreadsheet to interpret results, change figures and so on then you'll have chaos. This kind of thing sucked up three summer holidays worth of time at my last job, time which could have been used to do useful things with our network. I tried setting up an Excel spreadhseet for users to enter figures in to but they simply never got the hang of it, so I then tried developing a few quick scripts to take a few basic CSV files of data and spew out some graphs. As these worked they started to get horribly extended in scope, so we started to have a whole growing data-analysis application cobbled together with no coherent structure.

    My guess is that your best bet is to simply buy in a statistical analysis package and learn how to use it - Crystal Reports might be a good place to start. Otherwise, I'm guessing that a script that takes in an Excel spreadhseet of raw results and outputs an Excel spreadhseet containing sorted, eparated results and formulas might be your best bet - all you then have to do is enter results in the correct format in a single Excel spreadsheet.

  6. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,843
    Thank Post
    671
    Thanked 1,381 Times in 1,143 Posts
    Rep Power
    349
    I don't know the costs of their products and services and this does not mean it's okay to start advertising them, but you may like to look at a third party that can help such as this : 4Matrix

SHARE:
+ Post New Thread

Similar Threads

  1. Data retention
    By Dos_Box in forum School ICT Policies
    Replies: 21
    Last Post: 27th March 2006, 08:40 AM
  2. Replies: 1
    Last Post: 24th November 2005, 10:57 AM
  3. Cd Hidden Data
    By StewartKnight in forum Hardware
    Replies: 6
    Last Post: 7th November 2005, 10:45 AM
  4. Data Projector being stupid
    By e_g_r in forum Hardware
    Replies: 3
    Last Post: 19th October 2005, 03:27 PM
  5. Data Protection Act - re: Remote Access
    By mark in forum School ICT Policies
    Replies: 18
    Last Post: 26th September 2005, 07:19 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
  •