Vorkosigan
New Member
- Joined
- Nov 1, 2024
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hello everyone,
long time reader, first time poster. I'm working on a scheduler for a business with a very complicated process, and got stuck on something relatively simple. Somehow, I can't get my head around it.
Production is divided into levels. The last stage, packing, is always Level 1. Then, whatever happens before packing is Level 2 and so on. The scheduler receives the plan via CSV - style list, example below.
Here's the challenge. In order to track all important information between levels, I assign an individual serial code to each row in the CSV. The serial code is [product code]-[date as a number]/[process name]
I want to write a formula that will identify the serial code of any processes that feed into this one.
Here's an example of a plan for one item:
The table runs from B1, column A is a helper column for something else.
Look at the last two rows. 5000 units in Cookie Mixing are being made on 22/10 and 23/10, which then feeds into Rheon on 23/10. I need a formula (or a VBA script, if you can find an efficient way to do it) that will list both of those next to the Rheon on the 23/10.
The biggest problem is that any combination is possible - you can have product on lower level made over several days and then used in one day on higher level, one day of lower level production feeding into multiple days of the next level make, or you can have 1:1 relationship, done on different days, or the same days.
The one thing I don't expect to happen (and I might be wrong on this, but I have to draw the line somewhere), is a situation in which only a part of a lower level production is used one day and the rest on the other. It doesn't make sense from the process perspective and makes this thing way too complicated, so I will ignore this scenario and train the users to avoid it or at least be aware that it may cause issues.
The last thing to mention is the fact that UOM changes at level 1. this is simple enough, I deal with it with a simple *IF($F2=1,$L2,1) wherever I'm working with the quantity. This is 100% guaranteed, only level 1 changes the UOM and needs to be translated.
I would be super grateful if someone could help me with this. I found several partial solutions, but nothing that addresses all scenarios consistently and now I feel like the frustration is making it harder for me to find the solution, and I need some fresh, experienced eyes on this problem.
long time reader, first time poster. I'm working on a scheduler for a business with a very complicated process, and got stuck on something relatively simple. Somehow, I can't get my head around it.
Production is divided into levels. The last stage, packing, is always Level 1. Then, whatever happens before packing is Level 2 and so on. The scheduler receives the plan via CSV - style list, example below.
Here's the challenge. In order to track all important information between levels, I assign an individual serial code to each row in the CSV. The serial code is [product code]-[date as a number]/[process name]
I want to write a formula that will identify the serial code of any processes that feed into this one.
Here's an example of a plan for one item:
| Run Rate | Code | Date | Qty | Level(number) | OEE | Process | Labour | Min Delay | Max Delay | UPC | This Code |
| 34.0 | 4119 | 22/10/2024 | 100 | 1 | 80% | Packing | 4 | 1 | 24 | 100 | 4119-45587/Packing |
| 34.0 | 4119 | 23/10/2024 | 100 | 1 | 80% | Packing | 4 | 1 | 24 | 100 | 4119-45588/Packing |
| 3400.0 | 4119 | 21/10/2024 | 5000 | 2 | 80% | Spiral | 8 | 24 | 100 | 4119-45586/Spiral | |
| 3400.0 | 4119 | 22/10/2024 | 5000 | 2 | 80% | Spiral | 8 | 24 | 100 | 4119-45587/Spiral | |
| 3400.0 | 4119 | 23/10/2024 | 10000 | 2 | 80% | Spiral | 8 | 24 | 100 | 4119-45588/Spiral | |
| 3400.0 | 4119 | 21/10/2024 | 10000 | 3 | 75% | Rheon | 5 | 1 | 6 | 100 | 4119-45586/Rheon |
| 3400.0 | 4119 | 23/10/2024 | 10000 | 3 | 75% | Rheon | 5 | 1 | 6 | 100 | 4119-45588/Rheon |
| 3400.0 | 4119 | 21/10/2024 | 10000 | 4 | 80% | Cookie Mixing | 4 | 1 | 24 | 100 | 4119-45586/Cookie Mixing |
| 3400.0 | 4119 | 22/10/2024 | 5000 | 4 | 80% | Cookie Mixing | 4 | 1 | 24 | 100 | 4119-45587/Cookie Mixing |
| 3400.0 | 4119 | 23/10/2024 | 5000 | 4 | 80% | Cookie Mixing | 4 | 1 | 24 | 100 | 4119-45588/Cookie Mixing |
The table runs from B1, column A is a helper column for something else.
Look at the last two rows. 5000 units in Cookie Mixing are being made on 22/10 and 23/10, which then feeds into Rheon on 23/10. I need a formula (or a VBA script, if you can find an efficient way to do it) that will list both of those next to the Rheon on the 23/10.
The biggest problem is that any combination is possible - you can have product on lower level made over several days and then used in one day on higher level, one day of lower level production feeding into multiple days of the next level make, or you can have 1:1 relationship, done on different days, or the same days.
The one thing I don't expect to happen (and I might be wrong on this, but I have to draw the line somewhere), is a situation in which only a part of a lower level production is used one day and the rest on the other. It doesn't make sense from the process perspective and makes this thing way too complicated, so I will ignore this scenario and train the users to avoid it or at least be aware that it may cause issues.
The last thing to mention is the fact that UOM changes at level 1. this is simple enough, I deal with it with a simple *IF($F2=1,$L2,1) wherever I'm working with the quantity. This is 100% guaranteed, only level 1 changes the UOM and needs to be translated.
I would be super grateful if someone could help me with this. I found several partial solutions, but nothing that addresses all scenarios consistently and now I feel like the frustration is making it harder for me to find the solution, and I need some fresh, experienced eyes on this problem.
