Curious about how Brock University's Sakai-Based LMS was named Isaak?. Find out more here

Using VLOOKUP to consolidate data for Gradebook

From Information about Isaak, Brock University's Sakai-Based LMS

Jump to: navigation, search

The VLOOKUP (vertical lookup) function is useful for consolidating an external data set --such as that produced by Brock's Scantron services-- with Sakai course site data, ultimately for import to the Sakai Gradebook. This is often a necessary procedure due to the difference in the way that provided data is organized versus what the Gradebook will accept for import, but will offer significant time savings as opposed to manual entry.

In general, the VLOOKUP function will "search the first column of a range of cells, and then return a value from any cell on the same row of the range"[1] Spreadsheet programs such as MS Excel, Open Office, or Libre Office are recommended.

Contents

[edit] Scantron to Gradebook

This is a multi-step procedure that allows for import of Scantron data to the Sakai Gradebook tool. Using VLOOKUP, the following data conflicts are resolved:

  • Scantron data is provided with student numbers only. Seven-digit student numbers are used within many systems at Brock to situate students, however the Sakai Gradebook tool is able to do so using CAMPUS_IDs only. Using VLOOKUP, student numbers and campus IDs can be safely related to one another using student names as a common identifier.
  • Not all enrolled students may be included within a Scantron report. Scantron reports provide a list of scores based on the submitting student, however students that do not sit for an assessment will not be included. VLOOKUP will locate and assign null values to students prior to your import to the Gradebook, allowing all enrolled students to receive a score rather than only those that wrote the assessment.

[edit] Stage 1 - Match Scantron scores to student names

  1. This assumes that you've received the Scantron results file containing one column for student numbers and a second for scores on the assessment. The file is normally called "ROSTER BY STUDENT NUMBER", and will have a .CSV extension. Open the file using a spreadsheet program like MS Excel, and copy the contents of columns 1 and 2.
  2. In your Sakai course site, open the Gradebook tool, click "All Grades", then "Export as CSV for FGB". This step will generate a downloadable spreadsheet of student names and numbers that will be ideal for matching with the data in step 1.
  3. Open the file downloaded in step 2. Note that it's safe to delete columns other than "Student ID" and "Student Name" to simplify your view, as these will not be used in the upcoming steps. Next, create a second worksheet/tab using the buttons to the lower left of the interface, then paste the contents that were copied in step 1 into the first two columns.
  4. It's now time to enter the VLOOKUP function. Return to the first tab and enter the following formula into the second cell of the first empty column (likely to be cell C2)
    1. 1. the lookup_value, in our case the cell number containing the student number that we are searching for in the other tab because we want to locate the score that is associated with it.
    2. 2. the table_array, the range of cells and columns that are to be searched. Both columns in the second tab are specified, as the first column contains the value (the student number) that we wish to locate, and the second contains the value (the score) that we wish to import. Enter this by switching to the second tab, holding CTRL/Command, then highlighting the header of the two columns.
    3. 3. the col_index_num, the number of the column from which the matching value must be returned. When the function locates the lookup_value as specified in 1., it will then import the value within the same row but from the column specified here.
    4. 4. the range_lookup specifies whether you wish to locate results that approximate the value specified in lookup_value or match it exactly. Consider entering FALSE for this value, as it is important that results are identical.
  5. The cell into which you've just entered the function should now be returning a score that is identical to its value for the same student number on the second tab (which consists of data provided by Scantron results used in step 1), except the student name is now also known and associated with the student number and score. Extend the function to all cells in the column used in step 4.
  6. All students should now possess a score for the assessment. Those that did not submit will receive a value of #N/A, which is an indication that the Scantron results did not return a score for that particular student number. Optionally, save your spreadsheet for reference.

[edit] Stage 2 - Match student names to CAMPUS_IDs

  1. Note that you must create a Gradebook item for the assessment that is to be imported from Scantron. This next stage will begin with the downloading of the Gradebook template that will eventually be uploaded back in the final Stage. Do so by opening the Gradebook tool, clicking Import Grades, then Download Spreadsheet Template as CSV. This generates a list of students based on Campus ID and name, and will be useful for merging data from the previous spreadsheet containing student numbers.
  2. Open the file downloaded in step 1. Note that it's safe to delete columns other than "Student ID" and "Student Name" to simplify your view, as these will not be used in the upcoming steps. Next, create a second worksheet/tab using the buttons to the lower left of the interface, then copy and paste the three columns that were created within the first tab in Stage 1 above.
  3. You should now have three columns in the second tab: one for Student ID, one for Student Name, and a third containing the scores for the assessment. For the VLOOKUP function to work properly in this Stage, it's necessary to swap the locations of the Student ID and Student Name columns in the second tab so that Student Name appears first (thus, in column A).
  4. Back in the first tab, enter the following VLOOKUP function code in the second cell of the first blank column (likely to be C2): =VLOOKUP(B2, Sheet1!A:C, 3, FALSE). Please refer back to the information in Stage 1, step 4 for an explanation of the values.
  5. The cell into which you've just entered the function should now be returning a score that is identical to its value for the same student name within the second tab. Extend the function to all cells in the column used in step 4.
  6. All students should now possess a score for the assessment. Those that did not submit will receive a value of #N/A, which is an indication that the Scantron results did not return a score for that particular student number. To prepare the data for import to Gradebook, locate instances of #N/A and replace them with zeroes.
  7. Highlight and copy all the data on the first tab, then paste only the cell values back to the first tab of the spreadsheet. Note that it's most ideal to past the values on top of themselves to ensure that your spreadsheet remains manageable. The removal of the formulas is necessary in this step because, upon saving your spreadsheet as a CSV in preparation for import to the Gradebook, the second tab will be lost.
  8. Almost done! As a final step, save your spreadsheet as a CSV file.

[edit] Stage 3 - Import to Sakai Gradebook

  1. Follow steps as outlined on the Gradebook import help guide. The spreadsheet to be imported will be the one completed at the conclusion of Stage 2, step 8.
  2. It may be worth spot-checking the imported scores or reviewing the checklist for releasing grades documentation to ensure that all looks as it should.

[edit] Other articles to consider

[edit] Notes and References

  1. VLOOKUP function, Office Online

Other articles about Isaak/Sakai's tools and how instructors can use them:

Core Tools:   Image:Flag blue.png Announcements | Image:Page edit.png Assignments | Image:Lessons-icon.png Lessons | Image:Comments.png Forums | Image:Report.png Gradebook | Image:Folder.png Resources | Image:Calendar.png Schedule | Image:Pencil.png Tests and Quizzes

More Tools:   Image:User comment.png Chat Room | Image:Folder page.png Drop Box | Image:House.png Home | Image:Comment.png Messages | Image:Chart bar.png Statistics | Image:Page world.png Web Content


Related articles:   How to add tools to your course | Using the Text Editor | Manage Groups | Permissions and roles | Contact the CPI for help

Personal tools
  • Log in / create account
Bookmark and Share