JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- 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.
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:
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.
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.
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:
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.
