Posts Tagged ‘vlookup’

Getting answers for the LRC management from Report Express

    1. Report Express is a powerful tool to get current enrolment data which seems vital for running the LRC, but which I have not been able to get my hands on before easily (SCT-Banner limits access too much).
    2. Excel download format – which I recommend : cleaner (fewer graphics) and more information – DOES work, but for Excel 2010, I have to rename the download file extension from XLS to HTML (which the download is) and “open with”  –> Excel.
    3. I have not been successful merging these output files per language on the command line into one large HTML file and cleaning up the <html><body> framework – so I have to open each one, merge by copy/paste the contents of the result worksheet into a new worksheet and clean up the data in there by converting into an Excel Table and sorting by a suitable table column, e.g. ID, which puts all actual enrolment data sequentially, and separates all (redundant anyways) header and footer information.
    4. I finally added table columns with array formulas to calculate the enrolment aggregates,
      1. per this section (to answer questions like: will this class fit into the language resource center?),
      2. this course # (to answer questions like: where can we have maximum impact on improving learning with technology with creating the minimum of new learning/assessment materials. Assessment is standardized per course #.)
      3. and per language-level.
    5. Finally,  vlookup-columns allow me to link the instructor of record and other missing class information (room, building, time) to the student enrolment rows. This allows me to filter, sort and search the enrolment sheet with real-life questions, like
      1. can we support this size class/course/level and language in the LRC
      2. is it practical to relocate this course for part/a whole class meeting to the LRC
      3. which students need be given access permissions to the SANAKO
      4. etc.
    6. Sample filter of the aggregate sheet: enrolment-with-vlookup

How to identify graded participants in Moodle Course Activity Reports using Vlookup

  1. Having a chart of full names, Moodle id # and usernames would be useful (e.g. for debugging with the Activity Reports that in some cases do not resolve Moodle id# so Excel vlookup has to rescue the day once again…)
  2. The Moodle “Participants” affords the instructor a convenient list, with paging, sort, filter (by role), and different detail-level views.
  3. However, the overview does link course and student id #, but not institutional username while the detail view contain the username as part of the email, but is not truly tabular (consists of an array of tables, each containing one user; at least you can avoid the paging).
  4. Enter grepwin to extract the emails:


  5. and copy paste them into the Excel created from the tabular overview:
  6. lookupIs there an easier way?