|I posted a question about this several months ago, but I didn't explain it very well and then I got sidetracked by some family emergencies. Now I need to get this done, so would appreciate any help.
Here's the situation. About a year ago, while cleaning out the attic, I found a box of old LP record albums. I haven't had a turntable for 20 years and, figuring that no one listens to LPs anymore, I was about to toss them. Before I did, I posted a message on our neighborhood email group. I got a ton of responses offering to take them and pleading with me not to toss them. I've always had a tendency to leap and then look, so I posted another note offering to collect any other unwanted albums anyone else might have in their attic and then have a yard or garage event to distribute them to those who want them. I got another surprising response. More than a dozen people responded and brought over albums. I now have over 1,000 albums in about 25 banker boxes all over my home office.
I need to get these distributed so I can use that space for other junk. 😉
The yard event is out. I have at least 20 "takers" so far. There is no way that I can find a time when they are all available and a couple of them are relatives of neighbors who are not in town. Plus, with 1,000 albums, people would be thumbing through the boxes forever. Much better to have a list that people can peruse at their leisure.
I decided to use this opportunity to polish up my database skills and come up with a way to allow each taker to select the albums they want online and then come up with an algorithm to allocate them fairly.
I have made some progress. I have the albums in an Access database. I have a Google Sheet derived from that database. I have a way to share that with the neighbors. They can mark the albums that they want. All I need now is the allocation algorithm.
My first question is how to set up the Google Sheet. Should I put a checkbox next to each album so that they check the albums they want? I don’t really like this option, because the albums are not equal. Each neighbor will have a different priority for different albums. I need way for them to indicate a preference of one album over another.
Next, I thought about letting them rearrange the albums in priority order or give each one a priority number. But there are 1,000 albums. I think that would be difficult to manage.
My current thinking is to define a limited number of priorities, like 5 or 10. There would be a box next to each album into which each neighbor could enter a number from 1-5 or 1-10. Then I would develop an algorithm to allocate the #1s as fairly as possible, then the #2s, etc.
It soon occurred to me that I’ll need to limit the number of albums each neighbor can select in each priority. If one neighbor marked all 1,000 albums priority 1 and I allocate them first, no one would get any of their lower priority albums. There are 1,000 albums and about 25 takers, so a limit of 10 albums in each priority level should allow each neighbor to get some good albums without taking them all away from those who made them their #2 or #3 choice.
I also plan to allow each neighbor to indicate that they will take any albums left over and not taken by anyone. My plan is to give away all of them.
Now for the actual allocation. I see several possibilities. I could first allocate all of the albums that were selected by just 1 person. This seems like a good start.
Now do I allocate the contested albums by priority or not? That is, do I allocate the priority 1 albums, then the priority 2, and so on? Or do I allocate them all in one loop?
I could sort the albums by the number of takers, then allocate them in that order starting with the 1-taker albums. I would then use a weighting mechanism to give priority to the neighbor that rated it #1 over one who rated it lower.
I also plan to weight the takers by the number of albums each already allocated. This will help ensure that each neighbor gets their fair share. Should I count all of the albums allocated so far including those that were not contested? On the one hand, it will help even out the total overall allocation. On the other hand, it seems unfair to punish someone for claiming an album that no one else wanted.
That’s as far as I have gotten so far. I’m working on a flowchart. I’ll post it when it’s ready.
Thanks for any suggestions.