I take attendance in large, lecture classes. Each student inputs a vote on a multiple-choice question with a "clicker" (a hand-held, remote control device), and each clicker has a 6-digit, hexadecimal serial number. This semester, I have been placing the attendance data into an Excel spreadsheet (sorting hexadecimal numbers is a nightmare, but that's another story). Due to students' absences, the list of serial codes is different---and has a different size---for different days. Therefore, I have been manually checking off on attendance by visually looking for the same serial numbers.

Can you suggest a way to have Excel automate this process?


If the data is all in columns in a single worksheet, use =countif() on a new worksheet where you have:

  • column A Student Name
  • column B Student Hexcode
  • column C =countif(reference to the column in the other sheet, cell to the left)

This would give you a count of attendance for students to compare to the number of classes held so far.

Alternatively if the data for each class is in a separate worksheet (either in a single workbook, or multiple workbooks:

  • column A Student Name
  • column B Student Hexcode
  • column C-X (one for each class) =match(cell with Hexcode, range for this class, false)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.