Help with allocation algorithm

[Cynthia Moore]

Hi, I am new here. I hope I am doing this right. If not, please advise. I signed up to handle a neighborhood project that involves distributing about 700 items to anyone in the neighborhood who wants them. I have created an Access database containing the items with details such as the size, weight, and condition (very good to poor). I also have a Google Sheet that I populated from the database. It has one row for each item. There are columns for each of the properties (description, size, weight, condition etc.). These are all read only (protected). The first column is the selection column. In it, the neighbor can indicate whether they want that item or not. My current thinking is to let them enter a priority number (1-10) or leave it blank. I will allow each neighbor to access a copy of that sheet and fill in their choices. When they are all done, I will lock the sheets and import the priority columns to Excel where I am more comfortable. I have the code roughed out that will do the actual allocations. My remaining task is to devise an algorithm for allocating items that are selected by multiple people. I could just select randomly, but I'd like an algorithm that does the allocation as fairly as possible. I realize that "fairly" is subjective. I currently have about 20 people who have said they want some of the items. At least one person said they would take them all. The algorithm will start by allocating all of the items that are selected by just one person. My current thinking for the items selected by multiple people is to calculate some measure of the percentage of the items they requested that they have already been given. Suppose A & B both choose item 143, A has 20% of their requests, and B has 30% of theirs. I could then award that item to A or, since B has 3/2 as much of their selections as does A, I could generate a random number from 1-5 and award it to A if it's 1-3, and B if 4-5. Or I could skew it more in favor of A, the one with the lower percentage. I'm not entirely happy with this algorithm for a couple of reasons. (1) It doesn't take into account the priority choice. What if A choose the item with a priority of 5, but B chose with with a priority of 1? (2) I'm not sure of it takes into account how many items each one chose. If B only chose 1 item but A chose 100, should that one go to B, because if not, they get nothing? I would appreciate any suggestions for good ways to handle this. Thanks

[Lost User]

You do a draw for each item. Assign each interested party a sequential number. Generate a random number in the above range and assign the prize. Most other methods will be called into question.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food

[Cynthia Moore]

Let me see if I understand what you are suggesting. I have 15-20 "takers". I give each of then a number from 1-20, right so far? Then I pick the items one at a time and draw one of the taker numbers from a hat, like a Bingo wheel. Whichever number comes up, that person gets the item. Right? So I have almost 700 items. Are you seriously suggesting that I have 700 "draws"? If I could manage to keep each draw to 30 seconds, which I doubt, that would take almost 6 hours. Then there is the problem that most of the takers will only want a few items. So I'd have to go through the numbers after each draw and only put in the ones who want that item. Now we are well over minute for each draw and well over 12 hours for the whole disaster. Plus I don't want to try and find a time when all 20 takers are available. I have the items in a database and I have a Google Sheet containing the list of items that I can share with each taker. At their leisure, they can browse the list of items and indicate which they want. I will merge those choices into an Excel sheet that will execute my allocation algorithm. All I need is the algorithm. The key feature is breaking ties. I am working on a way to calculate the percentage of the choices each taker has received. If 2 or more takers want the same item, I will either award it to the3 one with the lowest percentage or generate a weighted random number based on those percentages.

[Lost User]

I "seriously" thought you were saying you could program. The "draw" is simply iterating through the list of items (a "for" loop); and then executing a "RAND" function if there are takers. The "logistics" of "having takers available" has nothing to do with the "drawing program" and any "list" you may produce saying who got what. It would take less than a second for 700 items. You said: 1) You "have the items in a database". Don't you know how to read them? (Part 1) 2) They "indicate their choices" which you "merge, blah blah" (Part 2) 3) [Rnd Function - Access](https://support.microsoft.com/en-us/office/rnd-function-503cd2e4-3949-413f-980a-ed8fb35c1d80) Your (output) list could even have a "second choice" if the winner did not show up to claim their prize. Yes, they claim them; you don't chase them down or wait for them after you "publish" the list of winners. You do intend to create a winner's list? Or is this word of mouth? Oh ... and you don't "give" them a number; you "assign" it as you "merge them" (or whatever). You need a name, you assign a number that you can later randomly generate. I suggest 1,2,3,4, ... # of takers for "that" item. If you knew anything about "indexes" (relative or otherwise), you wouldn't even need to assign a "physical" number; it would be based on "position"; assuming an unordered list. Note, nowhere is there any extra "draw" time.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food

[Cynthia Moore]

Wow! My programming skills may not be the best, but it's clear that you meet every geek stereotype, namely, social skills: non-existent.

[BillWoodruff]

What an interesting challenge ! Creating an "algorithm" for what I'd call "distributive social justice" requires you precisely define the logic of "who gets what." Of course, that logic can include introducing randomness. Among the scenarios I'd want the algorithm to deal with: 0) "need" ... if you have a way to evaluate how much the person really needs the items, how "essential" the item is. Age ? Living alone vs, with others ? Income ? Health and access to health-care ? 1) "utility" ... if you have a way to evaluate how much the person can benefit from the items. 2) "greediness vs, modesty" ... does someone who wants everything with maximum intensity deserve more than those with fewer choices ? 3) "collateral benefits:" ... to what extent the items benefit the family, or community, of the receiver. 4) "reciprocity:" ... if you have a way to evaluate how much the person will contribute to your project. And ... more, and more. Given "human nature," whatever principles you define will probably anger some people ... unless you make the allocation "purely" random.

«The mind is not a vessel to be filled but a fire to be kindled» Plutarch