How to equally distribute items to people based on the value of the item???

[ddoss17]

hello,

i am trying to distribute items to people based on the value of the item.

example: of how i would manually distribute the item to the people.

PEOPLE:
bob
joe
steve

ITEM LIST
item 1: 10 points - BOB
item 2: 5 points -STEVE
item 3: 5 points -STEVE
item 4: 3 points -JOE
item 5: 4 points -JOE
total: 27 points

is there a way to make excel automatically assign the names of the people to the items and get them equally distributed as possible, its doesn't have to distributed perfectly.


thanks.

[spitfireblue]

Hi,

You can do it with a macro like the attached.

[JohnTopley]

Try

Formula in F2

=MIN(SUM($B$2:$B$6)-SUM($F$1:$F1),ROUNDUP(SUM($B$2:$B$6)/SUMPRODUCT(1/COUNTIF($C$2:$C$6,$C$2:$C$6)),0))