Excel Help - Student Attendance

[axbase01]

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. 

[#2]

What does the CSV file look like? There might be an easier way to get your output to look like your image looks, to do so without needing to copy and paste. Just add the most recent set of data from that CSV, with date, name, chat# and poll#
But it would help to see what that raw data looks like (again, using "names" like Jane Doe and Bob Jones)

[#3]

It looks just like the data in the image, except with many more columns (that I don't need) & only on date. I added the other dates for an example of what I need set up. The big thing I cannot figure out is how to have only one set of names and have everything still match if a student is added/removed and the data rows change due to that.

[#4]

@axbase01 

It looks just like the data in the image, except with many more columns (that I don't need) & only on[e?] date.

If there's only one date, that means (or so I'm inferring) that it doesn't look "just like" the data in the image; there's some kind of difference, most likely in how chats and polls are reported. Are real names used, or is there a student ID (more unique, so that John Doe #1 can't be confused with John Doe #2)

That aside, for an ongoing process, it's often best to separate raw data (the input end of things) from the output. You're asking here for a way, a process--be it formula or something else--that will enable you to take the daily (weekly?/monthly?) data from that CSV and generate a summary view of the data like you have in your initial image.

Right now your process is to take portions of the input data and copy and paste.

Could you create a slightly more expansive edition of the raw data (with false names) CSV and post it on OneDrive or GoogleDrive, pasting a link here that grants access to it, as well as that spreadsheet you created as a sample of the output. Working with actual workbooks (or mockups) would be more relevant than images.....I am quite sure we can work up a fairly simple process for you.

[#5]

Sure - here is the raw data from two days. Thank you for your help! I use excel normally, but here is it pasted onto a google link in editor form.

https://docs.google.com/spreadsheets/d/1AUL8yLAmIxU9cYGzLElFx2RAr_Z52Y-v/edit?usp=sharing&ouid=11225...

[#6]

@axbase01 

Thanks. I've downloaded that. BTW, it's not at all in the same format as the example file you showed, but more what I expected.  I'll won't be able to get to devote much time until the weekend.....

can you tell me how often the student roster changes, and are they altogether new, or just different from day to day with, in fact, a master list somewhere so that we could in fact work from something like "students registered this semester"? It seems odd to care so much if people are always coming and going; I can understand that any one person will have absences, etc., but totally new showing up?!

[#7]

@axbase01 

OK -- here's a first pass. I've designed this so that what you can do is simply copy each subsequent day's CSV report to the bottom of the data table. The formulas will accommodate any new initials. However, assuming there IS a full roster of registered students, it would make more sense to have that on the left most column as a fixed text list. You will still need to add columns for Chat and Poll and copy the formulas to the new columns. Some documentation in the form of text boxes is included.

The result looks like this. 

[#8]

https://b23.tv/hI1vKzm
Another option

[#9]

Thank you so much for your time and trying to help me out. Once I get a working sheet, it will save me many, many hours a week. Unfortunately the roster is very fluid, as students get added and withdrawn many times per week & we teachers don't know we are getting new kids till they pop up in our roster their first day. Online schools are very different with this than traditional schools. Over the year I probably have 500  kids total come in and out. Only about 100 will be the same all year. 

I downloaded the sheet you made, but as soon as I paste new data below the old data, all numbers in the report have dashes. I have been using excel a long time, but only do very simple things, so am not extremely well versed. Poll # change each day - not sure if that matters. Some days we will have 1 or 2, others 4-7. Here are 3 things I tried & I'm not sure what I am doing wrong.  I played around with it a bit more before making the video and cannot figure it out.   https://somup.com/c0QhD3Akve

[#10]

Sadly, the video doesn't really help me grasp it all. Is it possible for you to post another copy with a new day's data (or maybe two) in their own raw data form. then I'll create an updated one with more detailed instructions on how to manage that changing number of students.

[#11]

Yes, here you  go. @mathetes 

[#12]

@axbase01 

And here you go.

Note the comments on the raw data sheet first, then the summary sheet. By the way, do you have a subscription to Microsoft 365, or the Excel 2021 version or newer. Some of the functionality of this workbook does depend on having one of those current versions.

[#13]

I have found xl_func in your video which may mean the forumla not work properly.
What is your excel version?

[#14]

@peiyezhu 

 

[#15]

Professional Plus 2016

[#16]

Thanks! I'll play around with this today & update. the version I have is Professional Plus 2016

[#17]

@axbase01 

As @peiyezhu observed, 

I have found xl_func in your video which may mean the forumla not work properly.
What is your excel version?

And your response, that you're using Professional Plus 2016, that IS the problem with applying my solution. I use several functions that require Excel 2021 or a subscription to Microsoft 365. I'd strongly encourage you--is this school-provided software?--to upgrade. These new Dynamic Array functions, notably UNIQUE and FILTER, as well as SORT, are crucial components of what I created.  Here's a good introduction to those functions.

[#18]

Yes, it is school provided. I have an even older version on my personal computer from college. I did find out we have 365 and was playing around with it just now. It did seem to work the first time, I pasted something in, but as soon as I added another data set - an old one (date wise - the date was already in the file functioning properly) everything turned to dashes again. I'm not sure if this will be an issue or not. I have 5 periods of classes for this, so will add 5 different sets of data for each day, and usually only work on attendance once a week because it takes so much time out of my day. I am not sure why it is turning to dashes after another set of data, with the first set working multiple times. I did try to re-copy the formulas down and they don't repopulate.

[#19]

@axbase01 

You are causing my head to spin a bit. 5 periods for classes each day? Different cohorts of students, or different subject matters or continuation of subject matter with the same cohort?

Get Microsoft 365 and stay with it, first of all. On your home computer too.

The dashes will appear when you add new datasets, until the formulas get copied down. I can't explain why you say you can't copy the formulas down.

A suggestion. Do you need to be showing on the summary page all dates forever and ever, amen?

Could a weekly display work? Such that we could have any set of seven days (or five days, if that's a school week)? You could keep ALL the data for all the days as a single database, but it would then be possible to have that front page, summary page, set up so that you just change the first day to the start date you want, and all the rest happens.

Or it could be monthly.

If something like that would work, such that all you had to do was copy the new datasets and paste them into the data table, I could set up that "front page" so that all you needed to do would be to set the first date of the period to report. 

[#20]

Yes - 5 periods, all with different students. Each period has the same kids each day unless we get new ones added or disappear if kids transfer back to a regular school (or if they are absent their name won't show). So 5 attendances each day (was just going to put them all on one sheet). I need to see participation in my class for the school year in case a parents asks if they were in class on certain days and for special ed support (they need to keep track of how many days students are coming and if they are actually participating or if they just logged in and walked away - our new system does not have a way of taking attendance other than the reports generated each period). I will need to have every day of school on there so I can see trends in participating/not participating day to day.
I do like the original set up, as the data was all on the same page, with the rest hiding in the back. I'd need to see 2-3 weeks of classes at a time at least. Sorry, I was hoping this was somewhat simple - you are patient man & I appreciate you.