workload distribution

[shaftora]

Hi All,

I have a task to distribute work load for back office team and it take full day to do it..

I am seeking your support to automate the sheet..

Sheet has 2 columns "A" for ticked ID "B" for number of transactions..

I want to distribute the work load based on below conditions:

- Each user can handle 800 - 850 transaction.
- No limit number for users

I want the final sheet to have each user and how many transactions he will handle from each ticket along with the ticket ID..

I have attached excel sheet for more explanations

Thaaanks in advance

[ChemistB]

Okay, so the first case ID has 6010 transactions. You max any user at 850 so the first 8 users can be assigned to that first case iD, correct?

[shaftora]

Yes Correct but we will have one user with transactions less than 800 - 850 so he need to Handel another transactions from different case ID.

Also some cases have transactions less than 800 so we need to distribute those cases to users taking into consideration he doesn't exceed 800 - 850 interactions.

Pleeeease

[ChemistB]

Okay, see if this works for you. The easiest way to do it (IMHO) is to keep things in single columns, User #, CASE ID, and Transactions. So In F5, I put "User01", then in F6 copied down for user ID

="User"& IF(SUMIF($F$5:$F5,F5,$H$5:$H5)>=800, TEXT(RIGHT(F5,2)+1,"00"),RIGHT(F5,2))

In G5 copied down for CASE ID

=IFERROR(INDEX($B$4:$B$45, MATCH(SUM($H$4:H4),$D$4:$D$46)),"")

In H5 copied down

=IFERROR(MIN(850-SUMIF($F4:$F$4,F5,$H4:$H$4), INDEX($C$4:$C$45, MATCH(SUM($H$4:H4),$D$4:$D$45))-SUMIF($G$4:$G4,G5, $H$4:$H4)),"")

This method will give each user exactly 850 transactions (except the last user). The formula's can be adjusted to 800 or any other number. See attachment.

[shaftora]

Thaaaaaank u thaaank u
you are a life saver..
this is perfect for me but if we can distribute the transactions so that users can have the same number it will be moreeeee than perfect..
Thaank u again

[ChemistB]

What do you mean so that users can have the same number? Give me an example

[shaftora]

I Mean if we make the transaction number to be 850 the last user will handle 335 only, what i need is all users to handle the same amount..

i figured out a way as below:

Sum number of transactions (32635) / 850 = 38.39411765

(32635) / Round (38.39411765) = 858.8157895

Round (858.8157895) = (859)

so if i change the transaction number to be 859 the last user Handel 852

What do you think ?

[ChemistB]

Sounds good. I used I2 for expected workload

=ROUND(SUM(C:C)/ROUND((SUM(C:C)/850),0),0)

Then in F6, change 800 to $I$2 copied down
In H5 change 850 to $I$2 copied down.

[shaftora]

Thaaaaankkk uuuu :D

[ChemistB]

No Problem. Can you mark this thread closed (Thread tools in post #1)