+ Reply to Thread
Results 1 to 173 of 173

Macro for equal distribution !

  1. #1
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Macro for equal distribution !

    Hi Team,

    I would like to create a new macro for Allocation within my team with the below criteria.

    1. Allocation of cases should be equally distributed in the team. (10 ppl)
    2. Assigning should be on FIFO basis. (First in first out) i.e based on case entry time.
    3. I will be adding new cases every 1 hour and that should be allocated as well. When I add new cases, allocation should happen for those cases.
    4. There should not be duplicates. Example. If a case is Allocation to member A at first then if the case came again it should not be allocated to any member in the team.
    5. Alao there are special cases which should be moved aside and those should be allocated at the end. We have case type.

    Can anyone please help me out...

    Thanks in advance !!!
    Last edited by Arunkumaramk; 12-03-2018 at 09:37 PM.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Work allocation macro

    Please send a non sensitive sample with the input data and the desired result

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Work allocation macro

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)
    Ben Van Johnson

  4. #4
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Team I m trying to attach a sample file but it didn't show attachment.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Work allocation macro

    Ah ! Now I see finally attached.

    If u look at the attached data, you would see case numbers in the data sheet. Those cases should be allocated to a team evenly. I have summarized a summary of how much cases allocated to each one.

    Please leave the card split as I have already created a macro for that.

    Every 1 hour I will paste new cases which should be allocated evenly among the team These new cases should be added to the old cases.

    Each time we hen I add new cases and click assign only the new cases should be allocated and it should not replace the data which has been already allocated.

  6. #6
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Can anyone please help me out !!!

  7. #7
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    Please Login or Register  to view this content.
    try this very simple macro. Mind you that the summary values need to be 0 at first instead of blanks...

  8. #8
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Perfectly worked out. Thanks a lot.

    Now the allocation of cases should happen on first in first out based on the case entry time.

    Can we create a option to select only employees who are available today and allocate only for them.

    Also it would be helpful if a separate Excel sheet is created in a path so the users can pick their file alone.

    Can u please add that as well.

    Appreciate your timely response !!

  9. #9
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    Another thought. Have you consider time allocation? Not all tasks take the same amount of time. Considering this factor 'evenly distributed' would be the amount of hours/minutes allocated.

  10. #10
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    No I did not mean time take for Allocation.

    I mean if the first case entry time is night 6 pm and the case entry time is morning 10. Then the case entered in night 6 should be allocated first to the users.

    And if a duplicate case comes in the 3rd set then that case should be allocated to the user who was assigned in first set.

    Would that be possible?

  11. #11
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    Here is the improved version

    Buttons have been added to the Summary tab. ASSIGN and Restart
    The restart button cleans everything for a full run.
    Tabs with the persons names have to be created beforehand
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    For the employee availability there is more work to do. Give me some time as I have to go now.

  13. #13
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Thank you !! This is really awesome.

    One more thing is duplicates which should be taken care. Same cases should not be allocated to different user..

    Please add that as well.

    Thank you. You are great. !!! Salute..

  14. #14
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Hi

    Can u get that fixed by today...

  15. #15
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    This version only assigns those who are there. To flag those not present add an "*" in the flag column.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Wat is the values that get returns in column M and N

  17. #17
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Also I see the card cases are getting copy to the card spreadsheet. I want the case I'd which have card should get moved from data to card and it should get deleted in the data spreadsheet.

  18. #18
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    OK let me work on the changes. I use columns M and N as a temporary list of the personnel available and the jobs assigned to them in order to facilitate the search for the next person to assign,

  19. #19
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Hi one more doubt if I don't want to generate separate sheet for users can u let me know which code I would need to delete

  20. #20
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Need a macro for allocation of work in team !!!

    I based the user tabs on your request that each user learned his or her tasks. If you want I can set it up so you pick the user from a list and the task pertaining that user will be listed.

  21. #21
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Can u do that? And send me the updated macro.

    If possible can we generate Excel sheets and save it in a specific folder. Like batch 1 batch 2

  22. #22
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Need a macro for allocation of work in team !!!

    Also just copy paste the codes that identify employee presence here. I would like to know which coding represents that

  23. #23
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi there need your suggestion now.

    Each time when I click split card type the entire dump which is already assigned gets split up.

    It is very difficult for me to again copy paste the splitter data to the consolidated data for Allocation.

    Mainly I need duplicate cases not t be assigned second or third time it comes.

    Can u help me? How can we consolidate all the data which is assigned ..

    Please give me some suggestions.

  24. #24
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    This is how I will do it. I would keep a master copy in data, have a tab for cards, and another for the rest, a userform-like for reporting each person's assignments and the summary where you can see it summarized. (hidden I would keep a tab for temporary data)

  25. #25
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Well to organize like this way. Then how do consolidate all assigned data including card in one sheet

  26. #26
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Duplicates is my big pain. How can I return the same person name who was assigned first for that day.

  27. #27
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Just to be clear. An assignment is given to a person, then this assignment is again entered into the data tab, you want to retain the original person or just flag it as already assigned and assigned to the original person..

  28. #28
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I want it to retain that same case to that same person. Also can we return value as batch 1 batch 2 when I put Allocation

  29. #29
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi there? Are u working on this modification?

  30. #30
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi there? Any update?

  31. #31
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Yes I am here, but I do this on my free time.

  32. #32
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank u. Below are the changes that are required.

    1. In the second batch only unassigned card cases to get moved to card sheet leaving the assigned data in the data tab

    Or can we create a separate assign button in the card sheet to assign only those cases to the users.

    2. User name should be returned in the assigned column if the case comes again back on the same day.

    3. Can we add batch 1, batch 2 for lot names so we will know which lot the user has to do.

    Please help me with these changes. Thank u.

  33. #33
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I think we have to pause and take a step back.

    Tell me in detail what is the flow of data.
    1. How is the data obtain?
    2. What frecuency?
    3. Whho types it in?
    4. What reports are needed from it?
    5. If not reports, what info?
    6. For how long do you want it accumulated? (forever?)

    These are just the basic questions that ocurred to me at this moment.

    I need to fully understand the flow of events and the flow of data and processed info in order to design the proper macro.
    You urgency and my limited time to do it demands that the macro serves you right this next time.
    I dont need to know real data, just the fields and the use and needed transformation of it.
    Think of the whole process please, I would be band aiding it with new ideas and you need a working and helpful macro PRONTO!!

  34. #34
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    [QUOTE=rcm;5021695]I think we have to pause and take a step back.

    Tell me in detail what is the flow of data.
    1. How is the data obtain? Every 1 hr we will take the dump in Excel format
    2. What frecuency? 1 hr
    3. Whho types it in? Raw data will be available in Excel format
    4. What reports are needed from it? Need to assign equally. Consolidation report. Card cases to be splitted at first And then have to assign to the team.
    5. If not reports, what info?
    6. For how long do you want it accumulated? (forever?) This is for whole day.

    We need consolidated one sheet that shows the status of assigning.

    If a duplicate case comes in the 3 Rd slot it should be assigned to the person who was initially assigned. Same case should not be allocated for 2 members.

    Hope this clarifies.

    Let me know if u have any questions.

  35. #35
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    This version will
    Split CARD and others into a CARD tab and SPECIFIC TAB when jobs are been assigned
    In Assignments one can check the persons assignments
    If the assignment routine find a duplicate it will assign it to the same original person and place the first job # as ref

    Check it out. I will be away the rest of the day..
    Attached Files Attached Files

  36. #36
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi Thank u for the updated version.

    There is no need to look for individual case assignment and moving data to a specific. My requirement is only to split card alone.

    There are some small modification to be done.

    1. The card assignments should be first splitted into a separate sheet. The data should get moved from the original data to a separate sheet. After a particular time the cases will be added to the original data and it gets allocated to the users.

    So we will have consolidation of all assignments in one sheet.

    2. During the second hour I will download the next set of data to the data tab again. The macro should again move the card cases to a sheet leaving the cases which got already allocated. ( Logic is look for text card and assigned to as "blanks")

    3. I added a same data again to your sheet and clicked assign it does not replicate th e original user. Can u check that?

    Let me know if you are clear.

  37. #37
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I hope u have understood now.

    There should be only 3 sheets

    1.Assigned summary
    2. Data
    3. Card sheet.

    Need to implement this logic for card sheet (2. During the second hour I will download the next set of data to the data tab again. The macro should again move the card cases to a sheet leaving the cases which got already allocated. ( Logic is look for text "card" and assigned to as "blanks")

    Rest the flag indicating present and duplicates batches exist should return the name which is already assigned.

    Also would like to add batch 1 batch 2 so we know how many allocated in each batches.

  38. #38
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    OK 3 tabs it is + Rawdata that I used to try it out (you can delete it)

    No need for the CARD SPLIT button as it is done before the jobs are assigned (Assign Button)

    To transfer CARDs into DATA just place "*" in column F
    Attached Files Attached Files

  39. #39
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Everything looks fine. Need a small correction.

    When I add the 2 set of data and click assign, the already assigned card assignments are getting moved to the card tab. This should not happen.

    Only Unassigned Card assignments should move to the card tab leaving the already assigned ones.

    Also when I click restart it shows error.

    Wat is the values that gets generated in the data tab. Can we hide that?

    Can u fix that?.

  40. #40
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    1. When I add the 2 set of data and click assign, the already assigned card assignments are getting moved to the card tab. This should not happen.

    I will fix it. I will look for empty cell in the assignment.


    2. Only Unassigned Card assignments should move to the card tab leaving the already assigned ones.

    see #1

    3. Also when I click restart it shows error.

    I will fix it.

    4. Wat is the values that gets generated in the data tab. Can we hide that? Can u fix that?.

    Yes they were case generator for testing they have no real value in reality.

    Expect the revised version later..

  41. #41
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    All the last reported bugs, fixed
    Attached Files Attached Files

  42. #42
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Really appreciate the quick fix. I will check tonight and get back to u.

    Please move the values to a last column so they are no visible. Also can u add comments in your code so I know and modify accordingly to my real data

  43. #43
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please Login or Register  to view this content.
    Attached Files Attached Files

  44. #44
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi Thank u This is exactly what is required. In the assigned summary there are values getting viewed as "0".

    I understand that this is mainly for speed up calculation. Can we move that to a last column so it will not be shown in the summary.

    Really appreciate the quick bug fix and assistance in getting this done.

    Thank u so much !!!

  45. #45
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    OK.

    Cleaned column E in the Summary
    Column F is hidden from view in data but it shows where the first entry is as reference for the repeats
    Should the repeats counts as jobs too? (they are not been counted as of now)
    Attached Files Attached Files

  46. #46
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Repeat should not count as it has been initially calculated. Need column f in data tab should also be removed. I just noticed it was hidden.

  47. #47
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    When I click restart values getting populated in column e,l,m,n. If that is necessary move those values to last columns in the same sheet so it is not visible in the assigned summary

  48. #48
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Modified the Restart Macro
    1. NO longer 0s the E column in Summary
    2. The available table was moved to the AM-AN columns (not visible when you are in the A-Z column quadrant
    Attached Files Attached Files

  49. #49
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank u sir. I will check with real time data.

    Hats off to u for your assistance and quickly fixing this..


    Thank you so much !!!!

  50. #50
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I have a proposal

    Since you want to learn VBA, if you want to change something, I will tell you which lines need to be modified

  51. #51
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Sure thank u !!!

    Will get in touch with u !!!

  52. #52
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can we create a live dashboard in Excel? I was just wondering if that is possible!!

  53. #53
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I will be moving the assigned to column in column "G" in the real time data. And filter will be in column "E".

    Let me know WT code has to be replaced

  54. #54
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. Can u tell me?

  55. #55
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Sure, but lets be clear, in TABS Data and CARD you want to move the assigned column right?
    By the filter you mean the process number right?

  56. #56
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I will do it tomorrow, there is a lot of changes. Could you send me the headers of the DATA and CARD tabs as well as the summary tab, I dont need real data just the headers...

  57. #57
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please Note the table in the Summary Tab. Place the appropiate letters of the columns there, and the total number of fields.

  58. #58
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Sorry I clicked to rapidly, without first uploading...
    Attached Files Attached Files

  59. #59
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Sorry for late reply.

    This the modification required.

    Data tab - Assigned to required in Column "G" and filter will be in Column "E".

    Card Tab - Currently I see only 5 columns getting moved, I need all 7 columns to be moved and the flag in the card tab should be in Column "H".

    Also I see duplicates are working but if u test the row 2 in the data tab the first assignment when I copy paste again, it didn't replicate duplicate. But the remaining rows are showing and getting assigned to the same person. Can u check that. I m uploading a sample with the column changes..
    Attached Files Attached Files

  60. #60
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi.any updates ?????

  61. #61
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    The last version considers it. It has a setup table in the summary tab where YOU set up the column letter and the maximum number of fields. I am resending it
    Attached Files Attached Files

  62. #62
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    So if I update the maximum number of fields does that mean the data gets moved from data to card tab?

    If I just modify that table will the macro get automatically adjusted.

    Also did u check for duplicates in the second row of the data tab.

    I mean if u copy the same data in the 2 row of the data again and click assign it gets assigned to another person.

    This is happening only for the data in the second row. Can u check that?

  63. #63
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Does that mean I need to update only the fields or should I update in code??

  64. #64
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. Can u confirm so I can check...

  65. #65
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I tried modifying the column name u provided in the summary tab. It worked. Let me check with real time data

    Thank u....

  66. #66
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. I have a doubt. When I put a flag for card assignments it gets assigned only to 3 members by default and it does not get assigned to anyone in the team.

    Can u help me wat i have to modify so it gets assigned to everyone.

  67. #67
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. Can u help me out.?

  68. #68
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi can u please check and let me know...

    I m waiting for your reply...

  69. #69
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I found the mistake. it is in assigncard and its reporting 1 less line in the number of people available. I will fix it soon.

  70. #70
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    This time you fix it.. Edit the assigncard macro, look for this section...
    I did not adjust the range from column N to column aN when I moved the table out of sight

    Please Login or Register  to view this content.

  71. #71
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Bro sorry for the late reply. This worked out perfectly.

    Thanks a ton !!!

    I would need a formula to calculate the SLA report.

    Can u provide a formula to group the time difference. I want to group which was processed before 1 meaning whatever assignment processed before 1 is met with SLA. (Assignment entry time and exit time difference.)

  72. #72
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Can you provide a sample. I dont follow.

  73. #73
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Please refer attached.

    Check for criteria was should be shown within SLA and not met in SLA.

  74. #74
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Forgot to attach .

    Please see attached.
    Attached Files Attached Files

  75. #75
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Let me know if u understood my requirement.

  76. #76
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I would need a summary based on the criteria wat I provided.

    Help me with the formulas so I can try on my own to complete.

  77. #77
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u help me out?

  78. #78
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi any updates??

  79. #79
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Dear Arunkumaramk: This should be another thread. Yet, I place the formula based on your criteria

  80. #80
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I forgot to attach the file
    Attached Files Attached Files

  81. #81
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi thank u. I should have posted this as new thread.

    Formula is fine just want to add one criteria post 2 pm assignment entry time and processed time should be as tat met.can u tell me the formula..

  82. #82
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    And also want to count how much we have met within SLA and not met within SLA.

    I mean total assignment met in SLA and not met in SLA

  83. #83
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u help me with formula so I can check with my data..

  84. #84
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please Login or Register  to view this content.
    Criteria
    If ENTRY TIME after 14:00:00 then TAT MET
    else
    if ENTRY TIME before 14:00:00 but PROCESSED TIME after 14:00:00 then TAT NOT MET
    else
    all others TAT MET

  85. #85
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi thank u. How can we count the overall count of tat met and not met.

    I mean a summary of tat met and tat not met.

  86. #86
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Look at the highlighted table.

    Basically the COUNTIF(S) function counts only those entry withing the range that meet certain criteria. In this case the criteria us wether TAT was met or not.
    Attached Files Attached Files

  87. #87
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    This is really perfect.

    I would need a suggestion on how to group time with bucket.wat function can I use.

    Say for example 10- 1 should be 1 bucket. Like wise time intervals.

  88. #88
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please send me a table for the specific criteria and mock results so I can work better and faster...

  89. #89
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Use the same table in SLA report. Use the entry time to group the time bucjet.

    Time bucket needed as below.

    10-1
    1-3
    3-6
    Post 6

  90. #90
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Bro want to learn many things from u.. Teach me so I can try on my own...

  91. #91
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Let me know once u fix it... Thank u for all ur quick response...

  92. #92
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    One more thing I missed out. The SLA report should be based on 2 filters.

    Filter is "normal" and "urgent".

    Under normal we have to show SLA met and not met likewise same for "urgent"

    Help me with these modification also..

  93. #93
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I tried out to group and show SLA how many of the met before 2 and not met before 2.


    Same wise after 2. Does the same countif function will work?? Or do I need to modify ??

  94. #94
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please Login or Register  to view this content.
    This is COUNTIFS at its true form. Colum B contains the Reception Time, Cell g2 is the start time of the "bucket" and Cell "h2" contains the end time of the same "bucket", Column E has the TAT MET or NOT MET phrase. See, all of the inscribed criteria has to be met in order to be considered a counted member . Of course I just described 1 formula. As it gets copied the row number will change form 2 to 5 or so for the bucket cells and the column number will also change from I to J. See for yourself.

    Thank you for giving me another word for time frame. Time Frame = bucket (of time)

    The table shaded in green takes care of any time, I added the before 10:00:00 bucket and the beyod 18:00:00 ends at midnight.
    Attached Files Attached Files
    Last edited by rcm; 12-16-2018 at 09:51 PM.

  95. #95
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank u. I told about "normal" and "urgent" assignment. Can u tell me how to fix that.

    These are type of assignments. Need to segregate tat according to these filters.

  96. #96
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u segregate urgent assignment met SLA and not met SLA. Likewise need to segregate for normal assignments

  97. #97
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Show the criteria please, write manually a version of the SLA table so I can place the right formula.

  98. #98
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Refer attached. I added a column as type. I need to know show the same SLA table separately based on the type.

    Meaning how many SLA met and not met for Urgent assignment and normal assignment.
    Attached Files Attached Files

  99. #99
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    This is what I understood
    Attached Files Attached Files

  100. #100
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u save it in ordinary Excel and attach. Macro file does not get open in mobile.

  101. #101
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Sorry, I am working on some other project with macros, wrong type..

    All it was needed is to include another check in the COUNTIFS. Column B now contains NORMAL/URGENT so I added that check to the original table including now only the NORMAL and created another table for the URGENT ones.
    Attached Files Attached Files
    Last edited by rcm; 12-16-2018 at 10:26 PM.

  102. #102
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    This is exactly what I require. Perfect and thank you.

    I will check with real time data..

  103. #103
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    OK, if you need more criteria just added at the end of the formula.

  104. #104
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Sure. THANK YOU !!!!

  105. #105
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi need a quick fix.

    If the entry time and processed time within 2 and diversion time also within 2 then it should be considered as Tat met.

    If the entry time and processed time is before 2 and diversion time is after 2 then it is considered as TAT met.

    If the entry time and diversion time is within 2 however processed time is after 2 it should be considered as tat not met.

    Can u paste the formula here so I can modify with the data.

  106. #106
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u please help me out. I believe the formula that u have written on previous Excel does not consider diversion time. And also want to know what is meant $5 in the previous formula..

  107. #107
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I tried your formula and it is showing a ta not met for all assignments.

    So can modify the formula based on the criteria I posted so I can check with real time data

  108. #108
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. Any updates?

  109. #109
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi can u tell me the formula ???

  110. #110
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi. Can you work out and paste the formula alone I will modify with the real time data.

    Please provide an update

  111. #111
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    This is the modification required. I am re posting it for your reference.

    f the entry time and processed time within 2 and diversion time also within 2 then it should be considered as Tat met.

    If the entry time and processed time is before 2 and diversion time is after 2 then it is considered as TAT met.

    If the entry time and diversion time is within 2 however processed time is after 2 it should be considered as tat not met.

  112. #112
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    If the entry time and processed time within 2 and diversion time also within 2 then it should be considered as Tat met.

    If the entry time and processed time is before 2 and diversion time is after 2 then it is considered as TAT met.

    Logistically, it can be said that If the entry time and processed time within 2 -> TAT MET (diversion time can be anything)

    If the entry time and diversion time is within 2 however processed time is after 2 it should be considered as tat not met.




    Row C = Entry time
    Row D = Processed time
    Row E = Diversion time
    Cell 05 = 14:00:00
    Cell j3 = "TAT MET"
    Cell k3 = "TAT NOT MET"

    Please Login or Register  to view this content.

  113. #113
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi thank you for your response.
    Diversion time plays a vital role, if the entry time and diversion time is before 2 and processed time is after 2 then it should be quoted as tat not met

  114. #114
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    If the assignment entry time is after 2 and processed time is after 2 then it should be quoted as tat met irrespective of diversion time.

  115. #115
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u modify and paste the formula?

  116. #116
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Is this correct, so I can program it?

    E = entry time
    P = processed time
    D= Divertion

    Decision tree to the question:

    Before 14:00:00?

    E P D
    yes yes yes Met
    yes yes NO Met
    yes NO yes NOT MET
    yes NO NO NOT MET
    NO yes yes NOT MET
    NO yes NO NOT MET
    NO NO yes Met
    NO NO NO Met

  117. #117
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Program for first 3 conditions. If the entry time is after 2 then it should be considered as tat met irrespective of processed time and diversion time.

    If assignment comes in before 2 however diversion time is after 2 then it should also be shown as met.

    You mean by "No" as not came in before 2 right??

  118. #118
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    yes yes yes Met
    yes yes NO Met
    yes NO yes NOT MET
    yes NO NO NOT MET
    NO yes yes Met
    NO yes NO Met
    NO NO yes Met
    NO NO NO Met

  119. #119
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Refer attached updated decision tree

  120. #120
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Please paste the formula once u fix it.

    Thanks in advance sir

  121. #121
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    A small change below should be considered as met.

    Also I missed to add one major point so sorry. Entry time before 2 should processed before 4

    yes NO NO MET

  122. #122
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Below is the time break up
    For normal
    Cut off time will be 2
    Processed time should be before 4

    For urgent
    Cut off time will be 2
    Processed time should be before 3

  123. #123
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Have u fixed it? Can u provide me the formula.

  124. #124
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I hope you have understood the criteria now. Please confirm.. if any details required please let me know.

  125. #125
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi any updates on the modification

  126. #126
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Bro any update ???

  127. #127
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I am waiting for your reply.

    Any updates???

  128. #128
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Could you repeat the list of ALL constraints. I cannot find the latest version

  129. #129
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    See below updated decision tree and constraints

    Entry time is before and diversion entry time is before however processed time is before 2 quote as Tat met.


    Below is the time break up
    For normal
    Cut off time will be 2
    Processed time should be before 4

    For urgent
    Cut off time will be 2
    Processed time should be before 3

    E. P. D. Status

    yes yes yes Met
    yes yes NO Met
    yes NO yes NOT MET
    yes NO NO Met
    NO yes yes Met
    NO yes NO Met
    NO NO yes Met
    NO NO NO Met

  130. #130
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Please confirm if you have understood the requirement.

    If your require any clarification let me know....

  131. #131
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I am sending a fairly large nested bunch of exclusive if statements embeded into the STATUS cells. I did not normalize the tree purposely to allow you to change the STATUS by circumstance. Each entry in the decision tree table is considered and its outcome is the outcome displayed in the decision tree

    The three times display are the cutoff times
    Attached Files Attached Files

  132. #132
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    So do i need to change the tree according to the decision so the status gets automatically​ changed.

    Does this tree have to updated in the master sheet so everytime I paste the original data, it gets automatically​ changed???

  133. #133
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I believe this is much complicated. Can u provide me a simple Excel file with the details provided excluding the decision tree.

    Can u do that for me???

  134. #134
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Can u provide me a simple file with the criteria required????

  135. #135
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    You have added more constraints all the time. The decision tree and its outcome was the best solution for the changing reqs. It is not a Macro, the desicion tree is within the nested ifs. So one has to paste the formula to every line it wants to analize.

  136. #136
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Okay then fine so I can alter the time in the decision tree so the SLA status gets automatically change??

  137. #137
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    The formula in the status column is same for all assignments right??

    Or do I need to change on everytime for each line???

  138. #138
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Just copy the formula to each line only once.

    If you change the status in the decision tree it will change in the data too.

  139. #139
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Okay I believe for tat not met has a different formula than others. If I just type that formula and drag it for all cells will it work????

    Or should I need to type the formula for tat met alone???

    The Decision tree will remain the same once fixed I will add the new data set every time.

  140. #140
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi can you confirm??

  141. #141
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Yes. The decision tree is like a table where the nested ifs go to fetch the result based on the combination of data like the 3 times and the urgency of the job. When adding a line, you have to copy the cell above the inserted line that contains the ifs to paste the next ifs

  142. #142
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Well thank you. But I see the tat not met has one formula and tat met has one formula in the status. Does both are same??

    Please confirm.

  143. #143
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Please Login or Register  to view this content.
    Each IF() refers on the TRUE clause to the table so breaking down the first one
    IF(AND(J5="normal",K5<=$C$1,L5<=$C$3,M5<=$C$1),E$9,IF

    If it is a NORMAL entry and the entry time is before 2 and processed time is before 3 and diversion time is before or equal to 2 THEN it will return $e$9 whose value is "TAT MET". If all of the requirements were not met logically, then if goes to the next IF statement. Please note that the seeked and return value is E$10 and so on... The table covers all combination possible and assigns a STATUS to each one.

  144. #144
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Bro one more help need on the SLA report.

    How can I create a summary on how many met in "normal" and how many not met in "normal" using count if.???


    Can u help me???

  145. #145
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    This is the summary I require.

    I can use the countif statement to count how many there are in met and not met however it should be calculated based on type.

    Type Met​. Not met
    Urgent.
    Normal.

  146. #146
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi can u provide the formula??

  147. #147
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Hi, could you provide me the latest excel file that resembles your REAL one?

    do you know about COUNTIFS ?

    you could count based on more than one criteria. Lets assume that Met, or Not Met is in column E and Type like urgent normal is in column F then
    Please Login or Register  to view this content.
    then its a matter of filling all combinations of the two parameters there should be 4.

  148. #148
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    look at the example. It shows a table with all possible combinations..
    Attached Files Attached Files

  149. #149
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank you so much. Perfectly worked out !!

  150. #150
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Any time!!!

  151. #151
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Bro I have one last thing.

    Attached is the latest decision tree I m using, I just want to modify and summarize the result.

    Tat met before 2
    Tat not met before 2.

    This is for both urgent and normal cases.

    Before 2 is the entry time of the case.

    Can u paste the formula so I can try on my side.
    Attached Files Attached Files

  152. #152
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Will countifs works if we try to calculate based on the entry time??

  153. #153
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    You are right
    Please Login or Register  to view this content.
    can do the job.
    Attached Files Attached Files

  154. #154
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank u. Also I need the summary for also after 2.

    The criteria will be always met. So it should show met count of urgent and normal.

    Can u include that as well??

  155. #155
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    I have included the after 14:00:00 table
    Attached Files Attached Files

  156. #156
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Thank you. Appreciate the quick turnaround !!!

  157. #157
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Hi Rcm, hope u are doing good.

    I need a small modification on the allocation macro u have created.

    I have included 2 columns.

    1.Task code - In the data file we will have task code for each jobs and I have created a task code sheet in the same workbook and I want those codes to be excluded and moved only the correct data to a separate sheet.

    2. Product code - like the task code, we will have separate product codes for each job and I want only certain product codes to be picked up from dump. In this please consider "New".

    Please remove the card split up in the macro.After I click assign button, the above 2 criteria should be checked and it should get assign to members equally.

    Since this is a modification, I didn't open a new thread. Can u please help me out with these modification.
    Attached Files Attached Files

  158. #158
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    This is what I gathered from your request. Before assigning the tasks, it checks the Excluded lists for both the Task and the Product..
    Attached Files Attached Files

  159. #159
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi Rcm, Thank u for the quick response.

    But wat I need is after excluding the task code and product code, the remaining data to be moved to a separate sheet and it should get assign to the members.

    Can u modify it..

  160. #160
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Of course, so they get assigned but at the same time are moved to a different "DATA" worksheet... correct?

  161. #161
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Macro for equal distribution !

    Try it now,
    Attached Files Attached Files

  162. #162
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi Thank you. Please help me with one modification.

    Remmovr the card split code in the macro. And also I see macro describe duplicate cases while assigning.however what I would need this the same case if comes again should be allocated to the same person.

    Also please add one more criteria, under sale, there is one sub product "Bill'. During assigning, it should check for branch code which should be excluded and then task code and under task code for sale 'bill' should be excluded.

    So the reamining data should be populated in a separate spreadsheet and it should get assigned to employee.

    Can u do that for me. Also please add the comments in the macro code so I can modify with real time data.

  163. #163
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Under product code I need only the provided codes and under sale I need bills to be excluded. The macro should check from sub division for sale and exclude that criteria.

    Rest product codes should be considered fro assigning.

  164. #164
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Hi Rcm. Any update???

  165. #165
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Rcm any update please...

  166. #166
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Rcm I'm waiting for the updated file. Can u please provide me...

    Thank u

  167. #167
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Rcm can u please help me....

  168. #168
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Rcm any update? Please confirm if u are working on the modification. I understand it takes time to modify but please confirm so I can plan accordingly...

    Thanks in advance

  169. #169
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for equal distribution !

    I understand it takes time to modify but please confirm so I can plan accordingly...
    Seems to me you are taking advantage...We are here to assist and point in the right direction...is a free service we provide - out of the goodness of our heart - we are all here to learn...
    Perhaps next time you should consider appointing a member in the commercial section, where you then can make demands
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  170. #170
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233
    Hi I believe u have misunderstood. I did not demand anything here. I am asking assistance so I can develop myself.

    This is a sample code I am asking, since I do not have any idea to get it.

    I m sorry if my words are demanding.

  171. #171
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    I am not sure how to get the second filter in the code. If u can guide me that will be helpful so i can write myself.

    I am really thankful to RCM as he has helped me a lot and taught me a lot..

  172. #172
    Forum Contributor
    Join Date
    11-29-2018
    Location
    Chennai
    MS-Off Ver
    MS office 2010
    Posts
    233

    Re: Macro for equal distribution !

    Rcm can u teach me to get the second filter done, provide me an idea so I can write on my ow...

  173. #173
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Macro for equal distribution !

    What you have received here is help to get you going. If you require more than that (and it is clear from reading the thread that you do), then you really should now open a thread in the Commercial Services section where you can offer a small payment to anyone prepared to give you the comprehensive solution and training that you are asking for,
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Equal work allocation via vba
    By akulka58 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2021, 12:31 AM
  2. [SOLVED] Work Allocation
    By ashok0141 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-15-2020, 06:59 AM
  3. work allocation
    By ashok0141 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-09-2018, 01:25 AM
  4. [SOLVED] Macro for auto work allocation depending on old pending work?
    By nr6281 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2017, 04:07 PM
  5. [SOLVED] Work allocation to employees so that they are involve according to the requirement
    By shikha2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2014, 08:54 AM
  6. Work allocation to employees so that they are involve according to the requirement
    By shikha2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2014, 08:40 AM
  7. Key Notes related help required
    By pavittersingh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2012, 04:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1