Excel Help - Student Attendance

Copper Contributor

Hello!


I work at an online school and have well over 200 students. I am looking for an efficient way of running attendance using the bigbluebutton csv report that is generated for us. It has the student name and then two of the many columns I'd like to use - one is the number of chat responses, the other is the number of poll responses. I'd like to set it up similar to what I have below, for an example. The students name in column A. Then two columns of data that I can copy/paste in somehow. Sometimes I get new students or lose students, so I'd like any new names to automatically be added when a new list is added, along with their data. 

Is there a good way to do this so I can just copy/paste the names, polls, and chats each day & have just the poll and chat data match up with the name in the A column?  I'd like to have the student name on just once.  The hard part that is confusing me is removal or addition of students, so I can't just copy/paste like normal, the sheet would need to help me find the root name & match that up. I hope this makes sense!  It's just so I can have everything there at a quick glance. 

 

axbase01_0-1693591377370.png

 

 

 

26 Replies

@axbase01 

 

I've slightly modified the attached. On the "front page" you are asked to enter a date. That date then generates two weeks (a Monday to the Friday of the following week) of data assuming you have entered all the raw data for those two weeks. This "trial data base" is of course incomplete. But you can see how simply changing the start date will change the columns to correspond.

 

A couple questions for you: I asked whether the "school week" in your case is five days or something else. As this stands, it assumes you'll always want to start with a Monday and go for two weeks. If you want three weeks, let me know, along with whether by week you mean 5 or 7 days; I'll also modify the layout so it's clear where weeks begin and end.

 

If you still have the raw data for some of the dates not currently in the database, add it in to test the functionality.

 

JUST BE SURE YOU'RE WORKING WITH EXCEL 365

I LOVE this version! But - the same thing with the dashes is happening when I add new data - here is a video showing it, I am on 365 and the school week is typically 5 days; a three week version is probably better, if it's not a pain to add. If it is, the two week version is fine! https://somup.com/c0QiF5AEwj

@axbase01 

 

I've attached a three week version, 5-day weeks only, and you should always make sure to enter a Monday date. I skip over the Saturday and Sunday in the two weekends in the middle of this span of days.

 

Have you tested adding a full data set? Even a full weeks worth of data?

 

The only odd thing I notice in your video is what I've captured in this screen grab

mathetes_1-1694118164753.png

It's odd that the date and time are appearing as strings of hashmarks for that one column, but not the adjacent one. Making me wonder if somehow the data are not in consistent formats. Are you copying this from a CSV file? What you sent me, I believe, were always XLS or XLSX files. If it's a CSV file, try importing it to the Excel format first before copying.

 

 

 

 

Awesome, it works if I upload the csv file into 365 and convert. However I am only on class 2 of five and have only imported 4 days of data and it's starting to get reeeeeally slow - which was my only fear, that this much data may be too much! I will try again in the morning just in case it's my internet...but it has been getting gradually slower with each data set - this new one has been "working on it" pasting for about 5 minutes.
We could limit the columns imported to just the ones used in the summary, but that’s its own complication. Let’s see when you’ve got a good connection, etc.
Or do a different workbook for each class.

I have tried a couple of times now and have done a speed test, and have a great connection & it's not wanting to import more data (the longest I've waited is about 10 minutes). I still don't have a ton imported - only about 1/8 of what I need at this point in the year, and this has to last me through January, as far as data imported.  It may be just too much data to handle.

@axbase01 

 

Two suggestions: 

  1. Keep separate workbooks for each of your several classes.
  2. Eliminate from the workbook's raw data sheet all but columns A through I (those aren't used in the summary calculations).

 

See the attached to see how the latter operates, and try copying and pasting in just those first 9 columns from your incoming data sheets,