Solved: Difficult one!!!

[Ismael]

Hi to all,


In first place congratulations for the excellent forum that you have.

I have a doubt that I don?t now if it?s possible to solve with EXCEL, but I would like to try. So what I want is similar to a Gantt chart, in sheet one I have a group of values that have a beginning time and a end time.

In column B and C I have the name of several flights (column B name of company, column C number of the flight), in column D I have the beginning time, in column E the end time, in column F I have numbers that indicates how many check-in I need for each flight.

In sheet 2 I have 6 groups (A, B, C, D, E and F) that represent the total of check-in desk. I have made a little example in this sheet, in a way that you guys can understand what I want and try to help me if possible.

You guys can see that for the last flight that I represent I have 2 empty check-in (23 and 24) in group A, that happens because the flight LK 300 need 3 check-in desk and they have to be contiguous, in other words they have to stay all in the same group, they can?t stay 2 in one group and 1 in other group.

I don?t now if this is possible to do automatically in EXCEL, so if you guys can give a help I really appreciate.

Best regards,

Ismael

[K. Georgiadis]

Ismael, I'm not exactly sure what you are trying to do but you may want to read this article by Jon Peltier, about using Excel to draw Gantt charts:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

Jon Peltier also has an excellent website regarding Excel charting in general:

http://www.geocities.com/jonpeltier/index.html

I hope that one of these two links will put you on the right path

Ismael, here is another link that will branch you out to other resources re: Gantt charts:

http://peltiertech.com/Excel/Charts/GanttLinks.html

[Ismael]

Hi, Georgiadis

I will see this links and then I tell you something.

thanks

regards,

Ismael

[K. Georgiadis]

check Jon Peltier's tutorial regarding "floating bar" charts which is basically how Gantt charts are created in Excel

[Ismael]

Hi Georgiadis,

I already see all the examples in the Jon Peltier site, and for my misfortune, the examples doesn't help me to solve my problem.

I was thinking and look's to me that the better approach is the VBA, this because, if I imagine the range B3:IU79 like a "matrix" were I have space to fill, and if I make a find in the range C3:UI3 (Sheet 2) of the value in sheet1 of C4 and D4 and I check if the "matrix" have empty spaces or not, maybe I can now if I can put or not the flight in the "matrix".

I think I have to do the check inside the "matrix" column by column in order to see if the I have all values between C4 and D4 (shett1) empty in the range c3:UI3, then I have to see how many rows I have to fill (this information is in F4 sheet 1), and then make some kind of a loop to the rest of values.

Not that I don't need to fill the space's in sheet2 with colours, I can fill all the range that belong to the flight with the number, for example in the first case with KL 1692, and then make a board to see this better.

I don't now If I explain this in a way that you can understand, sorry but English isn't my native language.

The bigger problem that I see in this is that I don't have the knowledge
to do this in VBA, so I count with you guys to give me a help if possible ok?

Thanks in advance,

Best regards,

Ismael

[K. Georgiadis]

Sorry that I couldn't be of help. If your problem requires a VBA solution, I shall have to defer to the real coders that make this site the great place that it is. I often have to turn to them for help. Good luck!

[Ismael]

Hi Georgiadis,

I hope that the gurus of VBA can help me....

Thanks for your help

best regards,

Ismael

[Paleo]

Hi Ismael,

looks like you need Jake or Zack here to help you, because its trully a difficult one:thumb .

I have simply no idea on how to solve it:banghead: .

[Killian]

Hi Ismael,
do you use this to view the data or do you need a way to find free desks together in one group when you have a new filght to book in?

[Ismael]

Hi Killian,

In fact I need a way to find free desks together in one group when I have a new filght to book in, is something like that, the data that is in sheet1 is all the flights that existe for one day, what I need is a way to allocate this flights to free desks together in one group. this data only change in the next day.

I hope this help,

best regards,

Ismael

[mdmackillop]

Hi Ismael,
Are you still looking for a solution?

[Ismael]

Hi Mdmackillop,

yes, in fact I still looking for a solution, I have try whit another application but I didn't have success to, so until this moment I don't have any solution for this.

So if can help me, I will be very grateful.

