Need help with a formula for scheduling based on duration and priority of events

Hi dear Excel gurus!

I have a an issues coming up with a formula to create a schedule based on a given input.

Please find attached a spreadsheet outlining the problem with given scenarios and desired OUTPUT.

Scenario 1 = output schedule 1 (with emphasis on high priority events)
Scenario 2 = output schedule 2 (with emphasis on evenly spacing out high and normal priority events)

I am not quite familiar with functionality of Excel and wonder if any array formula would do the job. I have seen people do magic here with Excel and thought to give it a try.

Always eager to learn new things

Thanks in advance! Any help/idea will save the day!

-T

Dave,

To answer your question, this is a coincidence. It will depend on number of objects (7 in the scenarios but could 4,5,10,etc...) The count will vary according to the duration input data (3,6,5,12, etc..). I guess the high priority data is the data that has a higher duration (days). Normal priority has always 1 day duration. Does this answer your question?
Thank you,
Dave[/QUOTE]

Yes sir. Let's say if we had 4 objects and had #4 as 'high' priority with duration of 3, the output in scenario 1 would be:
day1: #4
day2: #4
day3: #4
day4: #1
day5: #4
day6: #4
day7: #4
day8: #2
day9: #4
day10: #4
day11: #4
day12: #3
and so on

I meant to say in scenario #2. Scenario 1 would be:

day1: #4
day2: #4
day3: #4
day4: #1
day5: #2
day6: #3
day7: #4
day8: #4
day9: #4
day10: #1
day11: #2
day12: #3
Day 13: #4
Day 14: #4
Day 15: #4
and so on. I hope you understand the logic I am trying to implement. I am not sure if I need to have a reference table for help or... Thank you for working on it!

This is amazing! Thank you! Let me know if you come up with anything on Scenario 2, @bebo021999
I wonder how I can learn Excel so well!

Thank you both for working on this!
I wonder if scenario 2 has to be done with VBA...

Please feel free to add help columns such as a priority column from (1-7 in this case) or anything that can facilitate the calculation.
The solution doesn't have to be as elegant as the one for scenario 1.

Anyway, thanks a lot so far!

Hi guys,

I tweaked the formula so we don't have to use 'high' and 'normal' to solve in column I.

I figured that I could rank the names in the input any way I want to get desired order.

=IF(R6>SUM($G$5:$G$14),INDEX($S$5:$S$431,MOD(R6-1,SUM($G$5:$G$14))+1),IF(COUNTIF($S$5:S5,S5)<VLOOKUP(S5,$F$5:$G$14,2,0),S5,INDEX($F$5:$F$14,SMALL(ROW($F$5:$F$14)-MIN(ROW($F$5:$F$14))+1,SUMPRODUCT(1/COUNTIF($S$5:S5,$S$5:S5))+1))))

I still cannot come up with an algorith to create the Scenario 2 pattern:

I tried to use the output from the first scenario and modify it but haven't had any luck so far. Let me know if you think of anything please.

T