When uploading data into PTO, one of the core requirements is that each row of data represents a student being in a class.
This page is about how run a macro that will convert the classes from appearing across the row, into a row for each.
In a nutshell
This macro is designed to convert data from this shape…
…into this shape:
Classes of each student should go down the page. The matching student details should be replicated next to each of these classes.
Spreadsheet Preparation
1. Make sure that the sheet within the spreadsheet, is the furthermost left one.
2. Make sure that the information that will be replicated next to each class is on the left side
This should include student fields (column headings starting with 'S')
This should include the academic year level of the student (CGN)
This can include parent fields (column heading starting with 'P', if you have them in this data)
3. Make sure that the columns of class information is on the right side
Be careful not to include the student sort code as part of the classes, particularly if the class details are codes
Using the macro
1. Remain on the sheet with the student and class information going across each row.
2. Press ALT/F11 (or for Mac users Go to Tools > Macro > Visual Basic Editor)
3. In the new page that opens, double-click on ‘This Workbook’, on the left of the page
4. Open the file below
5. Copy and paste all of the text in the file, into the empty space on the right in Visual Basic Editor
6. Click the play button in Visual Basic Editor, to run the macro
7. Proceed as guided on the screen
The number of student columns, when required, are the ones referred to in point 2 of Spreadsheet Preparation
The number of class columns, when required, are the maximum number of columns referred to in point 3 of Spreadsheet Preparation
8. When notified that the macro is done, click the Excel icon to return to your spreadsheet
9. You can now add merge in any additional data required, in order to upload the data to PTO
For instructions on how to merge information from other spreadsheets into this one, refer to Merging Spreadsheets Together for PTO