Best regards,

Ismael

[Bob Phillips]

yes, in fact I still looking for a solution, I have try whit another application but I didn't have success to, so until this moment I don't have any solution for this.

So if can help me, I will be very grateful.

Ismael,

I would have thought that a VBA solution to this is pretty straight-forward, PROVIDED that it is done on a first come first taken basis, i.e. loop through the list and allocate to the next available group. Of course this is not optimising it, and it might (will?) mean that some desks will be unallocated because there are not enough in that group for the next flight, where moving another flight would create space. And of course, it could also show that some flights cannot get allocated this way.

If you want optimisation, I for one am out of it, you are talking major development here where VBA is probably not the right tool.

Thoughts?

[Ismael]

Hi XLD,



In fact you have reason, you have understand perfectly what I pretend.

But for beggeing with this, I don?t need to optimize tha allocation of the different?s flights, and doesn?t exist any problem if are groups of check-in that at the end stay empty?s, of course that will exist flights that cannot be allocate.



I don?t now if VBA can do this, and I dond?t now how to do it, so if you guys at the forum can help me, I really appreciate.

Best regards,



Ismael

[mdmackillop]

Hi Ismael,
I've an idea of an approach that I'll check out this evening. Asd XLD stated, it won't be "optimised", but hopefully will produce a result.
Regards
MD

[Ismael]

hi MD,

Thank you very much for your help.

Best regards,

Ismael

[Bob Phillips]

Ismael,

Here is a first draft. Take a look and see if it is along the right lines. I know it is all one colour, but see if the principle is correct and I'm okay to finish, otherwise do I just give up?

[Ismael]

Hi xld,

Men this is amasing...:clap: very good.

I have made same experiences, and every thing works perfectlly.



The flighs are alocated to one group only (that is what we pretend to happen), so it seems that is every thing ok.



So I think that you can continue?.(what I realy thank you).



Xld, redargin the colors, I have one ideia, I don?t how to do it, but you now for sure that is:



MWE had made one function that associate several colors with the several flights (you can see this function in the attach file). this is one ideia that I tested, to try alocate the flights, but I didn?t have success (this was a combination of EXCEL whit a SW that I use (simulator of airports) but dind?t work), so maybe this can help you



XLD, I just have one question to you its possible type the hours to? For example the macro that you have done type the number of flight that are alocated to a check-in group, it?s possibel type also the hours? Something like this:



4:00 KL 1692 6:00



In fact is similar with the groups that you can see in the attach file when you run the macro to group all the data.

So XLD, once more thanks men :beerchug:

Best regards,

Ismael

[Ismael]

Hi again XLD,

Did you remeber this file (on attach) that you have made me?

I think that if you use this limits will be more easy to see the file, becuse you will reduse the columns to range (C:AS).

take a look at it and see if you can do this.

best regards,

Ismael

[Bob Phillips]

Here is a shot with colour, and button invoked. I also found some errors on subsequent runs that I think I have fixed.

I have used my colour allocation algorithm, I may get around to looking at the other later.

One enhancement that I thought would be good would be to allow a fight to be pre-allocated, so yoo could specify in the flight table the (first) desk to be used, and then the first pass would allocate these, the second pass would take up the slack. That way you could adjust it until it gets a more even distribution - sort of a poor man's optimisation. Thoughts?

[mdmackillop]

Hi Bob,
That looks great to me; just what I was going to suggest! I'll go and have a cup of tea (or maybe a beer) instead.
Regards
Malcolm
:beerchug:

[Bob Phillips]

Hi Bob,
That looks great to me; just what I was going to suggest! I'll go and have a cup of tea (or maybe a beer) instead.
Regards
Malcolm
:beerchug:

I would have thought that a man from your neck of the woods would be drinking the uisgebaugh.

Read something interesting once along the lines of beer being the ancestor of whisky (no argumentys so far), which, in simple terms, is just distilled beer (sacrilege!).

[Ismael]

Hi XLD,

In first place sorry the lasted reply.

I have tested the file again and it seems that I find same errors.

So lets see, in the attach file you can find this 2 flights LH 4537 and LK 300 both with 13 desk, so they must be allocated to group E and F, but this doesn?t happen.

