I work at a non-profit and I've been using Google Forms to track student sign in attendance. I downloaded the Form Responses to an Excel document and I've been trying to solve a problem to determine students individual total attendance throughout a quarter (Fall, Winter, Spring, Summer). I may need to rethink how I setup the Google Forms to make this process easier.
I've been looking at Pivot Tables, Duplication, Filters, and haven't found a solution and I think I may have to look at a VBA option.
Here is a brief example of the way the attendance data has been established:
1/2/15 Student A Student B Student C Student D
1/3/15 Student B Student D Student A
1/4/15 Student C Student A Student B Student C Student E
For the most part, I started using Google Forms to track daily total attendance. This allows me to see the daily running capacity, or capacity during yearly quarters. For example, if the program allows for 5 maximum students per day, 1/4/15 would be a day operating at 100% capacity, 1/2/15 = 80% capacity, and 1/3/15 = 60% capacity.
I now am trying to take this to the next level. How do I get Excel to compute the following as a list of UNIQUE students with TOTAL ATTENDANCE:
Student A = 3 days
Student B = 3 days
Student C = 3 days
Student D = 2 days
Student E = 1 day
This helps me to know that Students A,B & C have attended the most days. I know this is easy with the COUNTIF function, but this is not feasible for a list of 75 days/rows (x-axis) and a possible 15 columns (students on the y-axis). On any given day our program can have 15 students in attendance. Also, another factor is that there are blanks in some of the slots because of the fluctuating daily capacity.
Furthermore, if I could get Excel to generate a list of all the UNIQUE STUDENTS in the range of columns & rows, then I could use the basic COUNTIF function to generate the totals. I am stuck in trying to generate a unique list that spans both columns and rows. What I am finding is that data needs to be in the same row or the same column for Pivot, INDEX/MATCH/LIST to work, ARRAY, Duplicates, or Filters features to work. I don't know how to get Excel to sweep through a large area to tell me which duplicates it finds and how many. Maybe I am wrong.
I am looking at reverse engineering the Google Form so that the data is generated in a more manageable way. The I have setup the Google Form, is by using a CHECKBOX type question that lists all the students that are currently enrolled in the program. This is the easiest for me to quickly go through enter DATE in a date question, and then checkoff who is present on that given day. The issue with that is the Response Sheet for the Google Form, treats the CHECKBOX question as just one CELL. I have multiple students within that cell, however they are Comma Separated, which can be quickly expanded to separate cells using the TEXT TO COLUMNS function in Excel. The issue is that this puts the data into the format listed above. I have not been able to find a viable solution.
I am open to suggestions, because I would like to make this process (1) easy for other staff to complete this process, (2) seamless for data reporting.
I have two years of attendance data that would be helpful to present to staff and directors. Maybe I am making this more complex. If I need to rearrange the data, please let me know of any scripting that could be done. If a VBA script is needed, I am okay with that process, but I am not that familiar with how to go about it.
Thanks in advance for any help, insight that other forum members may provide.