Distribute values based on weights

[vttrian]

Hello again.

The problem at hand is as following:

Let's say we have 3 people A3:A5 and 3 fruits H3:H5. We need to feed every person a fruit. No two people can have the same fruit. Based on history, we know who has eaten what in the past. We don't want them to eat the same fruit all the time, but rather to alternate. I have managed to find the least of the fruits everyone ate, but when the same value occurs twice, I have a problem.

The goal is to find the Proposal. If the first and second person should eat the same fruit (but not the third), then the first one will eat it and the second will eat the one that is not the first's or the third's. If all three have the same fruit, the fruit will be distributed by the fruits ranking number (first to last).

I thank you all in advance.

Book.xlsx
	A	B	C	D	E	F	G	H
1	People	Apples in the past	Melons in the past	Berries in the past	What he should eat	Proposal		Fruit
2
3	Allan	3	1	3	Melon	Melon		Apple
4	Jack	2	2	2	Apple	Apple		Melon
5	Betty	2	4	2	Apple	FALSE		Berries
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(MIN(B3:D3)=B3,H3,IF(MIN(B3:D3)=C3, H4,H5))
F3	F3	=E3
E4	E4	=IF(MIN(B4:D4)=B4, H3,IF(MIN(B4:D4)=C4, H4,H5))
F4	F4	=IF(F3<>E4,E4,IF(F3<>E5,E5))
E5	E5	=IF(MIN(B5:D5)=B5, H3,IF(MIN(B5:D5)=C5,H4,H5))
F5	F5	=IF(AND(F3<>E5,F4<>E5),E5)

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Distribute values based on weights
and Distribute values based on weights
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.