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

Allocation challenge

FVFO

New Member
Hi Everyone!!

I´m new to the world of excel and this site among others have been a great resource for me, have learned a lot this past months and even managed to land a gig in reporting. Chandoo.org has been my guide to working out the different issues i go over at work, but recently i got a new challenge and havent really been able to find a solution so hoping the forums might help, if im on the wrong board i apologize beforehand.

I work in medical billing and recently we changed the way our accounts receivable is worked, we assign accounts per day to the agent available but we have been doing it manually and it takes a lot of time, looking for a solution preferably with formulas but if you can come up with a VBA thats fine as im also learning the code giving baby steps.

Now my problem, on the attachment you will find a sheet called AR distribution and got this far into the solution to my problem:

1-Calendar: gives me only the dates of the month monday thru friday ( weekends off)
2-List of billers: people available to work on each team
3- Total accounts to be worked: i made a table to see how many account we could work based on the total of accounts each agent will have assigned (K2:T2)
4-Helper column (AP): I use the fill series tool to complete from 1 to the whatever total i get on AN35 and use a simple vlookup to get values on AQ column each number corresponds to a date in the month (will use the values to pull the date on another sheet)
5-My issue: on column AR i need a formula or macro to get the number of the agent who will work that line item on my report, i manually placed the numbers for day 5 so u can get an idea of what i need

So basically every month my number of available agents and accounts each person will work can vary, also agents will have different amount of accounts assigned per day based on how long they have been on the production floor, also there will be days when an agent is off (vacations) so when someone is off i will just delete the number for that date on table total accounts to be worked and recalculate AP and AQ columns, my problem is i havent figured out a formula to fill AR columns (Expected) on the example for day 5 (09/03/2018) only agent 1,2 and 4 will come in to work so i need the following sequence

1
2
4
and so on until agent 4 has 20, than it should be
1
2
...until agents 1 has 25 and the same for agent 2

it is imperative that each agent is assigned to a line interleaved instead of being

1
1
... until 25
2
2
2
...et all

Have not found a solution to my problem, not sure if there is a way to fix with formula even by using additional helper columns or if this can only be achieved via VBA.

Hopefully i managed to explain the issue properly, if not let me know and i can change my wording so its easier to read.

Thank you all,
 

Attachments

  • allocation tool.xlsb
    57.4 KB · Views: 8
Back
Top