[sims] Alis predictions into SIMS

[CarpeDiem]

Ok so I am wondering if I am missing a trick. Every year we get our ALIS data in a ridiculous format that I have to fiddle with before it goes into SIMS. Last year I converted all the grades to numbers and hoiked it into a pivot table then converted it all back again, which gave me a nice CSV file.

 

It isn't working this year and I need the data in there. What does everyone else do?!

 

Ever hopeful.

[LosOjos]

It's been a while since I used ALIS, but I don't remember it being any more problematic than any other result set - don't they provide an Excel/CSV list of students and targets?

[CarpeDiem]

Yes but it is one line per child and subject so needs to be pivoted to get subjects in columns and pupils in rows

[LosOjos]

Yes but it is one line per child and subject so needs to be pivoted to get subjects in columns and pupils in rows

 

Ahhh OK I'm with you now; I'm afriad the only way I know is a pivot, however I'm not sure how you do yours but my method is quite quick:

 

- create a unique ID for the row, usually a combination of student ID, and subject name concatenated. Make that column A

- create your pivot with student ID as row and subject name as column

- copy the whole sheet and paste as values

- do a vlookup on the concatenated student ID and subject name in the first empty cell, locking the column on student ID and the row for subject name

- copy and paste to fill your table

- copy and paste all your lookups as values

- do a "Find & Replace" to change all #N/As to empty fields

 

I do that quite regularly with such data, it sounds long winded but once you have the knack, it'll take 5 minutes no matter how large a data set you're working with. I'll send you an example if you like with each stage on a separate sheet (if my explanation isn't clear)

[Seb1780]

Sort original by subject / pupil

Copy each pupil / subject / prediction to another area of the sheet

Resort original list to Pupil

Use VLOOKUP in each column to create table type spreadsheet looking up from the copied subject tables

 

Simples

[tpcc]

I export the marksheet(s) from the subjects and copy the relevant data from ALIS across. If the subject is only taken by a few students it is easier to manually enter the data into SIMs