Generate random numbers with no repeats with a Dynamic Entry

**austinrob** · 03-14-2010, 08:24 PM

I have not had much luck trying to figure out how to generate a range of random numbers based on a dynamic value.

Let's say I have Coulmn F1:F1000 with the following data:

=RAND()

In D1, I want to enter a value between 1 and 1000, which would be my dynamic entry field: Will Use 251 as a example in D1

In column A, A1-A251 would be popluated with Random numbers per row.

What formula do I need to use for a single column output in "A" of random numbers?

I saw a similar posting - but it was based on two rows of random numbers. They pasted the following in A1 - 1000:

=IF(ROW()-ROW(A$1)+1>$D$1/2,"",RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1)))

http://www.excelforum.com/excel-gene...o-repeats.html

Thanks in advance,
R

**austinrob** · 03-21-2010, 08:15 PM

Does anyone have any ideas on this one?

**martindwilson** · 03-21-2010, 08:35 PM

why the column f?
just put in column a starting a1
=if(row()>$d$1,"",rand())
its unlikely you'll get duplicates
but
in b1 put
=IF(MAX(FREQUENCY(A:A,A:A))>1,"try again","no dupes")
and just hit f9 to recalculate if you do