Sep 09 2022 06:41 PM
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.
ID | Student Name | Block | Course | Teacher |
123456 | Samwise | A | ... | |
123456 | Samwise | C | ... | |
123456 | Samwise | D | ... | |
123456 | Samwise | E | ... | |
123456 | Samwise | F | ... | |
654321 | Frodo | A | ... | |
654321 | Frodo | B | ... | |
654321 | Frodo | C | ... | |
654321 | Frodo | D | ... | |
654321 | Frodo | E | .... | |
654321 | Frodo | F | ... | |
654321 | Frodo | G | .... | |
987654 | Aragorn | A | ... | |
987654 | Aragorn | F | ... | |
987654 | Aragorn | G | ... |
Sep 09 2022 07:15 PM
Sep 09 2022 11:05 PM
@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))
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