Another thing is, the flights that cannot be allocated because doesn?t exist free desks doesn?t go to any were, my question is:

It?s possible type in same place the flights that aren?t allocated? In the case of the file attach the flight that must to ?jump? is the TP 920.

Now I will try to explain you my ideas about the several possibilities of allocate check-in that are:

When you ask about pre-allocated check-in, this it seems a very good idea, because imagine that I have a company that I want to allocate just to one group of check-in. example:
All the flights of Lufthansa (LH) must be allocate to group 14-17, a solution that allow us to do this will be very useful.

Another thing is when we make the allocation first came first serve, we allocate the flight to the first group available, why we do this? A better idea wasn?t allocate the flight to the group that are more check-in available? I think so, but maybe this will complicate the code?


Another idea is, have a table of flight that must to be allocated and allocate this flights first, and then allocate the rest of the flights, this will be very useful to.
So this are same idea that could be done, I don?t if this can or not be done in VBA, but I would like to have this kind of solution.

Regarding the algorithmic of the colors:

Your algorithmic works ok, but if you mark one color for each company will be nice, because you will identifier better were is this flights of the company.

So I don?t now if this is asking to much, but if you can do this I will be very grateful (and my boss to).
Best regards, http://vbaexpress.com/forum/images/smilies/beerchug.gif
Ismael

[Bob Phillips]

I have tested the file again and it seems that I find same errors.

So lets see, in the attach file you can find this 2 flights LH 4537 and LK 300 both with 13 desk, so they must be allocated to group E and F, but this doesn?t happen.

Okay, I will take a look. Would my pre-allocation idea work here?


Another thing is, the flights that cannot be allocated because doesn?t exist free desks doesn?t go to any were, my question is:

It?s possible type in same place the flights that aren?t allocated? In the case of the file attach the flight that must to ?jump? is the TP 920.

Not sure I understand all of that. Do you mean that items that don't get allocated don't show in the error list? And what do you mean by 'jump to'?


Now I will try to explain you my ideas about the several possibilities of allocate check-in that are:



When you ask about pre-allocated check-in, this it seems a very good idea, because imagine that I have a company that I want to allocate just to one group of check-in. example:
All the flights of Lufthansa (LH) must be allocate to group 14-17, a solution that allow us to do this will be very useful.

Another thing is when we make the allocation first came first serve, we allocate the flight to the first group available, why we do this? A better idea wasn?t allocate the flight to the group that are more check-in available? I think so, but maybe this will complicate the code?


Another idea is, have a table of flight that must to be allocated and allocate this flights first, and then allocate the rest of the flights, this will be very useful to.
So this are same idea that could be done, I don?t if this can or not be done in VBA, but I would like to have this kind of solution.

I have already written the pre-allocation code. It has no error-checking, so you can double-allocate, but you should be able to check that and re-allocate. Does pre-allocation, and adding them in priority order overcome all of those issues then?


Regarding the algorithmic of the colors:

Your algorithmic works ok, but if you mark one color for each company will be nice, because you will identifier better were is this flights of the company.

Colouring by airline is a better way I agree. We could do it one of three ways:
1 - allocate from my list on the next come next allocated basis (by airline, not by flight as is now)
2 - create another table of airlines and colours, and lookup at run time
3 - colour the flight list airline column in the preferred colour, and then the code picks that up (of course you could make mistakes, but it can always be rectified, you could also set the font colour this way).

My preference is 3.



So I don?t now if this is asking to much, but if you can do this I will be very grateful (and my boss to).


Great, so he will be quite happy to settle my bill then?

[XL-Dennis]

Great, so he will be quite happy to settle my bill then?


Bob - You should have take care about that before starting up the project.

In my opinion Bob have made a great work and it's beyond the general help that is provided on free public forums like VBAX.

Ismael, it would be nice to see if You can make some input Yourself.

Kind regards,
Dennis

[Bob Phillips]

Bob - You should have take care about that before starting up the project.

Dennis, I hope you realise that that is English humour http://vbaexpress.com/forum/images/smilies/001.gif

