Planning machines with dependencies- based on start date and duration

Hello Everyone,

I have done many thing with excel and learned a lot of VBA with this forum's help, and I have got much help with brilliant coding VBA from this forum.

edited to add specifics of machines working hours/schedule and attached edited file also)

Usually I get half code by myself, or at least some code, and then come here for help. But this is a tough one for me: it's been a week now searching the forum and internet for some input or at least a similar code/solution but this time I have achieved nothing.

(I have never created a Gantt, not to speak of a dependency Gantt and I suspect VBA may be a immense help here rather than classic Gantt)

I have to plan production schedule in my work place. Now this is a very different animal form a previous normal planning I have been doing couple a years ago.

I only have two machines, but pace is fast. Production orders have multiplied a lot and I can not manage them anymore, nor plan without a planning of some sort.

What I have been trying to obtain is: when inserting orders in the sheet Prod ORders and durations, to fill out the hourly schedule of the two sheets of the two machines (please see attached file), based on priority order, and start date (which is initialy the date when the production order is issued)

1. IF, for example, on machine 1A, item x1 production has priority n.1, the item with priority n. 3 on the same machine 1A which in my file is item X3 production, can not initiate untill item X1 is finished.

2. Of course, even if I give a start date in the Prd orders sheet, if the machine is already busy in that date, items should populate the machine Gant in the next date and hour available

3. Based on the machines schedule when machines calendar has been filed out, I would need it to give me an end date for each item based on where the item has really found place on the machine calendar. Also, if the start date is not realistic, it should correct that one too ( in the prod order and duration sheet)

4.Machines will work from 8 am to 3 am (night)

4. Is there a VBA code, and which kind (macro? change event?) that can populate machines sheets from the Prod Order and Duration sheet as per my points above?

Any kind of input, help or suggestion would very much help.

At the moment I am walking around with pen and paper, trying to understand where and when can I start production for an item, priorities; and pruduction team and me are really in a big fog/mess at this point. Orders are starting to come in numerous and I see a big mess ahead...

Thank you in advance, any help wil be much appreciated.

Carim thank you very much for your reply and input.

I will reply point by point:

1

1. Whenever you are using Excel, NEVER EVER use MERGED CELLS ...

well noted, I will correct that!

2.You seem to have hours and minutes in your data ... and days in Gantt .. tough to reconcile

I have hours and minutes in my prod ordrs sheet, but I would be happy to have them rounded to hours/half hours, minutes are not important here...I can round up duration to 3 when duration is 2.57 for example.. Or 2.30 when duration is 2.20

3. How do you currently get your Gantt built ...??

Fact is I didn't buil a Gantt, as data was all over the place when I tried. It is not a Gantt..I built it manually, as in wrote items name, colored cell.. I am not able to built a Gantt.

again thank you

Quote from Carim

OK ...

Let me quickly redesign the whole workbook to try to reach .... the starting point ..

Carim thank you so much for the correct design of the file.

Some questions, if you care to explain

1. How do I add date/day? Because planning will be weekly.

2. What kind of macro would I need to obtain what I need?

3. What if I need to change prioritie of the items in planning?

It seems really out of my reach..

In any case thank you for your time and help!

Carim, I am sorry but I don't seem to get a grip on the file.

Is it already a Gantt?

I added start time, in the production order dates and duration sheet but:

1. it changes nothing on the Machines calendar. What am I missing?

2. I am planning based on dates, and what I would need the file to do is "put the items fin the first free spaces on the machines calendar

3. ID, ok I can assing it, but firsdt I need to understand the rest of the file..

thank you

HI Carim,

thank you very much for your time and revised file.

I reviewed the file and saw the calculations you did:

1. Column of the start date: H2 =starting today's date and first hour of the working machine hours. Then, the rest of the start dates/hours in H column will be calculated depending on "duration time of the previous item + lag time in L1 ( which is standard, 1 hour)"

2. Column of the duration required for the quantity of the item -which I will extract form the system and fill in

3.Column of the end date : is starting date/hour + duration

4. Conditional formatting with IF And formula! Brilliant ( and now I feel dummy)

thank you very much Carim , highly appreciated.

Two more questions...

1. How do I go about filling the cells not only with coloring but also with item name? Is there a way to repeat item name for the number of cells of the duration (dumm question?)

2. How do I separate coloring of conditional formating for the two machines? I am guessing I need two separate sheets for the machines. Because machines can work both at the same time, indipendently. So dependencies will only be machine and items on that specific machine -related. For example machine 1 and 2 can work at the same time same hours (with two different items).

However you have helped beyond expectations, as I really did not have a clue to how to start this, so THANK YOU

Carim, I figured out the different colors conditional formating myself.

I am now working on dependencies machine/item related only

in the meantime again a big thank you, if it wasn't for your "starting ponit file" I would still be lost

Thanks Carim ( I really feel dumb level in excel right now LOL).

Regarding dependencies, they are related as follows:

1. to the priority number given in column A, because for various reasons and management decision we may have to switch priorities.

