Advanced Match and Relationship Challenge

[Danno]

I have to figure out a configuration to train the maximum amount of classes. I can do this once I know how many aircraft are required. Tab 1 is a simple Gantt chart that indicates the class along the row, and the date across the column. Within each square of the Gantt chart I placed 2 digit code (occasionally there are two codes) symbolizing one of 13 resources used to train the task on that day.


Most of my training devices are aircraft, and each aircraft can be used to train up to two classes at the same time. Some training tasks are not compatible to share an aircraft with others, as reflected in Tab 2, a relationship map of every conflict (built from the tutorial on this site).


The formula I am trying to develop will total the aircraft required under each day. If the tasks are compatible, it will combine two of them and assign one aircraft; if not compatible, the formula will allot one aircraft (instead of sharing with another task).


Hope this makes sense, because I’m not sure how to develop a suitable formula.


This is a cross post of http://chandoo.org/forum/threads/formula-work-aircraft-maintenance-training.29154/ that is hopefully more clear since I have not yet garnered any attention.
Thank you so much!

 

[NARAYANK991]

Hi ,

Please clear up my confusion.

1. Which tab(s) and which range(s) have the raw input data ?

Is it possible to have all of the input data in one tab ?

2. What is the purpose of the two tabs named PTT Relationship Map and Relationship Data & Calc ?

3. What will the final output look like ?

Narayan

 

[Danno]

Of course,
1. Tab 1 the actual Gantt has raw input of the training schedule. Tab 3 has the input of the relationships top chart (1s and 2s). Organization and appearances are absolutely no factor. I am creating an internally used tool to help find the most efficient schedule that will use all my training devices. So if you would prefer to put all input on one tab it should be easy.
2. The Relationship Data & Calc tab builds the PTT Relationship Map which I thought would be useful to build the final formula. If the tasks are compatible, it will combine two of them and assign one aircraft; if not compatible, the formula must allot one aircraft (instead of sharing with another task).
Example: Adding AF and TR require 2 aircraft that day because they conflict but if it's AF and EL on the same day then 1 aircraft because they don't conflict.
If there is a formula that can do this, I will have a tool that will allow me figure out the most efficient way to structure class starts, maximize our trainers, and know the exact amount of students that can be trained. So it's pretty exciting, but after creating the rest of this document I got hung up on a formula I don't know where to start with.

Let me know if that helps, thank you!

 

[NARAYANK991]

Hi ,

If we can find the best way of specifying conflicts among aircraft , that will help.

1. Is it that the conflict between two different types of training will always be the same , or will the conflict depend on the days or dates ?

What I mean is , will AF and TR always be in conflict or will it depend on the days or dates when they occur together , so that on some days / dates they will conflict while on other days / dates they will not be in conflict ?

2. Can only 2 tasks be used for 1 aircraft ? So will 3 tasks , even if they do not conflict with one another , mean at least 2 aircraft ?

3. Why does the depiction in the tab named GANTT follow a staircase design ?

Why are there only 2 cells populated in columns E through H , whereas column BZ has 30 rows of data ?

Why is row 4 blank after February 12 ?

There will be many more doubts , and it will help if you can explain the concept behind the depiction in this tab.

Narayan

 

[Danno]

So I have a large fleet of aircraft I use to train thousands of students a year. The aircraft are identical and on any day, I can put up to two classes on an aircraft. But only if they don’t get in each other’s way. MR and MR get in each other’s way, so they would need two aircraft (as would MR/EL, MR/STX, MR/IN, MR/HF, and MR/PP). MR and UT do not get in each other’s way so they could use one aircraft. So I am trying to build a formula that knows how to add all the tasks and can take into account what conflicts and what does not. At the bottom of each day, it will tell me how many aircraft are required. This will allow me to adjust the schedule (the colored rows on the first tab), adding classes and changing start times to the point where I know exactly how many classes I can run with the aircraft I have. To answer your question here, the rules are consistent every day.
2. Can only 2 tasks be used for 1 aircraft ? So will 3 tasks , even if they do not conflict with one another , mean at least 2 aircraft ?
Absolutely true. You got it.
3. Why does the depiction in the tab named GANTT follow a staircase design ?
Because I currently schedule a new class to start once or twice a week, and the classes last about four months. It illustrates classes starting and graduating over time.
Why are there only 2 cells populated in columns E through H , whereas column BZ has 30 rows of data ?
Most columns will have many rows of data, about 30. But this is something I will be adjusting once I have a way to total aircraft required in each column. It starts at a low number to simulate starting classes on 4 Oct 2016, although it shouldn’t really matter.
Why is row 4 blank after February 12 ?
I think row 4 is actually blank after Feb 7 for all purposes, illustrating that that class has graduated and is no longer requiring any resources.
Hope that helps, ask away if there are more questions and I am happy to explain.

 

[screach]

Really curious to see how this pan's out. This would be relatively easy to do in a database format, so I am intrigued how Excel will be able to handle it.