networkn

asked on

# Random Number from Excel List

Hi There!

I have a sheet that contains a list of members of a club. There is a membership ID field which is unique and non sequential.

I want to be able to randomly once a month select a membership ID to win a prize.

How could I simply do this?

I have a sheet that contains a list of members of a club. There is a membership ID field which is unique and non sequential.

I want to be able to randomly once a month select a membership ID to win a prize.

How could I simply do this?

ASKER

I don't think either of those is correct. I can't see how to make them apply to the numbers in a group of cells. I also need it to be able to run over and over again.

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

akb: that does the job. I'll test it over the weekend and accept a choice on Monday.

I suggested using a named range called Members so you wouldn't need to change the formula if you add or subtract members. If the membership list is fixed, then you could also use a formula like:

=INDEX(A2:A14,1+RAND()*13)

=INDEX(A2:A14,1+RAND()*13)

ASKER

Thanks for your help it works just fine. We would actually now like 4 names drawn out at once and ideally capture not just the member ID but the next 2 fields beside it, firstname and last name. Should I open another question to cover that?

I sincerely apologize for the lack of updates, it's been INSANE at work.

I sincerely apologize for the lack of updates, it's been INSANE at work.

I think you should open a separate question for that as your original question has been answered.

To get the additional information, you should use a VLOOKUP formula based on the member ID.

=VLOOKUP(memberID, LookupTable, 2, FALSE) for first name

=VLOOKUP(memberID, LookupTable, 3, FALSE) for last name

=VLOOKUP(memberID, LookupTable, 2, FALSE) for first name

=VLOOKUP(memberID, LookupTable, 3, FALSE) for last name

The formulas suggested to return a random member ID can be copied down to return additional member ID numbers. You'll need to make the row references absolute in that last formula if you do that. The first formula (using named ranges) doesn't have that problem.

=INDEX(A$2:A$14,1+RAND()*13) 'The $ makes the row references absolute

It's worth noting that you might get duplication if you copy the formula down. The simple workaround is to copy it down with an extra member ID or two in case of duplication.

The more complex approach is to put a random number to the left of each member ID:

=RAND()

You then find the largest four random numbers and their associated member ID, first name and last name. Copy the following formula down for the the n largest random numbers:

=LARGE(random number column, ROWS($A$1:$A1)) 'returns nth largest number

=VLOOKUP(nth largest random number, table with random number column on left, 2, FALSE) 'returns associated member ID

=INDEX(A$2:A$14,1+RAND()*1

It's worth noting that you might get duplication if you copy the formula down. The simple workaround is to copy it down with an extra member ID or two in case of duplication.

The more complex approach is to put a random number to the left of each member ID:

=RAND()

You then find the largest four random numbers and their associated member ID, first name and last name. Copy the following formula down for the the n largest random numbers:

=LARGE(random number column, ROWS($A$1:$A1)) 'returns nth largest number

=VLOOKUP(nth largest random number, table with random number column on left, 2, FALSE) 'returns associated member ID

This question should be close. The original question has been answered. The new question should be posted as a new question.

The original question has been answered. This is a new question.

Here is a sample workbook showing how to use a single formula array-entered in a range of cells to return the Member ID, first name and last name of four randomly selected prize winners--without duplication.

=INDEX($L$2:$N$14,MATCH(LARGE($K$2:$K$14,{1;2;3;4}),$K$2:$K$14,0),{1,2,3})

Brad

PrizeWinnersQ28176304.xlsx

=INDEX($L$2:$N$14,MATCH(LA

Brad

PrizeWinnersQ28176304.xlsx

ASKER

I sincerely apologize, I have somehow allowed this to slip from my radar.

Given the protesting I'll close this question, award points as they relate to the original question and open another with the expanded requirement.

Again my apologies for this getting away from me.

Given the protesting I'll close this question, award points as they relate to the original question and open another with the expanded requirement.

Again my apologies for this getting away from me.

RAND

or

RANDBETWEEN

I would think RANDBETWEEN is what you need as it looks at entries between A to Z and returns a result.

HTH,

Kent