Automation

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Automation

Post by jimpatel1993 »

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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

What do the numbers under Worker 1, Worker 2 etc. mean?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

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.

Automation.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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 :)
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

Here is a formula-based solution, no VBA.

Automation2.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

Excellent. Thanks a lot. You are genius.

Thanks

User avatar
BobH
UraniumLounger
Posts: 9215
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Automation

Post by BobH »

^^^
I second that and add that he is a gentleman and a scholar, to boot. :clap:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

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

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

I used a numeric variable for the part number because they are all numbers in your sample workbook. Change the declaration

Code: Select all

    Dim pn As Long
to

Code: Select all

    Dim pn As String
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

That's great
Thanks a lot

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

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.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

There is nothing to be deleted in that workbook, so it is not a relevant example.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

Oh sorry
Pls find attached
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Automation

Post by HansV »

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.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Automation

Post by jimpatel1993 »

Wow
That's brilliant
Thanks a lot for your help Hans