Share via

Select Random Winner

Anonymous
2016-05-23T14:44:09+00:00

Hello

I am trying to randomly select winners from  a list of persons that submit a maximum of 10 entries to win from a pool of 20 items numbered 1-20. I would like to use excel to choose a winner of an item from the persons that submitted an entry for the particular item. Each person should be able to win a maximum of 2 items from the pool and once the item is awarded it should not be awarded to another person.

Example of Data:

Entrant's Name Entry 1 Entry 2 Entry 3 Entry 4 Entry 5 Entry 6 Entry 7 Entry 8 Entry 9 Entry 10
Mary Smith 1 11 9 4 17 19 13 5
Micheal Matthews 1 8
Mark Evans 1
David Jones 1 1 1
Jane Doe 1 2 3 4 5
John Doe 1 2 3 4 15 6 7 8 9 20
Bob Smith 1 4 5 8 9 10

Example of the winners would be:

Name Item Won
Mary Smith 1
Mary Smith 9
Micheal Matthews 8
Jane Doe 4
Jane Doe 3
John Doe 20
Bob Smith 10

Note: Not all persons are required to win an item.

Can you please advise if this can be done? Looking forward to a response.

Thanks in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Andreas Killer 144.1K Reputation points Volunteer Moderator
2016-05-24T16:41:08+00:00

Okay, got something ready, download this sample file:

https://dl.dropboxusercontent.com/u/35239054/Samples/f7352a1d-7c09-45c9-a91c-61c6ed8d608c.xlsm

This is just one way to go, I choose a random price for a person, follow your rules and finish when no more prices are possible. I've not sorted the output, so you can follow the code logic.

The consequence with your data it is that not all prices are awarded.

When we have to award all prices, means John Doe wins 20 and 15 always, because nobody else is interested in this prices. If you like I can rewrite the code and solve the puzzle by starting with the prices...

Andreas.

Was this answer helpful?

0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2016-05-24T15:02:11+00:00

    Okay, understood. One more question:

    Should not all (possible) prizes are awarded?

    Your sample shows that item 5 is not awarded, but Bob Smith  is interested in and your rules makes it possible that he can win that item.

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-05-24T13:35:19+00:00

    Hi,

    Thanks for your response.

    Each person is allowed to submit up to 10 entries. The entry-number is to keep track which item they selected for each entry.

    So in the case of Mary:

    Entry 1 is for Item 1

    Entry 2 is for Item 11

    Entry 3 is for Item 9

    Entry 4 is blank and so on. (We can ignore the blank entries)

    So each person can specify which item they are interested in winning. So Mary is only interested in winning items: 1, 11, 9, 4, 17, 19, 13 and 5 based on her entries made.

    Please let me know if any further clarification is needed :)

    Was this answer helpful?

    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2016-05-24T13:15:26+00:00

    randomly select winners from  a list of persons

    win from a pool of 20 items numbered 1-20.

    submitted an entry for the particular item.

    Okay, no problem, but it is not clear what is the sense of the numbers in the Entry columns and what is the sense of the Entry-Numbers itself. And what items? Which pool? Are the numbers connected?

    That is clear:

    Each person should be able to win a maximum of 2 items from the pool and once the item is awarded it should not be awarded to another person.

    Andreas.

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more