Student Schedules

Copper Contributor

Good evening,

 

I am trying to create a comprehensive scheduling excel document of students at my school. My school operates on an 8 period day (A, B, C, D, E, F, G, H). However, when students have a scheduled "free period" that block is omitted entirely from my excel spreadsheet listing student ID, Name, Course, Teacher, etc.. 

 

I am trying to find a way to create a conditional whereby when there is a period missing in the sequence given above for a particular user ID, a new row is created (preferably with the letter of the missing block). This sequence should repeat for each unique user ID in the table. 

 

IDStudent NameBlockCourseTeacher
123456Samwise

A

... 
123456

Samwise

C... 
123456SamwiseD... 
123456SamwiseE... 
123456SamwiseF... 
654321FrodoA... 
654321FrodoB... 
654321FrodoC... 
654321FrodoD... 
654321FrodoE.... 
654321FrodoF... 
654321FrodoG.... 
987654AragornA... 
987654AragornF... 
987654AragornG... 

 

 

2 Replies
What would your desired output then from your given sample data?

@quaranteach If I read correctly, if students have a free period (some might 1, some might have more than 1, and some might have none) that period will not show up on their schedule, so you want to ensure all periods are accounted for whether free or otherwise.  Therefore, I ignored the "Blocks" in your table and assumed that all students should have all blocks (A-H).  

 

In the original table, there were 3 unique students.  I created a separate table to manage the # blocks each student should have (8 blocks if using A-H).  Using a combination of both the unique student list multiplied by the number of blocks (A:H) tells us we should have a total of 24 records for the base schedule.  

 

Note this formula requires Excel 365 to work:

The formula uses the number of blocks to determine how many times the student ID/name combination should repeat, and then uses the number of students to determine how many times blocks A-H should repeat.  Lastly, it combines the result of those two formulas.  The animated picture shows how a single formula (entered once & only in cell J2) will update based on either changes in the number of blocks or number of students.  

 

=HSTACK(CHOOSEROWS(UNIQUE(tbl_StudentList[[ID]:[Student Name]]),ROUNDUP(SEQUENCE(COUNTA(tbl_Blocks[Block Possibilities])*COUNTA(UNIQUE(tbl_StudentList[ID])),1,1,1)/COUNTA(tbl_Blocks[Block Possibilities]),0)),INDEX(tbl_Blocks[Block Possibilities],MOD(SEQUENCE(COUNTA(tbl_Blocks[Block Possibilities])*COUNTA(UNIQUE(tbl_StudentList[ID])),1,1,1)-1,COUNTA(tbl_Blocks[Block Possibilities]))+1))

 

2022-09-09_22-47-03.gif

 

This is likely an over-simplification of what you need with a formula syntax which is very difficult to understand.  :) 

 

But I hope this helps achieve what you're seeking.  

 

Dexter