Random Selections

G

Greg

Hi all,
I have a spreadsheet with a colum of 200 I.D. numbers.
01A
01F
02C
02H
etc.

Each week I have to randomly select 10 of the 200.

Is there an Excel function that will do this for me?

Thanks
Greg
 
H

Hank Scorpio

Hi all,
I have a spreadsheet with a colum of 200 I.D. numbers.
01A
01F
02C
02H
etc.

Each week I have to randomly select 10 of the 200.

Is there an Excel function that will do this for me?
Click to expand...

Not really; I have one way of doing it which will probably only prove
that I need more sleep, but it does seem to work...

Can you stick a blank column in front of your ID numbers, and fill
that with =RAND() formulas?

Let's say that your RAND formulas are in column A, and your ID numbers
are now in column B.

Now enter the numbers 1 to 10 in somewhere. I've put them in D1 to
D10.

You can now put the following formula in E1 and copy it down to E10:
=VLOOKUP(LARGE($A$1:$A$200,D1),$A$1:$B$200,2,FALSE)

The formula in E1 will look up the largest random number through its
LARGE function. The one in E2 will look up the second largest one and
so on. The VLookup will return the ID code that is in the next column.

Accordingly all you need to do is recalculate the sheet, the RAND
functions will generate a whole new series of random numbers, and your
lookup formulas will select the top 10.

And now, I'm off to bed. 8^>
 
J

JE McGimpsey

One way:

Paste the RandInt() UDF from

http://www.mcgimpsey.com/excel/randint.html

into a regular code module (if you're not familiar with UDFs see David
McRitchie's

Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm

In an empty column, say B, select the first 10 cells and array enter
(CTRL-SHIFT-ENTER or CMD-RETURN):

B1:B10: =INDEX(A:A,RandInt(1,200))

This will return a random selection of A1:A200 which will change with
every recalculation of the worksheet.
 
K

Ken Wright

Could also use that approach with RANDBETWEEN in an OFFSET function, though more
likely probably to get odd repeats:-

With your data in A1:A200, in any other 10 cells

=OFFSET($A$1,RANDBETWEEN(0,199),0)
 
Top