Comparing Excel lists

In my online courses, I try to limit myself to sending out two weekly emailers to ākonga (students), to guide their mahi for the week. To do that, I run a little handbook for each course, built up over time, so I don't forget key milestones, reminders, and other course-specific information. But to send those emails, I need an accurate mailing list. That sounds like it shouldn't be too hard, right?

However, at tertiary level, the first three weeks of each semester are tricky to manage as class lists fluctuate daily. Until the end of week 3, ākonga can withdraw, and get a refund on their fee, which I think is very sensible (and during the Covid pandemic, this was extended, which I thought was highly compassionate). You see, we have ākonga who: 

• enrolled last year, thinking they would study next year, but their lives have changed in the intervening three months; 
• didn't realise that the semester was underway, have enrolled late, and are now overwhelmed by how much they need to do to catch up; 
• didn't realise that the semester was underway, have been ignoring emails, and now the catch-up study pressure is driving the need to withdraw; and 
• didn't realise that the workload would take so much time away from their whānau and need to withdraw to restore their sanity.

This flow of ins and outs in the class lists requires daily checking of two institutional systems where I teach, and the keeping of my own spreadsheet. I get emails from ākonga who withdraw, and pass them straight onto the administrators, but there is still a lag between my list (immediate) and the system (likely two days behind). Sometimes ākonga withdraw via Student Services or through our administrators, then I am likely two days behind, still mailing ākonga who have already withdrawn. 

So each day I check that my spreadsheet matches the system, and as part of that, I use a matching function in Excel to spot the variances.

The matching function is very simple. In the function I enter the surname cell reference from my spreadsheet, and temporarily copy in the surname column from an institutional enrollment system Excel export, and compare, using:

=EXACT([Excel s/s surname],[copied out surname from system])

This returns a "TRUE" if a match, "FALSE" if not. By looking down my Excel sheet, I start working where the item first turns to FALSE, reconciling the two lists as I go.

Too easy.

Sam