[XL-Dennis]

Bob - English and Swedish humour is quite similar so I fully understand ;)

However, sometimes when I lurk around I see work that are far beyond the scope of what people can expect from public forums and then I make a post about it like the above with a more serious approach - that's all.

Kind regards,
Dennis

[Ismael]

hi XLD,

thanks for all the help that you are given' me.

I will prepar one awnser and then I will post back in a few moments ok?

best regards,

ismael

[Ismael]

Hi again XLD,

Sorry for the lasted reply, and once more thanks for all the help that you are given? me.

I will talk with my boss and see what I can do, I can?t promise anything, but I will try.

The only thing that I can promise is, if you came to Lisbon (in vacation or work) you can count with me, I will have whole the pleasure on sow you Lisbon and drink a few beers with you ok?

So regarding the questions that you asked, let see:

When you ask if the flights that don?t get allocated doesn?t sow in error list, I think that type the flights in error list can be a perfect solution, but I try this and didn?t work, maybe I have done something wrong. Because in the sample that I send you the flight that don?t get allocation is the TP 920 and this name doesn?t appear on the error list.
In resume what I pretend is:

Try to allocate all the flights, but this isn?t possible so they will always exist same flights that cannot be allocated, so a pre-allocation doesn?t resolve this problem.

What I said was if isn?t possible to type in one place what are the flights that aren?t allocated, in order to can say this:

For example the day 12 of December have 150 flights, and I only can allocate 142 flights, the other 8 must go to another place, so if you have a list that are updated when we run the allocation at the end it?s easy to see what are the flights that wasn?t allocated.


Note: the ?jump to? is only one expression that I use to say if the flight cannot get one allocation in all the desks then jump of the list.

About the code of the pre-allocated I think that this can solve my problem, but I have to see and tested to have sure.


Regarding the algorithmic of the colors I think that your 3 suggestion is the better one to.
So XLD, if you can continue to help me I will be very happy.
Best regards, http://vbaexpress.com/forum/images/smilies/beerchug.gif http://vbaexpress.com/forum/images/smilies/friendship.gif
Ismael

[Bob Phillips]

Sounds as though we are in agreement Ismael.

