+ Reply to Thread
Results 1 to 8 of 8

Attendance Sheet - Counting unique students in a range that spans multiple columns & rows

  1. #1
    Registered User
    Join Date
    02-07-2015
    Location
    Harrisburg PA
    MS-Off Ver
    2010
    Posts
    5

    Attendance Sheet - Counting unique students in a range that spans multiple columns & rows

    Hello,

    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.


    Question #1:
    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.

    Question #2:
    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.

    Rob

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    Hello and welcome to the forum,

    A VBA script can certainly do what you need, but it is impossible to provide a good answer without seeing your data.

    Can you provide a sample workbook with non confidential data including how the data is at the beginning and the desired final output.
    This will make it easier for someone to answer you.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    02-07-2015
    Location
    Harrisburg PA
    MS-Off Ver
    2010
    Posts
    5

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    I was planning to submit a sample. I just put one together, summarizing what I have tried/am trying to accomplish. I setup the file as a template to be used for future training sessions at the center. This could be utilized to track the elementary and middle school programs.

    In the process, I have tried to provide a sample of the data formatting from the Google Form (gray shaded area). As I stated, Google Forms (CHECKLIST and DROPDOWN LIST) seem to group each student I checked off together in one cell. When working on this, I proceeded to use the TEXT TO COLUMN function in Excel to separate. It seemed necessary for the Pivot Table, however I do not know if it is necessary when tabulating the student's total attendance.

    Thanks for giving this a look.
    Rob

    Attendance-generic-xls.xls

  4. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    Hi,
    See attached file in the tab called Data and click on the button.
    This will reformat the data so you can then do pivot table or other reports. It will also create a unique list of students.
    Hopefully, this will help you continue...

    (Adapt the code as required)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-07-2015
    Location
    Harrisburg PA
    MS-Off Ver
    2010
    Posts
    5

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    GC Excel,

    Thanks so much for your help and putting together a script!

    This task has pushed me to delve into scripting, and now I have some guidance in the process that will make me productive for the rest of the data.

    What you provided is extremely helpful and greatly appreciated.

    Take care,
    Rob

  6. #6
    Registered User
    Join Date
    02-07-2015
    Location
    Harrisburg PA
    MS-Off Ver
    2010
    Posts
    5

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    GC Exel,

    I am working with the script again, working backwards and when I try and run this in 2011 for the Mac, I get a "Run-time error '429': ActiveX component can't create object". When I debug the script it is hung up on the line " Set Dic = CreateObject("Scripting.dictionary")". I am trying to determine why the script isn't working. The syntax looks correct. It runs on Excel 2013 on the office laptop. It just won't run on my Mac at home. I am just trying to understand the logic.

    Thanks,
    Rob

  7. #7
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    Hello,

    I'm not familiar with Excel on Mac but with a bit of googling, my understanding is that Mac does not have the reference to the "Microsoft Scripting Runtime" dictionary that is used in the code I provided.

    See this link: https://sysmod.wordpress.com/2011/11...ng-dictionary/

  8. #8
    Registered User
    Join Date
    02-07-2015
    Location
    Harrisburg PA
    MS-Off Ver
    2010
    Posts
    5

    Re: Attendance Sheet - Counting unique students in a range that spans multiple columns & r

    Thanks for the clarification.

    Interesting find. This is good to know. This is another challenge to see if I can get this script running with the Dictionary.cls that Patrick O'Beirne @ sysmod created.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting Unique Elements Across Multiple Non-Consecutive Columns
    By Lemguin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2013, 10:07 PM
  2. Replies: 1
    Last Post: 02-18-2013, 01:46 AM
  3. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  4. Excel 2007 : Students attendance sheet
    By hemagoutam in forum Excel General
    Replies: 4
    Last Post: 04-18-2012, 04:40 AM
  5. Help in tracking my students' attendance
    By changokid in forum Excel General
    Replies: 0
    Last Post: 02-11-2005, 03:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1