Thread: i need 40 random numbers in 40 cell with no matches

can someone help me with this in excel 2003 st. ed

one way:

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

In article <CE2DF4A4-629E-4274-A2E3-7ACA577BBA4A@microsoft.com>,
"henry" <henry@discussions.microsoft.com> wrote:

> can someone help me with this in excel 2003 st. ed

If you with no matches mean no duplicates and 40 numbers only it won' really
be random? You can use a simplistic approach and put let's say 1000 from
1-1000 in A1:A1000, then in B1 put =RAND(), copy down to B1000, now select
both columns and sort by column B, then select the first 40 numbers in A and
copy somewhere else.



Regards,

Peo Sjoblom


"henry" wrote:

> can someone help me with this in excel 2003 st. ed

or you could turn iteration on and put this formula in B1 and copy down to
B40

=IF((B1>0)*(COUNTIF($B$1:B1,B1)=1),B1,(INT(RAND()*40+1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> wrote in message
news:20CB6A55-9451-4818-9392-630F56E0652E@microsoft.com...
> If you with no matches mean no duplicates and 40 numbers only it won'
really
> be random? You can use a simplistic approach and put let's say 1000 from
> 1-1000 in A1:A1000, then in B1 put =RAND(), copy down to B1000, now select
> both columns and sort by column B, then select the first 40 numbers in A
and
> copy somewhere else.
>
>
>
> Regards,
>
> Peo Sjoblom
>
>
> "henry" wrote:
>
> > can someone help me with this in excel 2003 st. ed