Automation
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Automation
Hi,
I am stuck with certain scenario. Any help will be appreciated.
I have list of tasks to be performed and allocated time for each tasks. Certain days there will be more workers available and certain days there will be less workers available. As allocated time for each tasks are stables, depending upon workers availablity is it any vba code available which will automatically allocated tasks list as shown in the excel please?
Please check the attachment.
I am stuck with certain scenario. Any help will be appreciated.
I have list of tasks to be performed and allocated time for each tasks. Certain days there will be more workers available and certain days there will be less workers available. As allocated time for each tasks are stables, depending upon workers availablity is it any vba code available which will automatically allocated tasks list as shown in the excel please?
Please check the attachment.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Thnaks for your reply. Can you please check attachment for more details.
The ultimate idea is to split working time almost equal to all workers. It can be up and down .
Thanks,
Jim
The ultimate idea is to split working time almost equal to all workers. It can be up and down .
Thanks,
Jim
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
See the attached version - now a macro-enabled workbook.
On Sheet2, you'll see a button that will run a number of simulations to find a good solution.
The larger the number of simulations (you can specify it in K1), the better the solution will be. But it'll take longer, of course.
On Sheet2, you'll see a button that will run a number of simulations to find a good solution.
The larger the number of simulations (you can specify it in K1), the better the solution will be. But it'll take longer, of course.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Hi Hans,
Thanks for your help. Really appreciate your time and effort.
It works fine but unfortunately the task list are in sequence. That is if worker 1 does Task 1 then i cant assign Task 8 to worker 1 by assigning task 2 to worker 2.
Example if there are 3 workers obviously we cant divide equally as some tasks take more time than others. It will be bit variation.I have attached sheet with more explanation in sheet 3 :)\
Thanks again Hans :)
Thanks for your help. Really appreciate your time and effort.
It works fine but unfortunately the task list are in sequence. That is if worker 1 does Task 1 then i cant assign Task 8 to worker 1 by assigning task 2 to worker 2.
Example if there are 3 workers obviously we cant divide equally as some tasks take more time than others. It will be bit variation.I have attached sheet with more explanation in sheet 3 :)\
Thanks again Hans :)
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
Here is a formula-based solution, no VBA.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Excellent. Thanks a lot. You are genius.
Thanks
Thanks
-
- UraniumLounger
- Posts: 9281
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Automation
^^^
I second that and add that he is a gentleman and a scholar, to boot.
I second that and add that he is a gentleman and a scholar, to boot.
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Sorry for opening the post again.
Thanks a lot for looking at my post.
I have added highlighted yellow columns.
What i am looking for is I am looking to delete all rows from keyword (just as an example) "Not" or "NOT" or "-Not" or "-NOT"
for the respective part number and go to next part number. Part number 122 as an example I have Task5 as not and i would like to delete next consective tasks in that partnumber.
Column K is the expected result which i am expecting. And automatically adjust the worker number with new allocated task please.
Is this something achievable please?
Thanks again
Thanks a lot for looking at my post.
I have added highlighted yellow columns.
What i am looking for is I am looking to delete all rows from keyword (just as an example) "Not" or "NOT" or "-Not" or "-NOT"
for the respective part number and go to next part number. Part number 122 as an example I have Task5 as not and i would like to delete next consective tasks in that partnumber.
Column K is the expected result which i am expecting. And automatically adjust the worker number with new allocated task please.
Is this something achievable please?
Thanks again
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
Run this macro:
Code: Select all
Sub DeleteTasks()
Dim r As Long
Dim pn As Long
Application.ScreenUpdating = False
r = 2
Do While Range("A" & r).Value <> ""
If InStr(1, Range("B" & r).Value, "not", vbTextCompare) Then
pn = Range("A" & r).Value
Do While Range("A" & r).Value = pn
Range("A" & r).Resize(1, 4).Delete Shift:=xlShiftUp
Loop
Else
r = r + 1
End If
Loop
If r >= 3 Then
Range("D2").Value = 1
End If
If r >= 4 Then
Range("D3:D" & r - 1).Formula = "=IF(SUMIFS(C$2:C2,D$2:D2,""<=""&D2)+C3>D2*$G$3,D2+1,D2)"
End If
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
That's brilliant. Thanks a lot for your help.
Just one question.
I have text and some characters in the partnhmber column. When I put just part number it works fine but anything with characters in the Column A the code is not working. Any idea pls?
Thanks again
Just one question.
I have text and some characters in the partnhmber column. When I put just part number it works fine but anything with characters in the Column A the code is not working. Any idea pls?
Thanks again
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
I used a numeric variable for the part number because they are all numbers in your sample workbook. Change the declaration
to
Code: Select all
Dim pn As Long
Code: Select all
Dim pn As String
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
That's great
Thanks a lot
Thanks a lot
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Hi Hans
Sorry for last question.
When ever I run this code I have some formula in column j like =if(E7=1,c7,"")
I have this formula from column j7 till j150
Issue is when I run the above code this formula is getting disturbed and I am getting REF error for deleted row.
Any idea Hans?
Is the above formula deleting whole rows?
Thanks
Sorry for last question.
When ever I run this code I have some formula in column j like =if(E7=1,c7,"")
I have this formula from column j7 till j150
Issue is when I run the above code this formula is getting disturbed and I am getting REF error for deleted row.
Any idea Hans?
Is the above formula deleting whole rows?
Thanks
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
In your sample workbook, the tasks start in row 2, and the range is 4 columns wide (columns A to D).
If your 'real' workbook has a different setup, you will have to change the code accordingly.
If your 'real' workbook has a different setup, you will have to change the code accordingly.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Thanks for your reply Hans
I have attached issue what I am getting.
When the macro button is clicked I am getting REF error in the formula cells.
Sorry about this
Thanks again
I have attached issue what I am getting.
When the macro button is clicked I am getting REF error in the formula cells.
Sorry about this
Thanks again
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
There is nothing to be deleted in that workbook, so it is not a relevant example.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Oh sorry
Pls find attached
Pls find attached
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Automation
Change the formulas in L3, M3 and N3 to
=IF(OFFSET($D$1,ROW()-2,0)=1,$C2,"")
=IF(OFFSET($D$1,ROW()-2,0)=2,$C2,"")
=IF(OFFSET($D$1,ROW()-2,0)=3,$C2,"")
Then fill down to row 16. this will prevent the error.
=IF(OFFSET($D$1,ROW()-2,0)=1,$C2,"")
=IF(OFFSET($D$1,ROW()-2,0)=2,$C2,"")
=IF(OFFSET($D$1,ROW()-2,0)=3,$C2,"")
Then fill down to row 16. this will prevent the error.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Automation
Wow
That's brilliant
Thanks a lot for your help Hans
That's brilliant
Thanks a lot for your help Hans