Randomly choosing

[Roger H.]

Hello. I have a rather tricky problem that I have not been able to untangle.
Situation: I have a group of numbers in column A that range from zero up to
999 in value. These numbers are randomly distributed. I have another group
of numbers that also range from zero to 999 in column B.

Starting with the value in A1, I want to RANDOMLY choose any value from
column B, such that, the value that is chosen depends in some way on the
value in A1. I tried =INDEX(B1:B1000, A1, 1) and copied down. For general
purposes with no restrictions, this works. But when the value in A1 is
repeated later in column A, this gives me the same number as before that was
chosen from column B. For my purposes, this is unacceptable.

To conclude, I need each individual value in A to pluck a number from zero
to 999 from B without forced duplication because of the numerical value
itself in column A. In other words, the second occurence of any number in A
will be free to choose a different number from B than before.It seems that
this will required a combination of formulas. Thank you.......Roger H.

 

[Bernie Deitrick]

Roger,

Perhaps you could enter the numbers 0-999 into B1:B1000, enter the formula
=RAND()
into cells C1:C1000, and sort B1:C1000 based on column C. That will
randomize the numbers in column B and prevent duplicates.

I must say that I don't know if that will solve your problem, which was a
bit confusingly stated.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Roger,

If you want to generate random numbers between 1 and 999, try

=int(rand()*1000)

and copying the formula into as many rows as you need

Cheers & hope this helps

Pete

 

[Myrna Larson]

Slight correction: your formula gives RNs in the range 0 to 999, rather than
1-999