Nine random and unlike numbers

#1 01-29-2006, 11:35 AM

Hi,

How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's self.
I need the number 1 - 9 in random order.

**daddylonglegs** · #2 01-29-2006, 12:21 PM

You can do it this way

put this formula in A1

=RAND()
and copy down to A9

in B1 use this formula

=RANK(A1,A$1:A$9)
copy down to B9

B1:B9 should now show 1-9 in random order, hide column A if desired.

Note: values will change every time sheet recalculates

#3 01-29-2006, 12:30 PM

For one way, see

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

In article <73A5E630-1542-4464-8B6B-09D7C9862F94@microsoft.com>,
Roy <Roy@discussions.microsoft.com> wrote:

> Hi,
>
> How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's self.
> I need the number 1 - 9 in random order.

#4 01-29-2006, 01:10 PM

Try this it works but some on here may think its a bit poor?

Sub randNumber()

Range("A1").Value = Int((9 * Rnd) + 1)

For i = 1 To 8
rng = "A" & i
Redo:
Range("A1").Offset(i, 0).Value = Int((9 * Rnd) + 1)
For n = 0 To i - 1 Step 1
If Range("A1").Offset(i, 0).Value = Range("A1").Offset(n, 0) Then
n = n - 1
GoTo Redo
End If
Next n

Next i
End Sub

This puts a random number in cell A1 then starts a loop from A2 which first
starts by entering the next random number in A2 using offset then checks it
against A1 if it matches it enters another rand number until it finds one
that does not match. And then so on in the loop with the next cell.

Regards
Neil
"daddylonglegs" <daddylonglegs.22emnm_1138551900.9949@excelforum-nospam.com>
wrote in message
news:daddylonglegs.22emnm_1138551900.9949@excelforum-nospam.com...
>
> You can do it this way
>
> put this formula in A1
>
> =RAND()
> and copy down to A9
>
> in B1 use this formula
>
> =RANK(A1,A$1:A$9)
> copy down to B9
>
> B1:B9 should now show 1-9 in random order, hide column A if desired.
>
> Note: values will change every time sheet recalculates
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=506159
>
>

#5 01-29-2006, 01:10 PM

Try this it works but some on here may think its a bit poor?

Sub randNumber()

Range("A1").Value = Int((9 * Rnd) + 1)

For i = 1 To 8
rng = "A" & i
Redo:
Range("A1").Offset(i, 0).Value = Int((9 * Rnd) + 1)
For n = 0 To i - 1 Step 1
If Range("A1").Offset(i, 0).Value = Range("A1").Offset(n, 0) Then
n = n - 1
GoTo Redo
End If
Next n

Next i
End Sub

This puts a random number in cell A1 then starts a loop from A2 which first
starts by entering the next random number in A2 using offset then checks it
against A1 if it matches it enters another rand number until it finds one
that does not match. And then so on in the loop with the next cell.

Regards
Neil
"Roy" <Roy@discussions.microsoft.com> wrote in message
news:73A5E630-1542-4464-8B6B-09D7C9862F94@microsoft.com...
> Hi,
>
> How can I generate 9 diffrent numbers from 1 to 9 thats not repeat it's
> self.
> I need the number 1 - 9 in random order.
>

Welcome to the Excel Forum

Forum Statistics