problem.xlsx
I need formula to allocate times for siblings in each group. there are 55 groups, with 22-25 children in each, some of them have 2,3,or 4 siblings.
Appointment last 10 mins, then if siblings: I added 10 minutes time to get to another room for another app, if no siblin: only 5 min between app is needed.
groups look like:(sheet SNST)
child A Group A SIBLING GROUP C SIBLING A GROUP E
child B Group A
child C Group A
child D Group A
child E Group B
child F Group B
SIBLING GROUP C
SIBLING A GROUP E
siblings are in different groups, appointments for siblings look like that: (sheet times)
(first sibling app is 8:40, second at 9:00 third at 9:20, then if 1st sibling app is at 8:55 next app is 9:15 then break and app resume at 10:15......
app for 4 siblings must start when 4 time slots are available so parents dont have to wait during staff break or long lunch.
I've got app times in a table:
08:40 09:00 09:20
08:55 09:15
break
10:15 10:30
10:30 10:45 11:00 11:15 11:30
10:45 11:05 11:25 11:45
11:00 11:20 11:40
lunch
13:15 13:35 13:55 14:15 14:35
13:30 13:50 14:10 14:30
Siblings are in different groups and have appointments within 20 minutes.
Then I've got all NON SIBLINGS: I can not use the app time allocated to any sibling who's in the same group as no sibling.
Finally, when all students have app time allocated appointment in a group must be at least 15 minutes apart:(sheet HT)
group A: child A 8:40 child B 9:00 Child C 9:15 (but not: app at 8:40 and next at 8:45)
in cell E2 on sheet SNST I'm trying to use index/match and small bur getting times repeated in the same group!
Could you help please?
Thank you
Bookmarks