Microsoft Excel - allocate AVAILABLE times (in table) for groups: time to be repeated

I need formula to allocate times for siblings in each group. there si 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:
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 like like that: 
(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......
so app for 4 siblings must start when 4 times 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: they need app within 15 minutes and I can not use the app time allocated to any sibling  who's in that group.
 
Could you advise on any nice VLOOKUP/INDEX/MATCH formula? 
Thank you

Hi Ewa,

I think I have worked out what it is you want but I don't think you are going to be able to 'automate' the process just with formulas.  I am pretty sure that it will need some VBA code and that it would be pretty complex as well.

I am thinking something along the lines of collating the sibling groups for each group (excluding those already dealt with) and ordering them in terms of sibling group size then allocating time slots to suit the group sizes and filling the gaps with singletons.

However, I am thinking it is a far from a trivial exercise.  As a minimum there has to be a unique identifier for each child that can fit them to a class and a sibling group to allow them to be sorted.

Sorry I can't be of more help at the moment but if I have any bright ideas I will let you know.

Regards

Harry

Hello again Ewa,

Not sure if this will help but it might get you started.

Set up your database in a spreadsheet in the following way.  generate the sibcount using the following formula -
=SUMPRODUCT(($B$2:$B$17=B2)*($C$2:$C$17=C2)) in D2 and copy it down.



Now select the data range and Data, Sort, Sort by sibcount, family name and group.

Set up a table of your break start and finish times.  Such as:

in I2:J5
In E2 enter the following formula:
=IF(D2>1,TIME(0,20,0),TIME(0,15,0)) and copy down (format the cells as Time).
In F2 enter the following formula:
=D2*E2
In F3 enter the following formula
=IF(AND(B3=B2,C3=C2),F2-E3,D3*E3) and copy down
In G1 enter your Work start time (before your first interview say 8:20.
In G2 enter the following formula:
=IF(G1+F2<$J$4,IF(G1+F2<$J$2,G1+E2,IF(G1<$J$2,$J$3,G1+E2)),IF(G1<$J$4,$J$5,G1+E2)) and copy down.

Now, I know this will leave gaps in the schedule from time to time and will need to be broken up in some way to spread over a considerable number of days to schedule the appointments that you need but perhaps it will get you started.

I do hope I have gone close to understanding what you want and that this helps in some way.

Regards

Harry

Hi Harry,
thank you for your suggestions, I'll try to work on the file now but I also attached the example of the file: problem.zip 

ALL students (with and without siblings) are on sheet SNST
in cell E2 on sheet SNST I'm trying to use index/match and small  

appointments are on sheet times
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))

We've got only 1 day for appointments (school!) in a past I've done it manually and then would recheck it and recheck etc but it takes all day  Thanks again

Hello Ewa,

Just to be sure I have an understanding of what you want.

As I think I see it now, you want to allocate different teachers to visit a family in an interview room and the teachers are represented by the four letter code.  So the idea is to fill each teachers timetable with time to migrate from room to room if necessary.

On the work book that you included there are some external references.  Should they be adjusted to refer to the time sheet in the book that you sent?

Regards

Harry

Hello Ewa,

I have had a bit more of a trawl around your spreadsheet and there are a few things that I have found.  Not sure whether they will progress the cause or not.

In ColumnF on Sheet HT the VLOOKUP column number should be 4 not 5, otherwise you are trying to look outside of the nominated range.

In Column E on Sheet SNST the formula is finding the nth lowest value in ColumnB on Sheet times by incrementing the number of rows that you are looking at and once it gets to the highest value (latest time) the formula falls over and gives the error.  This is obviously not what you were trying to do.

There is then a circular sort of logic going on where first appointment column on Sheet HT gets times from SNST ColumnE which then feeds to the appointment availability logic on Sheet times.  In that logic, you are trying to compare the teacher initials (ColumnA) with a time (ColumnG) and only able to test one column of the four possible.

In the existing workbook, I can get the appointment availability logic to 'work' by filling an empty column on Sheet HT with the following formula: =IF(ISNUMBER(F2),F2,IF(ISNUMBER(G2),G2,IF(ISNUMBER(H2),H2,IF(ISNUMBER(I2),I2,"")))) in Row 2 and copied down.  Then modify the formula in Sheet times ColumnC to refer to this new column and columnC (for the teacher initials comparison).  It might not be the most efficient way to do it but I didn't want to change what you had too much.

But all of this comes to nought unless the selection in SNST ColumnE can be worked out.

I will think some more about how to populate the first child column but can't promise anything.

Regards

Harry

Hello Ewa,

I made some progress using formulas but I think it is just too complex to do that way.

However, I think I am close to getting it going with a simple macro.

A couple of questions.  Do you want 11:40 to be available for an interview with a two child family?  The note says last appointment at 11:40 but the time is not included in the table. Are there enough timeslots if the interview spacing is 20 minutes?  At the moment your list of available appointment times only show the 20 minute spacing throughout the day which allows 18 appointments and if 11:40 is allowed should we just drop the last one (16:35) off?

Regards

Harry

Hello Ewa,

As near as I can tell, I have a working system but there does not seem to be enough timeslots available in the day with the schedules of appointment times that you have nominated.  I had to use a macro to populate the timeslots in Column E on SNST.  the macro can be activated by a button (Timetable) that I inserted on the page.  Hopefully, I have cleaned up the miscellaneous test cells that I used in various places.

Here is the modified file.

problem.zip

If you want to modify this further, it might be best to get me to do it but be warned that I will be away from home all of next week.

Can you check out the results and let me know what you think.

Regards

Harry

Hi Harry, Thank you so much! it works great, sorry i dint reply sooner, not been well. 
you're right there was not enough time slots, I have edited that now, I forgot that for non siblings appointments can be done every 10 minutes, only or those with siblings I needed appointment within 20 minutes.
Thanks you so much
Ewa

Hello Ewa,

Glad it worked out.  It was an interesting problem to solve.  Hope you stay well.

Regards

Harry