Here is version (#3 I think http://vbaexpress.com/forum/images/smilies/001.gif).

I have done the following:
- corrected some of the error handling - it didn't process non-allocation properly, so you should see any errors on the error list
- added a pre-allocation column - I have entered 4 values just to demonstrate it, using this facility means that you can pre-determine which group some flights go in should you wish
- added the colour coding - totally configurable as you can change the colours on the flight list, you could even do it by Even lookup code to make it more automatic.

I think (hope) that you will like this. Let me know, meanwhile I will tidy it up as the code has gotten somewhat awkward, and see if it needs any more bells and whistles.

[mdmackillop]

Hi Bob,
I noticed some repetition of the first allocations at desks 14-18 and 75-81, but I've not checked any further.

[Bob Phillips]

Morning all,

Another day, another version.

Malcolm and I have thrashed out the repititions, and so hopefully this is a full working version.

[Ismael]

Hi XLD,

Believe you or not when I receive the notification of a new message in the forum I was typed a message for you, were I will ask you if wasn?t possible don?t duplicated the allocation of the flights, but you? are amazing, because you have already done this.

Thank you so much, for all the help.

I already see and tested the new file and it seems that everything works perfectly.

I just have one more suggestion for you that is:

We have define that if a flight end at 12:00, another flight can be allocated to the same desk at 12:00.

But one thing that will make the code better is we can define a time between 2 flights at the same desk. For example if a flight live the desk at 12:00 the other flight only can be allocate to the same desk past 15 minutes, this in order to create something like a time between events. Because imagine that we have a company (LH) allocated to desks 14 and 15, and the flight ends at 12:00, then if we put for example another company in the same desks (14 and 15) we don?t give time for LH live the desks and the other company occupy the desks. I don?t now if understand what I?m trying to say.

So my suggestion is:

If we have a flight that belong to the same company we don?t need to make a ?changing time? because the person that will make the flight is the same, but if we are changing of company we have to give a ?changing time?, it?s obvious that this will reduce the number of flights that will get allocation, but I think this is the better solution, don?t you think the same?

Your algorithmic of colors is perfect now, I don?t now what can I say to you, your are the men.....:clap:

Best regards, :friends:

Ismael

[Bob Phillips]

Ismael,

Are you saying that if say we try to start a TP flight immediately after the end of a BA flight, that we should bump the time up by 15 minutes and see if it still fits? If so, what time do we show on the display, the allotted time, or the new time,

I am not sure this is doable (well it is doable, but I am not sure I have the appetite for it), as you need to check 3 time slots before for each of the number of desks required, and if it fails, bump it up by 15 minutes, and try again. Presumably we only bump up once.

I will think about it, but it probably means a re-design of the desk allocation routine, so abolutely no promioses at this point.

[Ismael]

XLD,

I think you have understand what I have said, but I thought that this could be more easier, but it?s ok, you have already done me one excellent help.

When I say that if the flights are from the same company we don?t need ?changing time? (were I?m optimizing) because a solution that presume a ?changing time? for all the flights (doesn?t matter if is from the same company or not) would be good to.

Note that the time of the flight doesn?t change, and we want to continue to display the allocated time.

Were maybe you don?t have understand very well what I try to said (take a look at the example on the attach file).

So XLD take a look at the file and see if was this that you have understand ok?

Best regards,

Ismael

[Bob Phillips]

XLD,

I think you have understand what I have said, but I thought that this could be more easier, but it?s ok, you have already done me one excellent help.

When I say that if the flights are from the same company we don?t need ?changing time? (were I?m optimizing) because a solution that presume a ?changing time? for all the flights (doesn?t matter if is from the same company or not) would be good to.

Note that the time of the flight doesn?t change, and we want to continue to display the allocated time.

Were maybe you don?t have understand very well what I try to said (take a look at the example on the attach file).

So XLD take a look at the file and see if was this that you have understand ok?

Best regards,

Ismael

You are correct, I didn't fully understand as I was thinking you meant adjust its time (very odd thing to do). Spookily enough, this was going to be my suggested alternative, but I think the problems still remain. Let's see.

[Bob Phillips]

Turned out to be easier than I expected. All I needed to add was this code


'check if gap required and available
For k = iStartCol - 3 To iStartCol - 1
If .Cells(j, k).Interior.ColorIndex <> fallALLOC_CI And _
.Cells(j, k).Interior.ColorIndex <> pzInteriorCI Then
fAvailable = False
Exit For
End If
Next k


everything else was in place.

[Ismael]

Hi again XLD,

In fact you are the men. :bow: :clap: very well done.

I see that you have added a new formatting for the flights that have a pre-allocation defined.:thumb I like that.

The intervals are just like what I pretend. In fact all the file is just like what I pretend.

I don't now how can I said to thank you.:beerchug:

I will mark the threat as solved.

best regards and many thanks,

Ismael

[Ismael]

Hi again,

XLD, I have tested the file and everything seems to work, but now, when I put same new data I obtain one message of an error.

You can see the error on picture attach.

I discovery that you send to the error list the Airlines that don't have a color defined, very nice.

So, I don't if I can ask you any more thing, but...if you can take a look at it.

Best regards,

Ismael

[Ismael]

Hi,

I have discovery why this is happen, the first flight must to start at 3:15.

If we put 3:10, we will not obtain an error, but if we check in the error list we will see a message saying that "unable to allocate the flight".

best regards,

Ismael

[Bob Phillips]

Hi,

I have discovery why this is happen, the first flight must to start at 3:15.

If we put 3:10, we will not obtain an error, but if we check in the error list we will see a message saying that "unable to allocate the flight".

best regards,

Ismael

Don't do that, there is an error in the code. I didn't handle anything starting before 3:15, or whenj the start time equalled the end time. Here is another version.

[Ismael]

Don't do that, there is an error in the code. I didn't handle anything starting before 3:15, or whenj the start time equalled the end time. Here is another version.

Good Morning,

Ok I think that now everything is perfect.

Bob, I already say this, but thank you very much for all the precious help that you give me.

best regards,

Ismael :thumb