Suggestions for allocation algorithm

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
I volunteered for a little neighborhood project that has turned out to not be that "little". Several neighbors, including myself, mentioned that they had boxes of old vinyl record albums that they no longer want since they do not have a turntable. Several others said they would love to have the albums as they do have turntables. I volunteered to collect the old records and distribute them to those who want them.

Well, I now have 16 boxes containing almost 700 albums with more to come. I decided to make this a little software project. I created an Access database and have entered all of the albums. I found a way to create a Google Sheet with one row for each album showing the title, the artists, the size, the RPM, the condition, etc. The PK column is the Access Primary Key. I included it to reduce confusion. It is the only unique field, as many of the titles are similar or even identical. The Priority column is the only unprotected field. Takers can enter a priority number (1-5) to indicate which albums they want and at what priority.

1630002131867.png


I can make a copy for each "taker" that is read only except for the Priority column. When all of the forms are complete, I will lock them and import the Priority columns (sorted by PK) into an Excel sheet, something like this:

1630002748893.png


Here we have just 6 takers. Their initials are in F5:K5. Their choices are in F6:K15. The PKs are in Col C. Now I can write a VBA Sub to read the selections and do the allocations. Col D is where the allocation algorithm will put the initials of the one who got each album.

Album 8 was only selected by 1 person, GH, so it was awarded to them. But I am not sure what to do about albums selected by more than 1 person. GH selected every album. PC selected just 2. If I just pick randomly, there is a 25% chance that PC will not get any albums, while GH is likely to get a lot.

I am playing with the idea of calculating some measure of the proportion of their selections that they have received so far. Suppose by the time I get to Album #4 (row 9), GH has 30% of their selections and PC has 0%. I could just award it to the person with the lowest %.

But consider Album 6 (row 11). It was selected by everyone. Suppose the selection %s are 50% 40% 30% 20% 10% & 5%. Do I give it to SB who only gave it a "3"? It seems like I ought to take into account the priority number and give more probability to someone who selected it "1" vs a lower priority. But I have to be careful not to give too much weight to that or someone like GH who rated everything a "1" or "2" will dominate.

I would appreciate any suggestions.

I assume this belongs here, rather than the Excel section. If not, please move as appropriate.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top