Allocation of 4 different items EQUALLY based on odd number of requirements

[carlaloup]

I wonder if anyone can help me, I don't know if there's a way to do this within excel or whether I will have to manually do this.

I have 4 equal numbers of items, 6,500 item 1, 6,500 item 2, 6,500 item 3 and 6,500 item 4.

I have to allocate all 26,000 items based on how much each person needs, but that number isn't neccessarily a multiple of 4.

I have 1500 people to distribute the items to, all with a specific number of items needed. I.e John Doe needs a total of 7, Jane Doe needs a total of 26.

It does not matter if they receive more of one item than the other.

Is there a way in excel to evenly distribute all items?

THANK YOU

[Jacc]

I hope I understood the task correctly.
4 different sheets with different solutions of increasing complexity. The last one contains some array formula.
Hit F9 to generate new random number of items for each person for testing.
There is a formula summing up how much of each item was used and those numbers deviate less for the more complex solutions.


This was a pretty interesting task and now I'm waiting for some of the more experienced guys to come up with improvements. To be honest my complex solutions are just too massive.

[FDibbins]

Hi and wlecome to the forum

From the sounds of it, I would think you need to calc some sort of proportion, based on how much each person needs to the total "needs", then apply that to each of the "items"?
You did not specify if John needs 7 or each or 7 in total, so assumed 7 of each
John Doe 7 = 0.028%
Jane Doe 26 = 0.104%
Total "needs" = 25000

So John would get .028% of each of the 6500 items = 1.82 of each item.

If this is what you want, then give it a shot and let us know how you make out. If it isnt, I suggest you upload a sample workbook with dummy data and a few examples of your expected outcome

[carlaloup]

Hi and wlecome to the forum

From the sounds of it, I would think you need to calc some sort of proportion, based on how much each person needs to the total "needs", then apply that to each of the "items"?
You did not specify if John needs 7 or each or 7 in total, so assumed 7 of each
John Doe 7 = 0.028%
Jane Doe 26 = 0.104%
Total "needs" = 25000

So John would get .028% of each of the 6500 items = 1.82 of each item.

If this is what you want, then give it a shot and let us know how you make out. If it isnt, I suggest you upload a sample workbook with dummy data and a few examples of your expected outcome

Here's a sample workbook in which I have done the calculations manually so that you can see what I'm trying to achieve.
My problem actually has 6500 of each item and 1500 people to distribute to, so I'm sure you can see how much manual work this would be!
Thanks for all your help!

[Jacc]

Did you check out sheet4 in the workbook I posted?

[carlaloup]

Did you check out sheet4 in the workbook I posted?

No, sorry I missed it...That appears to work! I don't understand how but I'm going through it and hopefully I can apply it to my problem. Thanks so much!!!

[Jacc]

----------

[Jacc]

Updated version. First row inequality fixed and 4 columns removed.

[Jacc]

While solving a similar problem, a few more refinements were made to this one. Other thread: http://www.excelforum.com/excel-form...employees.html
I post this here so it won't get lost.