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

Work Allocation

Sanoj

Member
Hi All,
Could someone please help me out with the work allocation file I am really in need of one.
I have attached my format in which its required.
1. Allocate each team member by First in First out method by considering Ageing (Sheet1 Col AY)
2. Allocation should be done according the value entered in sheet2 col C & E.
3. The main represents the main responsibility of each team member and support represents secondary responsibility.
4. Sheet2 Col B & D represents sheet1 column B (Region)
Example: If I put the value.
Ammy CZ - 50 and AT - 10
Andy AT-25 and CZ - 10
Then allocation criteria should be:
Then macro should allocate CZ to Ammy first as main for CZ is Ammy considering ageing (=Sheet1!AY:AY) and then to andy in CZ.

Please let me know if anyone still needs clarity.

Thank you in advance... :)

Regards,
Sanoj.
 

Attachments

  • Work allocation Macro.xlsx
    251.9 KB · Views: 58
Your requirement is not clear, Whatever you have explained above is not making much sense , if you can clean up your file a bit and upload with just few examples someone may be able to help you....
 
Hi Nebu,
Sorry for the confusion, let me try to explain it in simple way.
In the attached file the sheet named as “Summary sheet” we need to key in the Ageing (C & F) column and Allocation (D & G) column.
The VBA or Macro should read the name from column A of summary sheet and allocate by adding the Name in PIC column (C) in the sheet named as “Dump”.
In short we need to get the name from Summary sheet to Dump sheet in C column as per the criteria which we mentioned in summary sheet (Region, Ageing and allocation count).
Thank you… please let me know if you need any more clarity on this. You could change the layout of Summary sheet if required but Dump sheet is system generated so we will get the layout as is.
Regards,
Sanoj Viswam
 

Attachments

  • Work allocation Macro.xlsx
    251.7 KB · Views: 27
Here are some clarification I need:

  • There are two categories in the summary sheet , Main & Allocation , how are you going to figure out which is the main function and which is the allocated function in the dump sheet?

  • I have identified the corresponding region and ageing column in the dump sheet versus the summary sheet. However, I am not clear on which column in the dump sheet is corresponding to the allocation column from the summary sheet.
Could you also put together an excel work book only with those columns that are needed to build in a logic for the formula, I believe you will not be using the all the 72 columns for bringing in the name to the dump sheet.

Thanks
 
Hi Sanoj ,

To add to what Nebu has already posted , as far as I am concerned , nothing is clear.

Can you clarify the following ?

1. Do you want only column C on the Dump tab to be populated using the data entered on the Summary sheet tab ?

2. I see that column C on the Dump tab already has 666 cells populated ; from where have these come ? You say the Dump tab is system generated ; will it come with some cells in column C populated or will all the cells in column C be blank to start with ?

3. You say that the data entered on the Summary sheet tab is to be used to populate the cells in column C on the Dump tab ; I am not able to understand what kind of rules or logic should be used to do this ; at present you have filled in one row of data on the Summary sheet tab ; from this one row of data I am not able to conclude anything , since the figures relating to the name Ammy are CZ , (1,2) , 45 , AT , 30. I am unable to relate any of these numbers with the data on the Dump tab.

Against the region of AT , the PIC column has 57 entries of Ammy.

Against the region of CZ , the PIC column has 1 entry of Ammy.

If you can explain everything in one post , you will get an answer at the earliest ; otherwise we will keep having a dialogue for days together.

Narayan
 
Hello Narayan,

Thank you for your help
Definitely, will try to clarify the following please find comments below J

1. Do you want only column C on the Dump tab to be populated using the data entered on the Summary sheet tab ?
Reply: Yes, only column C on the Dump tab.

2. I see that column C on the Dump tab already has 666 cells populated ; from where have these come ? You say the Dump tab is system generated ; will it come with some cells in column C populated or will all the cells in column C be blank to start with ?
Reply: Yes, we import the Dump tab from system and we need to populate it manually as it’s our current allocation process. To start with, the column is blank the current data on C is manually populated (will upload new file for you as we get from system).

3. You say that the data entered on the Summary sheet tab is to be used to populate the cells in column C on the Dump tab ; I am not able to understand what kind of rules or logic should be used to do this ; at present you have filled in one row of data on the Summary sheet tab ; from this one row of data I am not able to conclude anything , since the figures relating to the name Ammy are CZ , (1,2) , 45 , AT , 30. I am unable to relate any of these numbers with the data on the Dump tab.
Reply: Okay, the current data on summary sheet is just an example. Logic what I am looking for is:
The summary tab ageing column (C & F) should consider Dump tab AY row and it should populate exact count of name in Dump tab AY row.
Example if we populate the summary tab as:
>Name Ammy,
>Main - Region CZ, Ageing 1, 2 Allocate 20
>Supporting - Region AT, Ageing 1, 2 Allocate 34
Then, after running the VBA or Macro coding the Dump tab should populate “PIC” column C with exact count of Name “Ammy” against ageing 1 and 2 (Dump tab AY col) that is 20 from Region CZ (Dump tab AY col) and 34 from region AT.
It would be good and more helpful if it kicks back some message or stops during keying in the allocation count if the summary tab allocation number exceeds total count of actual region available in Region tab.
For example let’s say in Dump tab if total count of CZ region is 26 but allocated is 28 in summary tab then should through some message or restrict to key in the excess number in allocation summary tab.
I have added count if function for this will it work?
Against the region of AT , the PIC column has 57 entries of Ammy – Please ignore manually populated attached new file.
Against the region of CZ , the PIC column has 1 entry of Ammy – Please ignore manually populated attached new file.
 