2. machine 1a and 2a will work indipendently. On machine 1A, start date/hour of an item must depend on the finish hour of the previous item ON MACHINE 1A (based on priority number. And thanks to you I have that.

But in the meanwhile machine 2A will have its own programming, not depending on machine 1A. Dependencies in machine 2 A will be as explained above. Item with next priority number, will start production when the previous item has finished.

I will attach what I have been able to achieve this morning

The only solution I came up with is to have another sheet with raw data, which I will then order and split in DATA sheet in two distinct tables.

But this way it will be at higher erro risk: cut copy paste and order by priority, add rows, delete already completed items etc..will make me make a mess sooner or later.

Nevertheless, it is a treasure compared to what I had last week (pen and paper and hard thinking.. :D)

Please have a look at the file and let me know if there are some ways to automate this

I will reply to each point, below

1. Your Sheet Raw Data is totally empty ... why ... ???-

- because ,the RAW data sheet I will only use it to extract (copy paste)the excel table directly form the system. That table has some extra columns, besides the ones I have now in Data sheet. So I will only copy paste the columns I need for the Data sheet. Now it's empty because file is a mockup, sheet, and Data already has the columns and info of the actual production orders.

2. In the Data Sheet, Machines are separated ... OK ... But, in Column N ... Why don't you have your Priorities sorted ...?

Because I tried to change priorities to see if the Gantt sheet would change them. Basically I tried to see if what I did with the file was working. And then I left them that way when I attached the file. Sorry..

3. Dependency is in fact a direct consequence of precisely your sequence of items ...i.e. your priorities

- yes that is correct.

4. To get the Start of a given item just after the End of the previous one, you can set your Lag to Zero

- I suspected that, but for now I was working arround data and formulas as you made them

Ideally, could you replace the data I have used for test/demo purposes by some real-life data to ensure we are heading in the right direction ...

- No problem, just give me an hour. I am not in the office right now, so I would have to compile a table of raw data mannually

Here I am.

Now, in the RAw data sheet I have put the table as I will have it form the system. I only changed item codes. Orders dates are real, and I will have to start production based on planning beggining 27/04/2023

1. -Please note: I will mannually add the Machine column in the RAW DATA sheet( insert column A) and simply alternate machine assigment, as items can be produced on both machines. So in the end, if necessary, we will change them (form the data sheet) to the machine with more free slots or if a machine is out of order, we will assign items to the other machine..
2. - Now form the Raw data machine, can we get data in the two distinct table in the Data sheet. Or is there another way? Maybe to have data sheet with one unique table and then (see next point 3.)
3. -and then assign items to machines in the Gantt sheet? And dependencies must be item/priority of items on the assigned machine related.

I am not sure if:

- I need the intermediary RAw data sheet

If you need anything else please let me know Carim

In the meatime I am trying myself with formulas, but for this step I know VBA does the magic.

thanks a bunch

Quote from Carim

Great ... !!!

So if my understanding is correct ... your "Raw Data" worksheet is a download from your main frame ...

Quote from Carim

What is Unit used by Column J ?

Yes it is a download frorm my very customized main frame/view form the SAP

Quote

and ideally it should automatically feed and complete your worksheet "Data" ...

Ideally yes. Actually no, I do not think so because it would take a query (SQL) and authorizations to open the query from SAP..

Quote

By the way ... SAP is by far the worst possible piece of software on planet Earth ...

I do not like it either

Quote

Are you manually modifying the production dates in Column B ?

No, these are the dates of the production order as issued in the system. So download of the main frame comes with those. Logicaly the older a production order the bigger the priority... but in reality is not always the case

Quote

How to set the priorities by machine ? Can it be automated ?

I am not sure if I got your question right. However: priorities may change most of all because sales managment will prioritize production of one item over another, regardless of the production order date (when it came into the system). So that is the only column I need to be able to freely compile and modify in the DATA sheet, based on how Key accounts change priorities.

Quote

What is Unit used by Column J ?

Hours and minutes, but I didn't set the correct format 00:00 (first I had to calculate estimate time based on some old excel file...). I apologize...

For example, in J2 the value 0.25 would be 25 minutes so 00:25

Quote

I was extremely optimistic when I said "we had reached the starting point ..."

ahahahaha! Yes, I know. But note, what I have now, as it is is waaaay better than what I had last week: hard thinking ahhahaha

thank you

Quote from Carim

Hi again,

Attached is your Test File V2 ...

Carim big big thank you for the file. I need to look at it, see what you did , but first of all THANK YOU. And I can stress it enough how grateful I am for your help.

Quick question about Column J you say Hours-Minutes ... what about Rows 33 and 38 ... with 60.94 ???

- It is 60 hours, yes, although I suspect production manager has exagerated excution time for that particular item...

Please note if your sheet "Raw Data" is sorted by Column B ... your previous sequences in Columns F & G are modified ...

In this file, sheet "Data", you can modify Priorities in Column A and in Column N ... Re-Sorting is automatically performed ...

BUT ...obviously, for Both Machines, your Start Dates and End Dates must be re-visited due ... to stick to your dependency rules ...

Jumping there to see what magic you performed. Well noted. WIll study and get back to you with feedback.

Thank you so so much