Using Excel to sort class roster/attendance list

Question

I will use a software (Moodle BBB) for online classes next semester which allows me to get a .txt file with the names of all attendees sorted alphabetically. I will use this to determine attendance in synchronous activities.

I would like to use these files to get an Excel spreadsheet with the attendance record of all students.

One way which I think would be possible, but which I do not know how to implement, would be to just copy-paste the content of the .txt file in a column, representing the respective day, and order the entries of each column in such a way that each row only has the same name. Then I would just need to count how many entries a row has to determine how many classes the student attended.

But the problem is that some students will attend some classes but not others, so I could end up with a .txt file which looks like

Alice
Bob
Carl
David

and another one such as

Alice
Carl
David

so just copy-pasting these entries will not do: I need to add an empty entry in the second file, at the place of Bob.

How can I add empty entries in the correct positions?

I am also open to other suggestions on how to keep track of attendance using these .txt files.

Answer 1

I feel your pain, I teach as well and have similar issues with attendance tracking.

Create a staging area or sheet with 3 columns Input list (A1), Full list(A2) and Expanded lists(A3).

The first column is where you will paste the the list of attendees.(A2) The second is a list of Everyone in the class sorted to the order of your choice(B2). The last will have the following formula (C2)

=IF(COUNTIF($A$2:$A$5,B2)>0,B2,"")

This assumes there are only 4 student in your class, expand as needed. Formula uses each name in the full lists(Col B) and counts the occurrences of the name in Col A. If the count is great than 0 it displays the name from col B in Col C other wise it is Blank (""). It does not care if the Input List has a name more than once or if the Input List is not sorted

You can copy the Expanded list and Paste Values into a table that has the student in the same order on the left header and a date above it. Additional processing can be done on that table.

Answer 2

Create a folder on your machine where you'll place a text file for each day. On the Data tab, select Get Data\From File\From Folder. On the window that pops up, click on the Combine and Transform button. In the Query Edit screen, double click on the column headings to change them, if needed, then click the Load button. This queries data from all of the files in the folder and creates a single table. After you've created the query, select a cell inside the query results. On the Table Design tab, click on Summarize with Pivot Table. Select a cell inside the pivot table and use the PivotTable Fields control to structure your pivot table. Check the fields you want (likely just Name and Date). Drag name field to the Row control, drag date to the column control and then drag name to the Values control. The values control should default to CountOfName. You can drag the text file for each date to the folder you selected and the query will do the work. Then, in the PivotTableAnalyze tab, click Refresh All when you add new text files.