• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA or Excel formula to randomly assing task to resources availibility as per the skills required.

V

vivekdabodiya

New Member
Hello Friends,

I am new here and I'm looking urgently for a solution to one my requirement . I would be happy with any solution be it VBA or excel formulas only.

I've attached an excel file and it contains two worksheets, one have tasks with date of task, required skill area, required skill category and location of task. Other worksheet have resources data with names, skill area, skill category and their availability date ( from, to )( both inclusive).

I'm looking for a solution where I can automatically random assign task (in Column E of "Tasks" worksheet) to the available resources ( available in "Resources" worksheet) which fits in line with the task on that particular date with a condition that whenever location is changing from one to other then the later must not be alloted to the same resource. For eg, if Task"1" is assigned to resource "A" at location "ABC" then Task "2" at location "XYZ" must be alloted to resource other than "A".

Thanks,
Vivek


looking forward for a fast reply.
 

Attachments

  • Chandoo Task allocation.xlsx
    98.5 KB · Views: 13
Also, random assigment should be in such a way that tasks gets assigned in a proportion way (i.e (close to) equal no of tasks to the available resources (which fits as per skills required)
 
Hey Vivek

I have gone through the excel sheet uploaded by you,
I have a query, I didn't understand properly how you want to allocate your resource.
Like for skill area fire, skill Category A, only four resource is available viz. SJ, SA, VJ and TRA
Whereas you have 179 tasks with skill area fire and skill category as A for different dates.
Kindly please explain the way you want to allocate the task
I would like to help you in case i can.

Thanks and regards
Riya Mehta
 
Hey Riya,

Thanks for replying.

I want to allocate randomly from these four resources only , provided the date of task should fall between the dates of availability of resource and would be great if allocation is proportionate between the four i.e equal no of tasks distributed ( so that one resource is not overloaded).

Also, if:-

1. Suppose, task 1 at date January 12, is allocated to SJ for location Delhi, then task 2 at date January 13 for location Chennai must not be allocated to SJ as the source cannot travel in short span.

2. If, different tasks have same date with different locations then tasks cannot be allocated to only one resource as same resource cannot be present at all the locations ( he can handle only one location at a time)

I know this is really difficult ( at least for me ) but it’s possible.

I’m doing it manually and it’s painful.

I hope if you can help till any extent will be great.

Thanks,
Vivek
Excuse typos & brevity
 
Hey

I tried to figure it out.
But was not able to figure it out properly.
I tried to allocate the resources to the tasks, and i did too, but one of your conditions the date of task should fall between the dates of availability of resource is not fulfilled in few case which i marked as FALSE.
I have uploaded a sheet.
In case this can help you.

Thanks
Riya Mehta
 

Attachments

  • Chandoo Task allocation.xlsx
    394.8 KB · Views: 10
Thanks Riya this will definitely help for some extent.

Can you please share the vba codes for this.

I have dozens of file to operate this thing on.


thanks a lot.
 
I havn't used any vba code to perform this.
Few formulae has been used which you can check on the sheet uploaded by unhiding the columns.

Regards
Riya
 
Riya,

I checked the file you uploaded, the FALSE cases are many ( between you changed the tasks date that’s why it’s showing less results for False) . This will not help.

Anyways thanks a lot.
 
vivekdabodiya
Press [ Do It ]-button ...
It's a SAMPLE !
If it works ask details or modifications.
ps. If You use eg term 'Misc' then use same term in both sheets!
 

Attachments

  • Chandoo Task allocation.xlsb
    60 KB · Views: 6
@vletm

this works but it allocates same resource at different locations on a particular date.

the resource can be available at only one location on a particular date.

Thanks
 
vivekdabodiya
hmm?
You wrote that 'this works ...' YES!
... but but ... You continued with BUT-word ...
... how .. 'this works' ... if this don't work?

Test this sample.
If still no match,
then give clear row numbers,
where is something,
which should work otherways?
 

Attachments

  • Chandoo Task allocation.xlsb
    61 KB · Views: 3
Does not work!

See screen shot attached (see task allocated to "AK", he is present at three different locations on 26-03-2019")

ThanksCapture.JPG
 
Only those three rows?
Check this ... and get more resources!
 

Attachments

  • Chandoo Task allocation.xlsb
    68.3 KB · Views: 7
@vletm Thanks man!

This works like a charm, except it places some resources in out of their available dates.

Can you please fix it?

It will be helpful if u can share the vba codes or provide the password, I have to implement it on dozen of files.


Thanks,
Vivek
 
... exact list

This for testing ...
If works better then, You would make copies from this for those other dozen of files.
 

Attachments

  • Chandoo Task allocation.xlsb
    72.6 KB · Views: 32
@vletm , can you please do one modification .

that there should be atleast one day gap in between when resource moves to another location.
for eg:

1.if resource is at location Sydney on 13-06-19 then he cannot be in England on 14-06-19.
2. if resource is at location Sydney on 13-06-19 then he can be in England on 15-06-19.

(atleast one day gap is required if location changes)


Thanks
 
vivekdabodiya
Do You know airplanes? Those flies daily.
Are those two locations only which should handle otherways?
... most of those were old dates and ... name has solved randomly!
 
@vletm I just used these two location to set an example.

I agree aeroplanes fly daily. But taking in account the travel time, time to do task and time for sleep and rest. I want to keep a day gap in-between.

agree dates are old ( will manage it).

a day gap is requirement from the resources if they are travelling to other location.

And i loved that ’empty’ you put in cells if no resource is available.

Would be obliged if you can add this requirement too.

Thanks A lot !!
 
vivekdabodiya
Your: ... set an example
... then there could be more combinations which would need more gap ... means, You would ask tomorrow to again?
... or ... and ...
... a day gap is requirement from the resources if they are travelling to other location.
This could mean that there should make check with ANY locations
... if different locations without a day-gap then 'random' next resource?
... ... hmm?
 
You must log in or register to reply here.
Back
Top