Automated scheduling

Beau the dog

Board Regular
Joined
Mar 8, 2021
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I'm trying to create an automated vehicle maintenance planner and saw this online, but couldn't see how they achieved it. This isn't my final solution, but might get me closer. How did they automatically plot the 'X' based on the interval and start date for each vehicle (with no VBA)? I've entered the X's manually.

13/12/2021​
20/12/2021​
27/12/2021​
03/01/2022​
10/01/2022​
17/01/2022​
24/01/2022​
31/01/2022​
07/02/2022​
14/02/2022​
21/02/2022​
28/02/2022​
07/03/2022​
14/03/2022​
21/03/2022​
28/03/2022​
04/04/2022​
11/04/2022​
18/04/2022​
25/04/2022​
02/05/2022​
09/05/2022​
IntervalStart
Vehicle 16 weekly13/12/2021XXXX
Vehicle 28 weekly13/12/2021XXX
Vehicle 38 weekly20/12/2021XXX
Vehicle 412 weekly27/12/2021XX
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Upvote 0
Legend in my own mind. It's a pleasure to work on these things.
 
Upvote 0
Legend in my own mind. It's a pleasure to work on these things.

Ok, next question then (you know you want to...!!)...instead of 'X' in each cell, could it be a pattern of A, A, B, A, A, B, A, A, B starting with the first A on the start date?

In my head this seems very difficult?
 
Upvote 0
Cell Formulas
RangeFormula
E1:AE1E1=D1+7
D2:D5D2=IF(D$1>=$C2,IF(MOD((D$1-$C2)/7,$B2)<1,"A",""),"")
E2:AE5E2=IF(E$1>=$C2,IF(MOD((E$1-$C2)/7,$B2)<1,IF(MOD(SUMPRODUCT(--(LEN($D2:D2)>0)),3)=2,"B","A"),""),"")
 
Upvote 0
Solution
Amazing! I still have a lot to learn it seems, these kind of formulas are beyond me.

Thank you, gonna save me a lot of time!
 
Upvote 0
Really appreciate your help on the above, This is basically a baseline preventative maintenance planner, your help will save me a lot of time. I'll manually add in the MOT's (which will be written as C in the box). So there will be A's, B's and C's (possibly two more letters as well for tacho and LOLER)

Ok, another question. I have a decent understanding of Excel and the filter formula. But, what would be the filter formula so when i select a date from a drop down list (on a new tab), have it filter only rows with any of the letters mentioned above (basically rows with a letter in). I know various ways of creating a drop down list, so I'm ok there. So I would be able to select a week and show only vehicles with a service event in that week.

So if I selected 27/12/21, it would filter to show only vehicle 3 and 6. This is so this could be printed and given to relevant people

13/12/202120/12/202127/12/202103/01/202210/01/202217/01/202224/01/202231/01/202207/02/202214/02/2022
RegistrationIntervalGVWMake & ModelMOT5051521234567
Vehicle18weekly26000Dennis Eagle E-collect OL1928-Feb-22AA
Vehicle26weekly26000Dennis Eagle 6x2 Olympus OL1631-Oct-22A
Vehicle36weekly26000Dennis Eagle 6x2 Olympus OL1628-Feb-22AA
Vehicle46weekly26000Dennis Eagle 6x2 Olympus OL1628-Feb-22A
Vehicle56weekly26000Dennis Eagle 6x2 Olympus OL1631-Oct-22A
Vehicle66weekly26000Dennis Eagle 6x2 Olympus OL1630-Nov-22AA
Vehicle76weekly26000Dennis Eagle 6x2 Olympus OL1628-Feb-22A
Vehicle86weekly26000Dennis Eagle 6x2 Olympus OL1630-Nov-22AA
 
Upvote 0
These final formulas that get posted are a bit out of hand in complexity. I build them in small parts and then put them together. When you get an answer, take them apart to see what each bit is doing. Nobody could really follow one of these formulas, they get incomprehensible. Real computer people are probably outraged and the complete lack of readability of excel formulas. The lambda stuff will be better, but I don't have that for some reason.
Cell Formulas
RangeFormula
E3:AE3E3=D3+7
D4:D7D4=IF(D$3>=$C4,IF(MOD((D$3-$C4)/7,$B4)<1,"A",""),"")
E4:AE7E4=IF(E$3>=$C4,IF(MOD((E$3-$C4)/7,$B4)<1,IF(MOD(SUMPRODUCT(--(LEN($D4:D4)>0)),3)=2,"B","A"),""),"")
A11:AE12A11=FILTER(A4:AE7,INDEX(A4:AE7,,MATCH(D1,D3:AE3,0)+3)<>"","")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D1List=$D$3:$AE$3
 
Upvote 0
Well you do it very well, these have all worked perfectly. Thanks yet again.

Last one I promise, as I feel I should be paying for these golden nuggets you're providing!!

Could we alter the filter formula to just show the vehicle info columns (A, B & C) and the column of which date I selected?

Usually I would put the filter inside another filter and use the first filter as the array and use something like this to sort out the 'include' argument {1,1,1,0,0,0,1,1,1,0} But I don't know how it would work when using criteria for one of the columns?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top