• 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.

Workload Allocation w/ skills matrix

shucky

New Member
Hi,

I'm currently creating a macro for allocation base on the skills matrix set. (determined by Y or N). Should be allocated randomly but evenly distributed. Can someone help me how to do this on VBA macro. Thank you.
 

Attachments

  • Work Allocation.xlsx
    14.3 KB · Views: 13
Hi ,

I am not clear on what the macro should do.

Do you want the range C8:J11 to be filled with Ys and Ns ?

If so , should it be randomly assigned ? What is the meaning of random in this case ? The assignment which has been made in the uploaded workbook has 20 Ys and 8 Ns ; is this random ?

What is the connection between this matrix and the data on the Data tab ?

Narayan
 
Hi,

Cases in the data tab (assigned column) will be filled out by the names based on the matrix table. For example Name A was tagged as Y for processing queue of GLO client, his name is possibly assigned for this case. For checking queue of GLO client, Name A should not be included for this case since he was tagged as N.

To summarize, Names should be allocated on the data tab based on the status, queue and client.

Regards,
Shaks
 
Hi ,

Sorry , but the answers or the explanation provided does not say anything about the questions I asked.

1. What should the macro do ?

A clear and comprehensive explanation of this requirement is still not available.

Should the macro fill the matrix with Ys and Ns ? YES / NO

Should the macro fill up the cells in column F in the Data tab ? YES / NO

Any YES answer to the above questions needs to be explained further as to how the macro will do the job ; what will be the logic used.

All explanations should be with reference to the uploaded workbook and the data in the uploaded workbook , preferably using actual worksheet cells and ranges / columns.

Narayan
 
Hello,

The purpose of macro is distribution of task based on their skill set. The purpose of Y and N is to determine if the worker is now eligible to work a certain case per queue and per client.

The macro will fill up the names on the data tab.

Example:
If the status is Y and queue is 'processing' and client is GLO, then assign the name. Else, determine the next worker if eligible.

Something like that. Apologies if I can't clearly visualize it to you. But the main purpose of macro is distribution of task based on skills matrix. If the matrix table need to be revised to make it possible, let me know.
 
Hi ,

I am sorry if I have not been able to understand.

I have manually entered the assignments in the Data tab ; can you go through the assignments and either confirm or correct where ever necessary ?

Narayan
 

Attachments

  • Example (1).xlsx
    13.9 KB · Views: 20
Multiple cross poster
 
You, shucky,
should focus
and reread Forum Rules: Eg if shucky uses Cross-Posting, then shucky should do many things.
... and of course, You've noticed, what replies You have already gotten from other Forums.
 
Sorry but most of the time, I did not get replies on some forums. But I'll take note of this. Thank you.
 
Hi,

I'll just focus on this thread. Thanks for notification.

Shucky
In that case, I suppose you advised the other forums that their members working for you for free on their spare time should not waste their time anymore for your question?
 
Hi ,

See the attached file. Run the macro named Sheet2.AssignNames.

Narayan

Hi All,

Can anyone please go through the attached macro in the above thread(if needed , I am attaching again) and try to explain this code. I am unable to get what is this code doing.
I could understand only partially. Line#1,2,3&5 are bit confusing. Not getting the purpose of the index used in each line.
Please explain.

Line1: Set firstcel = IIf(Array_of_Ranges(Match1 + Match2 - 1) Is Nothing, [AssignmentMatrix].Columns(Match1 + Match2).Cells(1, 1), Array_of_Ranges(Match1 + Match2 - 1))
Line 2: Set nextcel = [AssignmentMatrix].Columns(Match1 + Match2).Find("Y", firstcel, , xlWhole, xlByRows, xlNext)

Line 3: assignment = Application.WorksheetFunction.Index([NamesToAssign], nextcel.Row - [NamesToAssign].Cells(1, 1).Row + 1)

Line 4: cel.Value = assignment
Line 5: Set Array_of_Ranges(Match1 + Match2 - 1) = nextcel
Line 6: Set firstcel = nextcel


Thanks,
Sudha
 

Attachments

  • ForumSampleWork.xlsm
    23.3 KB · Views: 9
Back
Top