I have a list of attendance information for a school that I want to group by student and attendance date, and then check to see if there are consecutive periods on the same date for that same student and count them if they exist. Here is sample data:
A B C D
1 ID Name Date Period
2 12345 Julie 10/2/2018 3
3 12345 Julie 10/2/2018 6
4 66778 Fred 9/7/2018 0
5 66778 Fred 9/7/2018 1
6 66778 Fred 9/7/2018 2
7 66778 Fred 9/7/2018 3
8 66778 Fred 9/7/2018 4
9 66778 Fred 9/7/2018 5
10 66778 Fred 9/7/2018 6
11 66778 Fred 10/7/2018 0
12 66778 Fred 11/2/2018 0
13 66778 Fred 11/2/2018 1
14 66778 Fred 11/2/2018 2
So I would like to end up with Juie's (ID 12345) grouped, counting her total absences (2) and checking to see if her absences were on the same date (yes they are) and, if so, were the period numbers consecutive (no they're not) and if so, how many are there (zero for Julie)
For Fred, it would group his records for ID 66778, count his total absences (11), look at his absences that are on the same date and check to see if the period numbers are consecutive. Yes he does have absences on the same days (9/7/18 and 11/2/18) and yes there are consecutive period absences (7 on 9/7/18 and 3 on 11/2/18).
Any ideas how this can be accomplished? I've tried doing this with separate formulas to count the different items (ID, days, periods, etc) but don't know how to put it all together.
Thanks for any help!
Laurie
A B C D
1 ID Name Date Period
2 12345 Julie 10/2/2018 3
3 12345 Julie 10/2/2018 6
4 66778 Fred 9/7/2018 0
5 66778 Fred 9/7/2018 1
6 66778 Fred 9/7/2018 2
7 66778 Fred 9/7/2018 3
8 66778 Fred 9/7/2018 4
9 66778 Fred 9/7/2018 5
10 66778 Fred 9/7/2018 6
11 66778 Fred 10/7/2018 0
12 66778 Fred 11/2/2018 0
13 66778 Fred 11/2/2018 1
14 66778 Fred 11/2/2018 2
So I would like to end up with Juie's (ID 12345) grouped, counting her total absences (2) and checking to see if her absences were on the same date (yes they are) and, if so, were the period numbers consecutive (no they're not) and if so, how many are there (zero for Julie)
For Fred, it would group his records for ID 66778, count his total absences (11), look at his absences that are on the same date and check to see if the period numbers are consecutive. Yes he does have absences on the same days (9/7/18 and 11/2/18) and yes there are consecutive period absences (7 on 9/7/18 and 3 on 11/2/18).
Any ideas how this can be accomplished? I've tried doing this with separate formulas to count the different items (ID, days, periods, etc) but don't know how to put it all together.
Thanks for any help!
Laurie