Attachments

  • Work allocation Macro.xlsx
    251.6 KB · Views: 19
Here are some clarification I need:

  • There are two categories in the summary sheet , Main & Allocation , how are you going to figure out which is the main function and which is the allocated function in the dump sheet?

  • I have identified the corresponding region and ageing column in the dump sheet versus the summary sheet. However, I am not clear on which column in the dump sheet is corresponding to the allocation column from the summary sheet.
Could you also put together an excel work book only with those columns that are needed to build in a logic for the formula, I believe you will not be using the all the 72 columns for bringing in the name to the dump sheet.

Thanks
Hello Nebu,

Thank you for your reply I have replyed to Narayan and I belive it will give you more clarity and will also answer your all questions. If not then please let me know and will try to.

Thank you once again...

Regards,
Sanoj :)
 

Attachments

  • Work allocation Macro.xlsx
    251.6 KB · Views: 13
Hi Sanoj:

Please find the attached file.

I have changed the layout of the summary sheet to suit the macro.

Before running the macro:
  • You have to delete the contents of the PIC column of the Dump Tab.
  • Sort the Region and then ageing column of the Dump Tab using advanced sort.
  • Sort the Region and then flag column pf the Summary Tab using advanced sort.
Please test the file and let me know...

Thanks
Nebu
 

Attachments

  • Work allocation Macro.xlsm
    263.5 KB · Views: 24
Hi Nebu,
Thanks for your time and consideration but the attached file is not allocating as required. when we put ageing 1 - 25 and ageing 2-20 its not allocating as said also could you make the "summary sheet" handy it seems little complicated and difficult to get the total allocation to a person at a glance.
Thanks,
Sanoj
 
Hi:

The summary sheet is designed to suit the logic for the macro (it is difficult to loop through the rows if it is in separate columns, the logic for the Summary sheet and the sheet I created is more or less the same), if you insist, you can keep the summary sheet in the format you need, but there should be a way to to pull the values from the summary sheet to the sheet I designed based on the entries you made. I have modified the Macro to pick the correct values now.

Note: The Ageing 1 column should be same for all the names, likewise Ageing 2 column should be same for all the names (for eg: if you have entered Ageing 1 column as 1 for the first name all other names should be 1 for that column, like wise if you have entered Ageing 2 column as 2 all other names should be 2, it does not matter if you want to keep any cells blank , in case if a name has only one ageing value just enter the that ageing in the respective column but you can keep the other column blank the macro will still work)

see the attached sheet I have entered only Ageing 1 column for Seol but left the Ageing 2 column as blank , also note all the ageing which are 1 is entered in column C and all the ageing which are 2 is entered in Column D. The sorting rules I have given in earlier post still applies. Let me know with questions if any...
 

Attachments

  • Work allocation Macro.xlsm
    277.7 KB · Views: 11
Hey Nebu,

Thanks but not working as required, its not allocating properly when we change the values in the summary sheet.

Regards,
Sanoj
 
Can you upload the workbook where you have allocated the values. I tested it at my end and it was working fine....
 
yes, I have highlighted the cells in yellow, please find attached.
 

Attachments

  • Work allocation Macro1.xlsm
    277.3 KB · Views: 11
The Macro will not work because the only ages available in Dump sheet are 1,2 & 3, but you are giving the age as 25, 50 and so on. Also your allocated field should not be a formula it should be an user input.

Here is the comparison between your summary sheet and my macro sheet.

Summary sheet Ageing column is same as Ageing 1 and Ageing 2 columns in my macro sheet, In your summary sheet you had put age in one column separated by comas, I just made it into two columns for the macro to run smoothly.

The Allocate column in the summary sheet is same as the allocate column in my macro sheet.

There is no total allocation column in my Macro sheet, the total allocation is checked in the background by the macro to ensure that the total allocated is not exceeding the total available.

In short, you have to manually enter the column C,D & E in the macro sheet and run the macro, all the sorting rules I had given in my previous posts still applies.

Thanks
 
Please find the file for your reference
 

Attachments

  • Work allocation Macro1.xlsm
    278.3 KB · Views: 66
Back
Top