+ Reply to Thread
Results 1 to 24 of 24

Macro for allocation

  1. #1
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Macro for allocation

    Hi team,

    I have the attached the file in sheet1 i have batch no and count of action and sheet 2 i have the user name, the action in sheet1 that in coloumn b should be summed up and should be allocated equally to the users meaning in sheet1 in column c the name should listed based on the numbers. Can somebody help me with the macro for the same .

    regards
    som
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Can somebody help me on this

  3. #3
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    team can some body look into this and help me out

  4. #4
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    HI team ,

    I hope there are lot of expertise here , some body can extend their hands to help me on this

    regards
    som

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro for allocation

    I think it's a safe bet that no one knows what you're trying to do or how the results should appear.
    Last edited by shg; 09-25-2018 at 11:07 AM.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Hi team ,

    i would explain in a better its batch allocation based on number of actions batch no is in sheet1 coloumn A and count of action is in sheet 1 coloumn B
    , In sheet 2 i have the agent names . Now we have to sum up the count of actions and based on the sum we have to divide it to no of users in sheet2
    take their names and assign in sheet1 coloumn c .

    Hope you understand.

    regards
    som

  7. #7
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Can some body let me know if my script is understandable.

  8. #8
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Can somebody can look into this

  9. #9
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    please let me know if my scripting is not clear

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    Is it something like this?

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  11. #11
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    That was great , only one small change you have divided based on the count of lines now if run teh macro it gives me 22 lines or 21 lines per person , but the name should be printed in sheet1 based on the sum of coloumn b divided equally among all the people.when some thing is more than average you can leave that coulmn blank. I have attached the file for reference.
    Attached Files Attached Files
    Last edited by somusas; 09-26-2018 at 10:35 PM.

  12. #12
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Hi XLA,

    Can you please look into this

    Regards
    som

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    Hi Som,

    I've been trying - have yet to figure lines 554,669,1080
    Last edited by xladept; 09-28-2018 at 04:59 PM.

  14. #14
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    hi xla,

    why are you trying the lines sum of column B , based on teh sum take an average for each person.

    i hope you get it

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    Hi Som,

    I need instructions on how to handle those three lines all with action counts more than the average.

  16. #16
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Hi Xla,

    You can remove those three lines. sorry for the inconvenience.

    regards
    som

  17. #17
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    sorry xla, please delete those three lines and you can proceed , sorry for inconvenience.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    Hi Som,

    I'm submitting your request to the Forum at large. My poor old brain has come up with nothing better than my original idea

  19. #19
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Macro for allocation

    I belive I understand what is happening,.
    Sum in Sheet1 column B, divide by the name count in sheet2 column A

    In the sample provided it is rounded to 132, so for every total of 132 in column B, we want 1 name.

    This is just a start, I am having problems right now to get the start row working properly.

    Hopefully others can add on to the code to get it working properly.


    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    Hi davesexcel,

    Thanks if somebody can fix it that would be great

    regards

  21. #21
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Macro for allocation

    This kind of works, we run into issues where the numbers in column B are greater than the average, it is the best I have for now.

    Please Login or Register  to view this content.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    Hi Som,

    This works

    Please Login or Register  to view this content.
    Last edited by xladept; 10-10-2018 at 09:46 PM.

  23. #23
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Macro for allocation

    was great XLA , this really works , no words to say my thanks , thats ultimate .

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro for allocation

    You're welcome and thanks for the rep!

    But - it's not perfect

+ 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] 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
  2. Sum-up weekly allocation to Monthly Allocation
    By juan.doe in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2017, 04:41 AM
  3. Macro for Average Allocation.
    By sairam2014 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2014, 10:05 AM
  4. Macro for Task Allocation to Employees
    By ravikiran in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-25-2013, 04:50 AM
  5. Key Notes related help required
    By pavittersingh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2012, 04:48 AM
  6. Allocation using VBA
    By naveenhs5 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-21-2011, 07:25 AM
  7. Replies: 2
    Last Post: 02-04-2011, 03:36 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