1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Work assignment Macro

Discussion in 'VBA Macros' started by Niranjanrajrishi, Dec 6, 2017.

  1. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Hello,

    I need a Macro to reduce my work to allocate work items to my team. I have a team of 12 members and need to assign each category of work based on the volume. Let's say I have to assign work items A, B, C, D to 12 members. I have table wherein I will enter the count of work from each bucket and I want to assign the volume based on my table (manually prepared). Can anyone support me to build a macro with such scenario?
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    Can you please post a sample file with some sample data?
  3. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Please find the attached sample file. Please check the sheets and the responsibility should considered as "Responsibility Basket & Doc Type" and then the count of work items should be considered from the Volume input sheet.

    Attached Files:

  4. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Did you manage to work out for me?
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Nothing is clear , to me at least.

    1. Which cells should have formulae in them.

    2. What should be the outputs in those cells ? Manually enter these values.

    The more cells' outputs you provide us with , the easier it will be to understand your requirement.

    Narayan
  6. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Sorry if it was not clear.

    My intention is to assign the work ownership in the column W. The names should consider from the sheet 'Volume_Input'. If you look at the sheet 'Volume_Input', you will see the Usernames in the column 'B'. These Users should get assigned the work. But the sheet 'User_Responsible' should be considered when assign the work. The work type will be available in column 'H' in the Data. So based on the target (manually) the ownership should be assigned in the volume_input should be assigned.

    Please let me know if it works.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Sorry , but things are still not clear , since no manually worked out examples have been provided.

    The user names in column B in the tab Volume_Input are identical to the user names in column B in the tab User_Responsible.

    Unless you manually enter the user allocation for the responsibility basket AT01 , I doubt that we will progress.

    Narayan
  8. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Thank you so much for the direction. Please find the attached file wherein I have filled out the User responsible column manually.

    Te User_Responsible sheet is to identify the responsible basket based on which we need to allocate the work. Not all Users will work with all basket. So based on the volume inserted in the 1st sheet we need to allocate applicable basket to each one. If still not clear, I will send the exhaustive list which we worked manually. Since the file is huge, I considered only few lines here.

    Attached Files:

  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Sorry , but it is still not clear.

    What use is being made of the values in the Approvals , Overdue and SLA columns in the Volume_Input tab ?

    You have indicated User_1 in some of the cells in column W in the Data tab ; why have the cells W38 through W41 , W64 , W65 , W68 through W72 , W95 , W96 ,and many more cells in the range W98:W166 in the Data tab not been populated with the user name User_1 ?

    Narayan
  10. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Approvals , Overdue and SLA - these are the status of the work basket. The utmost importance will be given to Overdue, then followed by SLA and Approval and at last scan today. I have not populated all values. I just given sample values.

    If you need to entire file worked upon, then I will send it tomorrow after we work the file manually.

    I appreciate you efforts.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    That will be nice.

    However , doing it for just User_1 alone will be more than enough.

    Narayan
  12. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Please see the attached file with manual allocation. I decided the no of works items after the total queue count. I want to get it allocated based on the work items I given in a table and the same assign to them.

    Attached Files:

  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    The confusion still remains.

    If we take the responsibility code AT01 , we find that some of the entries have the name Vipin , which I assume is Vipin Nirapath ; other entries have the name kshi ; I have no idea who this is.

    Why have some entries been allocated to kshi , and why are there a lot of entries which have not been allocated to anyone ?

    Unless the logic is clarified , I am afraid we will be wasting time in this back and forth.

    Narayan
  14. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Sorry! Something messed up. Please find the attached file with complete names.

    Attached Files:

  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Can we focus only on the responsibility code of AT01 ?

    There are in all 92 such records.

    Of these , you have allocated 17 to User_14 , 7 to User_12 , while 68 have been given the value Not Allocated.

    How and why ?

    Narayan
  16. Niranjanrajrishi

    Niranjanrajrishi New Member

    Messages:
    20
    Yes.

    We given only17 to User_14 , 7 to User_12 , while 68 have been given the value Not Allocated since we cannot complete everyone on the same day. So what ever possible work will be given. We need to consider the responsibly for each User. As I mentioned, a table should be created and we manually insert the no of work items will be given to each User. The work items will be based on the work load. Let's say AT01 has 100 work items and we have I let one User available. So we will give , let's say 40 and other work items to make it 100 daily target. Once we do use the work items count, then the macrk should assign the owner ship.

Share This Page